Query based on Dates (1 Viewer)

JahJr

Andy
Local time
Today, 03:23
Joined
Dec 3, 2008
Messages
93
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.
 

Attachments

  • sampleAcct.accdb
    1 MB · Views: 244
  • QryBilling Results Example.zip
    10.4 KB · Views: 251

June7

AWF VIP
Local time
Today, 00:23
Joined
Mar 9, 2014
Messages
5,423
I don't see how this can be accomplished with query alone.

If you need two billing lines then need two records.

Going to require VBA if you want to automate. Very complicated VBA.
 

plog

Banishment Pending
Local time
Today, 03:23
Joined
May 11, 2011
Messages
11,611
Disagree, this can be done with SQL. However, is your expected data correct?

Second set of expected data---> AccountNumber=A2, ValueDate=3/31/2020. You expect 2 records for AccountNumber=A2. Why?
I see that you have 2 records in tblFees for that AccountNumber, but the effective data for the latest one is 4/18/20 which puts it after the ValueDate of that section.

Can you explain why I am wrong if I am and provide good data if I am not?
 

June7

AWF VIP
Local time
Today, 00:23
Joined
Mar 9, 2014
Messages
5,423
Account A2: 17 days from Apr 1 at 10% and 74 days at 8% because fee rate change effective on 4/18.
Similarly for A9 and then A4 in next quarter.
 
Last edited:

JahJr

Andy
Local time
Today, 03:23
Joined
Dec 3, 2008
Messages
93
Disagree, this can be done with SQL. However, is your expected data correct?

Second set of expected data---> AccountNumber=A2, ValueDate=3/31/2020. You expect 2 records for AccountNumber=A2. Why?
I see that you have 2 records in tblFees for that AccountNumber, but the effective data for the latest one is 4/18/20 which puts it after the ValueDate of that section.

Can you explain why I am wrong if I am and provide good data if I am not?
I'm taking the value as of the last day of the Qtr and billing for the next Qtr. 3/31/2020 is the last day of Q1 2020. Take that value and bill on it for Q2 2020 which is 91 days. The new fee for account A2 starts on 4/18/2020 so the fee for the first 17 days would have been charged at the previous rate, the remaing 74 days are charged at the new rate.
 

JahJr

Andy
Local time
Today, 03:23
Joined
Dec 3, 2008
Messages
93
Account A2: 17 days from Apr 1 at 10% and 74 days at 8% because fee rate change effective on 4/18.
Similarly for A9 and then A4 in next quarter.

Why only 88 days?
Where are you getting 88 days? The first and second set should be 91 days and the 3rd set is 92 days.
 

June7

AWF VIP
Local time
Today, 00:23
Joined
Mar 9, 2014
Messages
5,423
Sorry, I somehow got 14 days in my head instead of 17 I typed. Edited my post.
 

plog

Banishment Pending
Local time
Today, 03:23
Joined
May 11, 2011
Messages
11,611
What date will the user input for the first set of data (ValueDate=12/31/2019)?
What date will the user input for the second (ValueDate=3/31/2020)?
 

JahJr

Andy
Local time
Today, 03:23
Joined
Dec 3, 2008
Messages
93
What date will the user input for the first set of data (ValueDate=12/31/2019)?
What date will the user input for the second (ValueDate=3/31/2020)?
In the example I gave the user will put in the date that is in Column E, ValueDate. In my example the user entered 12/31/2019, 3/31/2020, & 6/30/2020.
The billing should always run to the end of the next Quarter or Account Close date, or Change in Fee.
The billing should always start on the last day of the previous quarter except when there are new accounts. I did not include this in the original example.
If needed I could add an AccountOpenDate to tblAccountNumbers to make this easier.
Example to add to the sample DB
tblFees
FeeIdAccountNumberBillToAccountNumberPercentFeeEffectiveDateChangedDate
27A136.0%7/27/20207/28/2020 7:56:00 AM
tblAccountValues
IDAccountNumberAccountValueValueDate
37A13$1,500.007/27/2020

This would give the result in the last data set in the spread sheet i attached of the following:
qryBilling
AccountNumberBillToAccountNumberPercentFeeAccountValueValueDateDaysinBillingPeriodFee Charged
A136.0%$1,500.007/27/202065$16.03
 

plog

Banishment Pending
Local time
Today, 03:23
Joined
May 11, 2011
Messages
11,611
Can someone help? I am 95% of the way there, but my query keeps bombing.

Attached is the database. Main is the query that will produce the results Jah wants, however when I add sub_C.PeriodEnd to the SELECT it crashes access. Anyone know why? That field is heavily calculated in prior queries (sub_C, which is built on sub_A & sub_B), but it is never null.
 

Attachments

  • test.mdb
    692 KB · Views: 260

JahJr

Andy
Local time
Today, 03:23
Joined
Dec 3, 2008
Messages
93
Can someone help? I am 95% of the way there, but my query keeps bombing.

Attached is the database. Main is the query that will produce the results Jah wants, however when I add sub_C.PeriodEnd to the SELECT it crashes access. Anyone know why? That field is heavily calculated in prior queries (sub_C, which is built on sub_A & sub_B), but it is never null.
You seem to be getting close to the desired result, thank you. I tried it and I have never seen that problem before. I also tried it in a .accdb file, same result. Hopefully someone knows why the query is crashing Access.
 

Users who are viewing this thread

Top Bottom