Totals Query that shows extra Info

[Locopete99]

Hi Guys,

I have a table which shows all the sales for my company for a year.

I want to create a query that sums all of the sales for each user but also shows some other information.

This would then return the Top 50 customers.

The query I have so far is as follows:

SELECT TOP 50 Tbl_CurrentFY.Salesperson, Tbl_CurrentFY.[User code], Tbl_CurrentFY.[User Name], Tbl_CurrentFY.[Customer Group], Sum(Tbl_CurrentFY.[MTD net sales]) AS [SumOfMTD net sales]
FROM Tbl_CurrentFY, Tbl_LastFY
GROUP BY Tbl_CurrentFY.[User code]
ORDER BY Sum(Tbl_CurrentFY.[MTD net sales]);

The problem is, I'm using a totals query so for every other field in my query I am getting an error that states "Your query doesnt include specified field x as part of the aggregate function."

Can anyone point me in the right direction.

Basically I want a query that sums up all of the MTD Net Sales grouped by user code, but then showing also the User code and Name, as wells as the salesmans name and customer group code.

All my attempts so far are erroring.

Ideally as well the next step would be to pull in the MTD Net Sales from the LASTFY table and have a comparison between the last FY sales and the current FY sales for each user.

 

[plog]

2 things:

1. In an aggregate query all the fields in the SELECT must be in the GROUP BY or be acted on by an aggregate function (MAX, SUM, COUNT, etc.). You have 5 fields in the SELECT, 1 in the GROUP BY and 1 with an aggregate function. You must make those other 3 fields comply.

2. You've created a cartesian product (https://en.wikipedia.org/wiki/Cartesian_product). That is, in the FROM you haven't connected your tables. You are going to end up with [Number of Records in Tbl_CurrentFY] * [[Number of Records in Tbl_LastFY] records from your query. You need to JOIN those tables somehow.

It might be best to just demonstrate with data what you hope to end up with, because you are quite far off with that SQL--so much so I don't know what you are trying to achieve.

 

[Locopete99]

Hi Plog,

Hopefully there is an attachement I have mocked up with random data to show what I want.

In response to your points, 1) I did have all of the fields as group by, but when i run the query the running query bar can be seen at the bottom, all the way across (basically nearly complete) but it just sits there for hours and nothing happens.

Secondly, once I'm happy with running this part of the query I would link the LastFY Table, but I couldnt get this to work.

On the attachment, all column names match my field names and all are from the CurrentFY Table.

 

[plog]

No attachment.

The query running for ever has to do with the Cartesian Product (#2) that I discussed not the GROUP BY. The GROUP BY issue (#1) was a syntax error that prevented the whole thing from running, when you fixed that the query made sense and was able to run, but then it hit the logical error (Cartesian Product) and timed out.

Again, no attachment, so try again. Also, why is tbl_LastFY in that query at all? You never use a field from it.

 

[Locopete99]

Hi Plog,

I see your point now. That last FY table was added in the default builder as the idea was to add that in, but then i moved to SQL builder. Now I have removed it all works well.

I hadn't even noticed that I still had that table on the query!!

Its been a long week and as usual, one simple thing was overlooked and causing me the issue! Thanks for your patience and help.