Ok, found all the errors but it wants to know what is t2.id
Ok, found all the errors but it wants to know what is t2.id
There's the issue I don't have the primary key in place.
Add a field named ID and make it's datatype Autonumber, then save.
You might want to back up the table first, or save a copy of the database.
There's the issue I don't have the primary key in place.
Unfortunately because it's a linked table I can't put the primary key in. It's linked because the data comes from a third party as an excel spreadsheet and since I have to manipulate it in Access I've created the table as linked so every week when I get a new spreadsheet I save it the same and I don't have to update all my queries and reports.
Unless I'm missing something. again. sigh.
OK,
Try this sql (doesn't use ID)
Code:SELECT t1.LOCATION, t1.DEPT,t1.[DEPT DESC],t1.[SumOfQTY SOLD], t1.[SumofNet SALES] FROM tblTOPSELLERS AS t1 WHERE ( ( (t1.[SumofNet SALES]) IN ( SELECT TOP 20 T2.[SumofNet SALES] FROM tblTOPSELLERS AS T2 WHERE T2.LOCATION = T1.LOCATION ) ) ) GROUP BY t1.LOCATION, t1.DEPT,t1.[DEPT DESC], t1.descr,t1.[SumOfQTY SOLD], t1.[SumofNet SALES] ORDER BY t1.location, t1.[SumofNet SALES] desc
Last edited by orange; 08-09-2018 at 06:20 PM. Reason: space before desc
No it's looking for parameters for each line in the select. I'm going to just do 6 separate queries at this point and putz with it later. I have to get my boss his report.
How about a create a table called "locations" and it has DFW, LAX, LAS, HRH, SFO, IAH in it.
Can I use that table with my Top Sellers Query (which is the file I exported as excel) to drive the TOP 20 by group??
How about posting the code you tried based on the query I provided in post #22.
Show us a capture(s) of the error/message
I got that column name from your xlsx file.
When I imported the xlsx into Access, that was the column name that was transferred.
I just noticed there is NO Space before DESC, make sure there is a space ( I did edit my original in post 22 ---)
I also checked my query in my database and it has the space and capital DESC???
I don't remember any edits, but I must have done something??
Here is the query sql as in my database. Sorry for the confusion.
Would it be because I'm taking it from a Query and not really a table?
That might explain things. Can you post your query SQL?
SELECT [ALL STORES WKLY SLS].[LOCATION ], [ALL STORES WKLY SLS].[DEPT ], [ALL STORES WKLY SLS].[DEPT DESC], [ALL STORES WKLY SLS].UPC, [ALL STORES WKLY SLS].Description, [ALL STORES WKLY SLS].[OH UNITS], [ALL STORES WKLY SLS].[OH Retail], [ALL STORES WKLY SLS].[NET SALES], [ALL STORES WKLY SLS].[DISCOUNTS $], [ALL STORES WKLY SLS].[QTY SOLD], dbo_IM_BARCOD.BARCOD, dbo_IM_ITEM.ITEM_NO, dbo_IM_ITEM.DESCR, dbo_IM_ITEM.ITEM_VEND_NO, dbo_IM_ITEM.VEND_ITEM_NO
FROM [ALL STORES WKLY SLS], dbo_IM_BARCOD INNER JOIN dbo_IM_ITEM ON dbo_IM_BARCOD.ITEM_NO=dbo_IM_ITEM.ITEM_NO
WHERE ((([ALL STORES WKLY SLS].[QTY SOLD])<>"0") And ((dbo_IM_BARCOD.BARCOD)=IIf((Right([ALL STORES WKLY SLS]!UPC,11))=(Right(dbo_IM_BARCOD!BARCOD,11)),dbo_IM_ BARCOD!BARCOD,'n/a')))
ORDER BY [ALL STORES WKLY SLS].[LOCATION ];