criteria question

[aussie_user]

Hi,

I'm having trouble getting a query to return exactly the results I want and was wondering if anyone had any suggestions on what I could do.

I have a database for various projects. They are given various amounts of funding according to a schedule of payments set up at the beginning of the project. Sometimes the projects do not require all of the funding. If they have already recieved the money then they submit a refund. If they haven't received the money yet, I make journal entries to adjust the amount of the project down.

I am trying to produce a report that shows the refund and adjusted down amounts for the current year. I have dates attached to the refunds and adjusted down amounts. In my query if I set the criteria as

>#1/01/2002# for refunds then OR for adjusted down I get all entries for both fields even if they happened in a previous year. Eg. I have one project that had a refund in 2001, but the adjustment down was not made until 2002 for the remaining unpaid funds. I want a report that will agree with the reports we get from the Finance Department who only include details for the current year.

If I set the criteria as AND for the two date fields I will only get records who have a refund and adjusted down in 2002.

Is there a way I can use NOT to exclude the refund from 2001 but still retain the adjusted down figure for 2002?


Thanks for any suggestions.

 

[Pat Hartman]

It sounds like from your description that you have an unnormalized structure. Do you have specific columns for refunds and adjusted down amounts as well as their cooresponding date fields in a single table row? Or, do you have a normal transaction table structure with columns for tranType, account , date, and amount?

 

[aussie_user]

I have a table called Grants. It includes the ProjectID which links the table to other information about the project. Fields in this table include GrantAmount, AmountRefunded, DateOfRefund, AmtAdjustedDown and DateAdjustedDown.

 

[Pat Hartman]

The problem is your table design. When you select a refund for a specific period, the adjusted down amount in the same record comes along for the ride even when it is out of the date range for adjusted down amounts. And of course the same thing happens in reverse. When the adjusted down is for the desired period, any refund in the selected records tags along.

The only way to solve the problem is to normalize the recordset. You can do this by redesigning your table and forms or you can do it on the fly with a union query.

Select "Refund" As TranType, ProjectID, RefundAmt As TranAmt, RefundDate As TranDate
From YourTable
Where RefundDate >#1/01/2002#
Union Select "AdjustedDown" As TranType, ProjectID, AdjustedDownAmt As TranAmt, AdjustedDownDate As TranDate
From YourTable
Where AdjustedDownDate >#1/01/2002#;