Results 1 to 14 of 14
  1. #1
    pvl55 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9

    Append Query

    Hi All,

    Working on appending sales and quota by month.

    Currently the query works properly only if the reps have sales. I need to see their quota, even if sales are $0. ($0 sales/$100 quota)

    Any solution? I usually prefer design mode as my SQL skills are limited.

    Thanks!
    Click image for larger version. 

Name:	Append Sales_Quota.PNG 
Views:	24 
Size:	9.7 KB 
ID:	40977
    Attached Thumbnails Attached Thumbnails Access Date.PNG  

  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You are not showing the most important part of your query design window, but I assume you have two tables, one for the reps and one for their associated sales.. Right-click the join between repid in tblReps and RepID in tblSales and select either option 2 or 3 to give you all recorxs from tblReps (you needto change the join from an equijoin to a left outer join).
    Cheers,

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What is the quota field and in which table?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    pvl55 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9
    Hi Gicu,
    Thanks for replying. So the invoiced sales is the table that will contain the sales by rep, with the caveat being that if they did not sell anything they would not be on this table. The other table as all reps with each reps quota split into 2 product streams by month.
    With this structure I had to make two joins (the last pic was incorrect for some reason,please see below) one between product streams and one between rep number.
    I tried left and right joins, still missing rows.
    Click image for larger version. 

Name:	Append Sales_Quota.PNG 
Views:	24 
Size:	25.9 KB 
ID:	40979

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    pvl55 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9
    HI June7,

    Please see the DB attached.

    I need to show both Pizza and Burger numbers in the performance data for every rep. Even if they don't have sales.

    Thanks
    Attached Files Attached Files

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at the attached, if you limit to the year,quarter, month in the sales table you obviously will not include the records with no sales. So you could add them to the quota or use VBA and dynamically modify the SQL statement for the 12 (Jan to Dec) annual append queries needed.

    Cheers
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    pvl55 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9
    Click image for larger version. 

Name:	Sales Duplicates.PNG 
Views:	21 
Size:	31.0 KB 
ID:	40989It seems like the sales are being shown by month, but they are all time sales. Jan=Feb and Jan or Feb=All time sales.

  9. #9
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi, that was just meant to show you that the outer join works. To get your data start with the original query (my revised version of it) and add Is Null on a new row in the three fields where you have criteria. You should get all records with the proper sales but the ones with no sales will be missing the year, quarter,and month.

  10. #10
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255

    Risposta

    In the attachment you will find an example of how to search data dynamically and without having to use any queuing table.
    By selecting the data to be searched in the relative control, the data in the summary from time to time will be filtered.
    To delete the filter of a control, just delete the related data.
    To eliminate all the filters, use the relative button in the upper right corner.

    Sorry for my possible incorrect English being Italian.
    Attached Files Attached Files

  11. #11
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If you're still looking for an append query have a look at the attached. Please note that I have added a QuotaYear field to the Quota table.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    pvl55 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9
    Thanks Vlad for your help!

  13. #13
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome!
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    In the attached file you can find an example to manage dynamic data search without having to set the parameters in the queries in advance.
    By selecting the data to be searched in the various controls, in the subform, the data will be dynamically filtered.
    To delete all the data set for the filter at the same time, use the appropriate button at the top right.
    To cancel the filter of a single control, delete the data selected in it.
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  2. Replies: 1
    Last Post: 09-02-2015, 08:44 AM
  3. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  4. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  5. Replies: 7
    Last Post: 07-21-2011, 01:01 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