Commissions/Receipts query

[BMoroneso]

Hi Everyone,
I have a database that is storing commission structures (between these two
dates, pay x % of revenue between y and z dollars to abc sales person),
receipts from clients, client information, sales manager information, and
sales division information.

Currently tables are linked as follows:
sales division to client (linked by sales division id)
sales divisionn to sales manager (linked by sales division id)
client to commission (linked by client id)
client to receipts (linked by client id)
commission to sales manager (linked by client id)
** note that sales managers can move between divisions, but once a
commission structure is set, that sales manager will be paid on that
commission structure until the structure expires.

problem comes in when I am querying on the receipts... the receipt records
are included in the query multiple times because of multiple commission
records, even if the commission records aren't applicable. I think I need to
fix the joins somehow, but haven't been able to come up with anything.

Thanks!!!

 

[Michel Walsh]

add a restriction on the dates, something like:

WHERE receipts.dateOfSale >= commission.betweenThisDate AND
receipts.dateOfSale < commission.andThatDate



Hoping it may help,
Vanderghast, Access MVP

 

[BMoroneso]

Here's what I think I will do...
create another table with both the commission id and receipt id (effectively
a many to many relationship between receipts and commissions. then for each
receipt I can assign which commission record(s) applies, and then also
restrict the commission records based on the revenue receipts.
thanks!