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,
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.
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.
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.
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
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
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.
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.
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,
Thanks Vlad for your help!
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.