1. ## strategy advice part 1 (A2002)

My question is in three parts. The big picture of what I need to accomplish is this: take utility bills, break out the various tax amounts reported on them, total them and then report those amounts in by quarters.

The first part of the question is this: The way the utility bills report taxes varies widely. I have around 20 distinct methods of presenting the information. There is no need to display the calculations on a worksheet so I thought the best approach to take would be to do this by code using SELECT CASE.

I have a combo box listing each method of reporting the taxes and anticipated using what is selected in the combo box as the criteria in the Case Is and then using an SQL statement I get by setting the situation up via a query grid and using the SQL it generates as the statement part of of the Case.

Does this sound like an effective efficient way to do this or is there a better way to do this?

E

2. ## Re: strategy advice part 1 (A2002)

This is in response to all three parts of your question. As I see it, it breaks down into two parts:
1. <LI>How to compute the tax amounts by quarter, taking into account that billing periods don't coincide with quarters, that tax rates change within a reporting period and that some amounts are available only as lump sums.
<LI>How to handle the different ways the taxes should be reported.
The first part can be done with queries, perhaps with the help of custom functions. You will have to tell us some more about the tables you have and their structure if you want specific advice about this.

The answer to the second part depends on how the reports differ. Again, you will have to provide details if you want us to help you.

3. ## Re: strategy advice part 1 (A2002)

1. How to compute the tax amounts by quarter, taking into account that billing periods don't coincide with quarters, that tax rates change within a billing period and that tax amounts frequently must be extracted from combined figures. The can be done with queries, perhaps with the help of custom functions. You will have to tell us some more about the tables you have and their structure if you want specific advice about this.

::I've attached my table structure here. Just in case you need this info...OrgType is whether the org is the client, the client's parent company, a taxing entity or utility company. OrgStatus is either active (this is an open case) or closed. ProviderOrg is the utility company. HowBilled defines 15 or so different ways the utility co provide billing info. OrginatorOrg is the taxer. Tax type defines state, local option 1 or local option 2

2. How to handle the different ways the taxes should be reported. The answer depends on how the reports differ. Again, you will have to provide details if you want us to help you.

::I think # 1 covered all the issues. After computing the amount of the taxes paid per billing period, the followup report should display that information by quarters.

I'm betting I've left some info out that you need. Let me know what and I'll post it.

E

4. ## Re: strategy advice part 1 (A2002)

Hi Elizabeth,

Sorry for the late reply. Perhaps the following will help, you will have to judge for yourself.

I would create a table tblQuarters that contains the start dates for the quarters. Let's say it has a single Date/Time field QuarterStart; this field is also the primary key. You can compute the last day of each quarter in a query qryQuarters: the expression for the calculated field QuarterEnd is

DateSerial(Year([QuarterStart]),Month([QuarterStart])+3,0)

Use qryQuarters together with the tables you already have to compute the period the tax is effective within each quarter. You have to determine the effective start and end date of the tax billing within a quarter, then calculate how much of the period falls within the quarter. This takes several steps, but they can be done within one query:

- For each quarter, return only those records from tblPymt whose billing period overlaps with the quarter and whose tax lifetime overlaps with the quarter.
- For those records, the effective start date is the maximum of the quarter start date, the billing start date and the tax activate date.
- And the effective end data is the minimum of the quarter end date, the billing end date and the tax deactivate date.
- The billing duration is the number of days fromthe billing start date to the billing end date.
- The effective duration is the number of days from the effective start date to the effective end date.
- The fraction of the billing duration that falls within the quarter is the effective duration divided by the billing duration.
- The effective tax amount can be calculated by multiplying the tax amount by the fraction.

For what it's worth, here is the SQL:

SELECT tblPymt.ClientOrgID, tblPymt.BillAmt, qryQuarters.QuarterStart, qryQuarters.QuarterEnd, tblPymt.BillStartDate, tblPymt.BillEndDate, tblTax.ActivateDate, tblTax.DeactivateDate, [BillEndDate]-[BillStartDate]+1 AS BillDuration, IIf([BillStartDate]<[QuarterStart],[QuarterStart],[BillStartDate]) AS FirstDate1, IIf([BillEndDate]>[QuarterEnd],[QuarterEnd],[BillEndDate]) AS LastDate1, IIf([FirstDate1]<[ActivateDate],[ActivateDate],[FirstDate1]) AS FirstDate, IIf([LastDate1]>[DeactivateDate],[DeactivateDate],[LastDate1]) AS LastDate, [LastDate]-[FirstDate] AS EffectiveDuration, [EffectiveDuration]/[BillDuration]*[BillAmt] AS EffectiveAmount
FROM qryQuarters, tblTax INNER JOIN tblPymt ON tblTax.TaxID = tblPymt.TaxID
WHERE (((tblPymt.BillStartDate)<=[QuarterEnd]) AND ((tblPymt.BillEndDate)>=[QuarterStart]) AND ((tblTax.ActivateDate)<=[QuarterEnd]) AND ((tblTax.DeactivateDate)>=[QuarterStart]));

I have attached a small demo (converted to Access 97 and zipped to keep down file size.)

5. ## Re: strategy advice part 1 (A2002)

Hans,

Are you kidding "Perhaps the following will help"? Feel like I just won the lottery; I was soooo stuck. Regards the timing, this is been very timely for me. I got smart and asked very early in the process, so I've had more than enough other stuff in the project to keep me busy, so, no worries, no problem.

As always, thanks for the help,

Elizabeth

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•