Query help

[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

 

[lespaul]

can you tell us about the table(s) that you are extracting data from? can you give some sample data from those tables and which records you want to return from that data and maybe what you expect your results to look like?

 

[cbearden]

I think I was going about it wrong, I believe I'm wanting to create a crosstab query...which I'm having trouble with.

My table has these fields: dtmSent, txtName, intAcct, curGcost, curGrefund, dtmGrec, curSCcost, curSCrefund, dtmSCrec (i'll stop there...CL and O have same names as G and SC).

I need to get this information from this table:

(1) > 45 days (from received date)
Total Days: SUM([dtmGrec] - [dtmSent]) (for all categories)

(2) > 7/1/2005
> #7/1/2005# for criteria in dtmSent

(3) All curGcost, curGrefund, curSCcost, and so on Totaled up for each category(total of curGcost, curGrefund, so on)

(4) Overall total for Cost and Refund

Overall look would be:

GAP COST, GAP REF, SC COST, SC REF, CL COST, CL REF
-------- ------- ------- ------ ------- -------
2300.00 1800.00 2300.00 1534.23 253.00 230.00

Total Cost : 4853.00
Total Refund: 3564.23

Examples:

Want to retreive

Gap Cost - 400.00
Gap Ref - 304.34
Days Pending - 103

Not retrieve

Gap Cost - 400.00
Gap Ref - 304.34
Days Pending - 103

SC Cost - 1000.00
SC Refund - 830.34
Days Pending - 3

Thanks

 

[lespaul]

so dtmSent is a date, where are you getting 'Gap Cost', 'SC Cost'? is that in txtName? are you trying to group by intAcct? Could you fill in the rest of the data and then show the results from THIS data and how you got them?

For example, looking at your sample above, how do you get a total of $2300 for GAP? The only things I see above for GAP total would be 400, 400 and 1000 which equals 1800 not 2300. Why would you not retrive the second GAP cost information even though it's exactly the same as the one above that you do want to include?

Do you need to search EACH of the date fields and see if it's delinquent?

FIELDS         DATA1          DATA2             DATA3
dtmSent        7/1/2005       
txtName        ???
intAcct        ???
curGcost       400
curGrefund     304.34
dtmGrec        ???
curSCcost      1000
curSCrefund    830.34
dtmSCrec       ???
curCLcost          
curCLrefund
dtmCLrec
curOcost
curOrefund
dtmOrec

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[cbearden]

The headings GAP Cost, SC Cost, etc are the TOTAL of fields.

GAP Cost ----) curGcost
SC Cost -----) curSCcost
CL Cost -----) curCLcost
O Cost -----) curOcost

I am trying for a total of each category.

The end result that I'm trying to get is:

GAP COST, GAP REF, SC COST, SC REF, CL COST, CL REF
-------- ------- ------- ------ ------- -------
2300.00 1800.00 2300.00 1534.23 253.00 230.00

It shows the total GAP cost, the total GAP refund and so on for the rest of the categories(SC, CL, O).

$2300 was the total for all GAP costs that had occured. $1800 was the total for all GAP refunds that had occured.

$400.00 for GAP cost and $304.34 for GAP refund was just an example of one individual record.

The one that I didn't want to retrieve was because the SC category had been pending for 3 days, which I only want records that have been outstanding for 45 days or more. The GAP category was, the SC was not. That's all that was, jsut an example.

The date field is only used so that records that have an received date of 7/1/2005 or later will be retrieved. It should search through all records.

Thanks!

 

[cbearden]

I've chosen another path...ignore this thread. Thanks for the responses.

 

[lespaul]

Well, just for future reference, if you look at the following threads, (Thread183-597793 and/or Thread701-676215) you'll notice that I gave all the table names, field names, sample data and exactly what results I was looking for. It's much easier for people to help when they can see what you are seeing and I got the exact query I needed to produce the results I wanted within a post or two.

Glad you figured out a different way.

Leslie