Page 3 of 3 FirstFirst 123
Results 31 to 38 of 38
  1. #31
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    Or this one that removes all the calculation stuff:
    SELECT [TOP SELLERS].[LOCATION ], [TOP SELLERS].[DEPT ], [TOP SELLERS].[DEPT DESC], Sum([TOP SELLERS].[QTY SOLD]) AS [SumOfQTY SOLD], Sum([TOP SELLERS].[NET SALES]) AS [SumOfNET SALES], Sum([TOP SELLERS].[DISCOUNTS $]) AS [SumOfDISCOUNTS $], [TOP SELLERS].ITEM_NO, [TOP SELLERS].DESCR, [TOP SELLERS].ITEM_VEND_NO, [TOP SELLERS].VEND_ITEM_NO


    FROM [TOP SELLERS]
    GROUP BY [TOP SELLERS].[LOCATION ], [TOP SELLERS].[DEPT ], [TOP SELLERS].[DEPT DESC], [TOP SELLERS].ITEM_NO, [TOP SELLERS].DESCR, [TOP SELLERS].ITEM_VEND_NO, [TOP SELLERS].VEND_ITEM_NO
    ORDER BY [TOP SELLERS].[LOCATION ], Sum([TOP SELLERS].[NET SALES]) DESC;

  2. #32
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Does your query give you results? I ask because I notice [SIZE=3]<>"0"[SIZE=2] in the sql .

    Update: You had another post while I was typing.
    Did you create a table called TOPSELLERS?

  3. #33
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could try the SQL in post 27. Just make sure the spelling of the table name is consistent within the sql and it is exactly the name of the table you have created.

  4. #34
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    no.
    I have so much data to incorporate in order to get to the Top sellers query. I have a linked excel sheet and two linked db as my tables that drill down to the Top sellers Query.

  5. #35
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    Let's try this question instead.
    I have broken up the query so I have 6 different ones for each "location" Is there a way to get each query onto one form? Page 1 returning the query from DFW, page 2 returning the query from LAX and so on?

  6. #36
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I saved your xlsx and then linked to it.
    Then modified the SQL to use the linked Excel file and created a new query

    Click image for larger version. 

Name:	Top20WithLinkedExcel.jpg 
Views:	6 
Size:	114.2 KB 
ID:	35107

    The SQL
    Code:
    SELECT t1.LOCATION,  t1.DEPT,t1.[DEPT DESC],t1.[SumOfQTY SOLD], t1.[SumofNet SALES]
    FROM [TOP SELLERS Query] AS t1
    WHERE (
            (
                (t1.[SumofNet SALES]) IN (
                    SELECT TOP 20 T2.[SumofNet SALES]
                    FROM [TOP SELLERS Query] 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
    The result:

    LOCATION DEPT DEPT DESC SumOfQTY SOLD SumofNet SALES
    DFW 60 Specialty Apparel 9 $299.20
    DFW 60 Specialty Apparel 2 $281.20
    DFW 60 Specialty Apparel 3 $237.00
    DFW 61 Specialty Accessories 3 $209.60
    DFW 60 Specialty Apparel 2 $204.80
    DFW 60 Specialty Apparel 6 $200.60
    DFW 60 Specialty Apparel 2 $168.00
    DFW 60 Specialty Apparel 2 $148.00
    DFW 60 Specialty Apparel 4 $144.00
    DFW 60 Specialty Apparel 1 $138.00
    DFW 60 Specialty Apparel 2 $136.00
    DFW 60 Specialty Apparel 3 $132.00
    DFW 60 Specialty Apparel 3 $132.00
    DFW 60 Specialty Apparel 2 $116.00
    DFW 60 Specialty Apparel 2 $116.00
    DFW 60 Specialty Apparel 4 $116.00
    DFW 60 Specialty Apparel 2 $104.40
    DFW 60 Specialty Apparel 1 $98.00
    DFW 60 Specialty Apparel 1 $98.00
    DFW 60 Specialty Apparel 2 $96.00
    HRH 61 Specialty Accessories 2 $285.50
    HRH 60 Specialty Apparel 1 $224.00
    HRH 60 Specialty Apparel 2 $196.00
    HRH 60 Specialty Apparel 2 $156.00
    HRH 60 Specialty Apparel 1 $145.00
    HRH 60 Specialty Apparel 1 $138.00
    HRH 60 Specialty Apparel 2 $128.00
    HRH 60 Specialty Apparel 1 $118.00
    HRH 60 Specialty Apparel 2 $116.00
    HRH 60 Specialty Apparel 1 $98.00
    HRH 61 Specialty Accessories 1 $94.00
    HRH 61 Specialty Accessories 2 $91.20
    HRH 60 Specialty Apparel 1 $89.50
    HRH 60 Specialty Apparel 1 $88.00
    HRH 60 Specialty Apparel 1 $88.00
    HRH 60 Specialty Apparel 1 $88.00
    HRH 60 Specialty Apparel 1 $88.00
    HRH 60 Specialty Apparel 1 $79.00
    HRH 61 Specialty Accessories 1 $78.40
    HRH 60 Specialty Apparel 1 $78.00
    HRH 60 Specialty Apparel 1 $78.00
    IAH 60 Specialty Apparel 3 $400.20
    IAH 60 Specialty Apparel 2 $250.80
    IAH 60 Specialty Apparel 3 $218.40
    IAH 60 Specialty Apparel 2 $196.00
    IAH 61 Specialty Accessories 4 $187.20
    IAH 60 Specialty Apparel 1 $168.00
    IAH 60 Specialty Apparel 3 $162.00
    IAH 60 Specialty Apparel 2 $150.10
    IAH 60 Specialty Apparel 1 $138.00
    IAH 60 Specialty Apparel 2 $136.00
    IAH 60 Specialty Apparel 2 $129.20
    IAH 61 Specialty Accessories 3 $126.00
    IAH 60 Specialty Apparel 1 $118.00
    IAH 60 Specialty Apparel 2 $115.20
    IAH 60 Specialty Apparel 3 $110.20
    IAH 60 Specialty Apparel 1 $108.00
    IAH 60 Specialty Apparel 1 $108.00
    IAH 61 Specialty Accessories 3 $108.00
    IAH 61 Specialty Accessories 1 $108.00
    IAH 60 Specialty Apparel 2 $102.60
    LAS 60 Specialty Apparel 2 $196.00
    LAS 60 Specialty Apparel 2 $156.00
    LAS 60 Specialty Apparel 3 $108.00
    LAS 60 Specialty Apparel 1 $98.00
    LAS 61 Specialty Accessories 1 $98.00
    LAS 60 Specialty Apparel 2 $91.20
    LAS 60 Specialty Apparel 1 $88.00
    LAS 60 Specialty Apparel 1 $88.00
    LAS 61 Specialty Accessories 1 $88.00
    LAS 60 Specialty Apparel 1 $84.00
    LAS 60 Specialty Apparel 2 $84.00
    LAS 60 Specialty Apparel 1 $76.00
    LAS 60 Specialty Apparel 1 $68.00
    LAS 61 Specialty Accessories 3 $65.00
    LAS 60 Specialty Apparel 2 $64.00
    LAS 61 Specialty Accessories 1 $61.20
    LAS 60 Specialty Apparel 1 $58.00
    LAS 60 Specialty Apparel 2 $58.00
    LAS 60 Specialty Apparel 1 $58.00
    LAS 60 Specialty Apparel 1 $49.00
    LAX 61 Specialty Accessories 11 $418.00
    LAX 60 Specialty Apparel 16 $352.00
    LAX 61 Specialty Accessories 8 $280.80
    LAX 60 Specialty Apparel 4 $270.00
    LAX 60 Specialty Apparel 4 $270.00
    LAX 60 Specialty Apparel 11 $261.60
    LAX 60 Specialty Apparel 3 $256.00
    LAX 60 Specialty Apparel 5 $250.80
    LAX 60 Specialty Apparel 6 $240.00
    LAX 60 Specialty Apparel 6 $232.00
    LAX 61 Specialty Accessories 6 $228.00
    LAX 60 Specialty Apparel 3 $220.00
    LAX 60 Specialty Apparel 5 $216.00
    LAX 61 Specialty Accessories 5 $210.00
    LAX 60 Specialty Apparel 6 $204.00
    LAX 60 Specialty Apparel 3 $196.00
    LAX 61 Specialty Accessories 5 $190.00
    LAX 60 Specialty Apparel 8 $187.20
    LAX 60 Specialty Apparel 4 $181.30
    LAX 60 Specialty Apparel 5 $170.00
    SFO 60 Specialty Apparel 3 $284.20
    SFO 60 Specialty Apparel 4 $232.00
    SFO 60 Specialty Apparel 2 $196.00
    SFO 60 Specialty Apparel 1 $188.00
    SFO 60 Specialty Apparel 1 $168.00
    SFO 60 Specialty Apparel 2 $158.00
    SFO 60 Specialty Apparel 2 $136.00
    SFO 60 Specialty Apparel 2 $116.00
    SFO 60 Specialty Apparel 2 $116.00
    SFO 60 Specialty Apparel 2 $116.00
    SFO 60 Specialty Apparel 1 $108.00
    SFO 60 Specialty Apparel 1 $108.00
    SFO 61 Specialty Accessories 3 $108.00
    SFO 61 Specialty Accessories 1 $108.00
    SFO 60 Specialty Apparel 1 $98.00
    SFO 60 Specialty Apparel 1 $98.00
    SFO 60 Specialty Apparel 1 $98.00
    SFO 60 Specialty Apparel 1 $98.00
    SFO 60 Specialty Apparel 1 $98.00
    SFO 60 Specialty Apparel 2 $96.00
    SFO 60 Specialty Apparel 2 $96.00

  7. #37
    Alanna is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2018
    Posts
    35
    Data type mismatch in criteria expression
    When you click ok it's selects this.

    In (SELECT TOP 20 T2.[SumofNet SALES]

    ignore image sorry.
    Attached Thumbnails Attached Thumbnails Untitled.png  

  8. #38
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ?? I used the data file (TOP SELLERS Query.xlsx) you provided, adjusted the SQL I provided earlier to reference this name [TOP SELLERS Query].
    I am using AccessO365.

    Don't know what else to advise.

Page 3 of 3 FirstFirst 123
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