Criteria help in query

[cbearden]

I am trying to get all records that have been delinquent in any part for 45 days. Each record has these categories: G, SC, CL, O. Not all records have data in these categories.

Here's what I have:

I have an expression: Total: Sum([dtmGrec] - [dtmSent])
dtmGrec is the received date for this refund. I am using this to get the 45 days. I have an expression for each category.

The problem I'm coming across is that G may return a result of 100 days and SC returns a result of 4 days. Is there anyway that I can have the result for the SC show 0 or Null?

Thanks

 

[Pat Hartman]

I don't understand the question but the problem seems to be caused by poor normalization. If each record contained data for only one category instead of four, I doubt that you would have this problem.

 

[cbearden]

That's not possible in my case.

One record(or account) has the possibility of having cost in each category(GAP, SC, Life, Other).
I'm trying to get totals of each category. I'm trying a crosstab query now. but seem to be having trouble with it.

 

[Pat Hartman]

You can only do the crosstab on a normal 1-many relationship that involves two tables. You have flattened yours like a spreadsheet so the crosstab won't help you. Each cost category belongs in a separate ROW in a many-side table rather than in a column of the 1-side table. Do some reading on normalizaton to see what I'm talking about.