Applying calculations to date ranges

[Tskutnik]

I need a query that will relate values to given date ranges. For Example:

Table: TaxTypeRange
Values:
TaxType; FromDate; ToDate; Contact
W2; 1/1/15; 3/31/15; Tom
1099; 4/1/15; 6/30/15; Tom
W2; 7/1/15; 12/31/15; Tom

Table: Bills
Values:
Contact; BillAmount; BillDate
Tom; 1000; 3/31/15
Tom; 1100; 4/30/15
Tom; 1200; 5/31/15
Tom; 1300; 6/30/15
Tom; 1400; 7/31/15

Requirement: For every period where the TaxType = W2 the query should multiply the Bill amount by 1.20, so the result should be something like this:

Query: BillsAndTax
Values:
Contact; BillAmount; BillDate; TotalBill
Tom; 1000; 3/31/15; 1200 (TaxType = W2 so 1000* 1.2)
Tom; 1100; 4/30/15; 1100 (TaxType = 1099 does not apply 1.20)
Tom; 1200; 5/31/15; 1200 (TaxType = 1099 does not apply 1.20)
Tom; 1300; 6/30/15; 1300 (TaxType = 1099 does not apply 1.20)
Tom; 1400; 7/31/15; 1680 (TaxType = W2 so 1400 * 1.2)

Not sure how to set this up the right way. Any help is appreciated.

 

[plog]

First make a subquery to identify just the W2 records in TaxTypeRange. Call it 'BillsAndTax_sub1':

SELECT TaxTypeRange.Contact, TaxTypeRange.FromDate, TaxTypeRange.ToDate
FROM TaxTypeRange
WHERE (((TaxTypeRange.taxtype)="W2"));

Next, you use BillsAndTax_sub1 in another query linking it to Bills via their Contact fields and then BillDate to FromDate and BillDate to ToDate . Change your Join Properties to show all from Bills. Bring down all the fields you want and then create your calculated field for TotalBill which multiplies BillAmount by 1.2 if there's a matching record in BillsAndTax_sub1 and 1 if there is not.

Then, because you can't achieve the JOIN you need using the Query Design Interface, you have to go into the SQL and change the LEFT JOIN clause on the Dates to >= and <= so it finds the exact matches. This would be the final SQL you would need to achieve your desired results:

SELECT Bills.Contact, Bills.BillAmount, Bills.BillDate, [BillAmount]*IIf(IsNull([FromDate]),1,1.2) AS TotalBill
FROM Bills LEFT JOIN BillsAndTax_sub1 ON (Bills.Contact = BillsAndTax_sub1.Contact) AND (Bills.BillDate >= BillsAndTax_sub1.FromDate) AND (Bills.BillDate <= BillsAndTax_sub1.ToDate);

Let me know if you have any questions/issues.

 

[Ranman256]

Normally, youd have a form, with the date range you want to pull.
txtStartDate, txtEndDate

then the query would read it,
select * from table where [Date] between forms!frmRpt!txtStartDAte and forms!frmRpt!txtEndDAte

 

[Tskutnik]

PLOG - thanks so much - it worked perfectly. I really appreciate the completed code.

Ranman256 - Now that i know how the query works this solution will be applied to the form date selection, as you suggested.

I really appreciate the help guys!

Issue closed