Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 38
  1. #16
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    Ok, found all the errors but it wants to know what is t2.id

  2. #17
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is the name of your table?
    I copied the data from your workbook and named the table tblTOPSELLERS
    Click image for larger version. 

Name:	tblDesign.png 
Views:	16 
Size:	16.2 KB 
ID:	35003

  3. #18
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    There's the issue I don't have the primary key in place.

  4. #19
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  5. #20
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    There's the issue I don't have the primary key in place.

  6. #21
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    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.

  7. #22
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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

  8. #23
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    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.

  9. #24
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    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??

  10. #25
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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

  11. #26
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    Click image for larger version. 

Name:	Untitled.png 
Views:	10 
Size:	29.1 KB 
ID:	35049Here you go
    Last edited by Alanna; 08-09-2018 at 05:29 PM. Reason: remove text

  12. #27
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I got that column name from your xlsx file.
    When I imported the xlsx into Access, that was the column name that was transferred.



    Click image for larger version. 

Name:	alnnaxl.png 
Views:	9 
Size:	106.9 KB 
ID:	35050

    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.
    Click image for larger version. 

Name:	qry09Aug18.png 
Views:	9 
Size:	25.9 KB 
ID:	35051

  13. #28
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    Would it be because I'm taking it from a Query and not really a table?

  14. #29
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  15. #30
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    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 ];

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-30-2017, 03:57 PM
  2. Replies: 2
    Last Post: 11-26-2016, 01:07 PM
  3. Replies: 2
    Last Post: 01-27-2016, 08:38 AM
  4. Replies: 6
    Last Post: 06-09-2015, 09:51 AM
  5. Replies: 0
    Last Post: 02-25-2013, 04:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums