I've attached a sample DB and a spreadsheet which shows what the Query results should be. I've been banging my head against the wall on this for several months now.
I don't know if i have a major design flaw or if i just don't know how to write the Queries.
There is a user form and the user will select a date which will be the end of a quarter. Once this is done i would like the query to show up and have the billing amount for each account. The difficult part is the query needs to determine which % fee to use based on the date the effective date of the fee. An added wrinkle to this is sometimes the fee changes mid quarter and if this happens there should be 2 billing lines for that account, one for the days included for the first fee prior to the change and the other for the last part of the quarter.
Also the way i'm storing the fees in the table there could be 2 different fees for the exact same billing period, if this happens the query should select the fee that was most recently edited.
Any help or pointers is greatly appreciated.
I don't know if i have a major design flaw or if i just don't know how to write the Queries.
There is a user form and the user will select a date which will be the end of a quarter. Once this is done i would like the query to show up and have the billing amount for each account. The difficult part is the query needs to determine which % fee to use based on the date the effective date of the fee. An added wrinkle to this is sometimes the fee changes mid quarter and if this happens there should be 2 billing lines for that account, one for the days included for the first fee prior to the change and the other for the last part of the quarter.
Also the way i'm storing the fees in the table there could be 2 different fees for the exact same billing period, if this happens the query should select the fee that was most recently edited.
Any help or pointers is greatly appreciated.