Can you provide an example of what you want here?
I don't see how your "dups" are really dups nor why you need a self join for that.
This seems like a report that can be set up on a Offers layout with sorting and sub summary layout parts to group the records.
Thanks for responding! To answer your question, an offer can have multiple status in the same report such as offer pending, accepted, rescinded, etc. I want to count them as only one offer which is why I called them dups.
I had tried to make my query simpler. Along with the above, I have another table for the open job reqs.
So here is the table structure again -
headcount table - employee ID, report date
offers table - employee ID, req id, report date...all offers have an employee ID.
reqs. table - req id, report date.
Some of the job reqs can also have an offer pending. So I consider these dups. Similar to above mentioned for the offers table, in my found set I am excluding reqs and offers which are dups.
headcount and offers are joined by report date and employee ID while the offers and req table are joined by req. id. I am trying to build a summary table of the headcount table and pulling in reqs and offers.
But I can't tell from your description and field names, which values constitute "dups". Several records in offers with the same EmployeeID are dups? or is it some other field or combination of fields that produce the duplicates?
And to repeat, can you provide an example of the report? What do you want to see on the screen or printed page exactly?
Sorry for not being clear. Yes, several records with the same employee ID are dups.
It's still not clear.
I don't see how the data in your table can possibly break down into the categories of your report. What field in Offers identifies the category to which it belongs in the above report.
And while I can see ways for your Offers table to have more than one record with the same employee ID, I don't see how that would adversely affect your report as I'd expect each such record to be counted in a different category.