Changing Values On Crosstab Columns (Dates)

[BJNK]

Ok I tried searching through the forums and didn't see what I was looking for, so either Im going about this the wrong way or I didn't look hard enough Regardless here is my question..

The end result that I am looking for is to create a report - a general ledger that would show a companies outstanding receivables to us spread out from 0-29 days, to 60+ Days. Like below:

Company Name & Info Balances Due
-------------------- ------------------------
CompanyPO InvoiceDate Due Date 0-29Days |30-59Days |60+

1234 5/6/07 6/6/07 $5000
4321 4/6/07 5/6/07 $1000


And then in the report Sum the Total Owed.

So I have made a crosstab query that shows the information I want, but I cannot figure out how to Change the columns to show the dates as 0-29, 30-59 , 60+.

What kind of language would I have to use for the crosstab to work that way? Or could I at all? Would I need to creat individual queries for each of the desired headings and then somehow merge them?

This seems like a simple report to generate, something that most companies would need, but I am unsure how to do this. Any help, or links etc would be much appreciated!

Thank you!

*Hope this is formated correctly so you can see what Im talking about

 

[PHV]

Perhaps something like this (SQL code) ?
SELECT CompanyPO, InvoiceDate, [Due Date]
, IIf(([Due Date]-InvoiceDate)<=29, Amount, 0) AS [0-29Days]
, IIf(([Due Date]-InvoiceDate) Between 30 And 59, Amount, 0) AS [30-59Days]
, IIf(([Due Date]-InvoiceDate)>=60, Amount, 0) AS [60+]
FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BJNK]

Well you've definitly got a lock on what Im trying to do, I've got the code edited to my tables, and fields, but all of the values are appearing in the 30-59 Column.. What are your thoughts?

SELECT Customer_ID, CustomerPO, Due, InvoiceDate,
IIf(([Due]-[InvoiceDate])<=29,Remaining,0) AS [0-29Days],
IIf(([Due]-[InvoiceDate]) Between 30 And 59,Remaining,0) AS [30-59Days],
IIf(([Due]-[InvoiceDate])>=60,Remaining,0) AS [60+]
FROM AcctRec;

 

[PHV]

Just to be sure, try this:
SELECT Customer_ID, CustomerPO, Due, InvoiceDate,[!] (Due-InvoiceDate) AS DueDays,[/!]
IIf(([Due]-[InvoiceDate])<=29,Remaining,0) AS [0-29Days],
IIf(([Due]-[InvoiceDate]) Between 30 And 59,Remaining,0) AS [30-59Days],
IIf(([Due]-[InvoiceDate])>=60,Remaining,0) AS [60+]
FROM AcctRec

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BJNK]

That change just creates a new column which displays the difference between the Invoice Date and Due Date. It does not seem to effect the placement of the values in the age columns..

Thanks for your patience

 

[PHV]

And doesn't this new column contain only values between 30 and 59 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BJNK]

No this new column displays 30 all the way down, because the default between Invoice sent and payment due is 30 days.

Perhaps I wasn't clear, that the query is returning all outstanding values ($ owed) but is placing them all in the 30-59 Column, even when some were entered yesterday and should be in the 0-29 Days Column.

 

[PHV]

Simply choose the right dates in your query !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BJNK]

Ok I've almost got it figured out, just need some help with the SQL. The problem I was having was that we were basing the due off of the Invoice-Due, when it should have been relevant to todays Date()

So here is the code, I just don't know how to have it properly say <=Date()-30 AND >=Date()-59


SELECT Customer_ID, CustomerPO, Due, InvoiceDate,
IIf(([Due])>=Date(),Remaining,0) AS [0-29Days],
IIf(([Due]) <=Date()-1 AND >=Date()-29,Remaining,0) AS [30-59Days],
IIf(([Due]) <=Date()-30 AND >=Date()-59,Remaining,0) AS [60-89Days]
IIf(([Due])<=Date()-60,Remaining,0) AS [90+Days],
FROM AcctRec;


So if you could just kindly point out the error in my SQL you will have earned YET ANOTHER star from me lol

I will definitley be making a donation to this site!

 

[PHV]

Like this ?
SELECT Customer_ID, CustomerPO, Due, InvoiceDate,
IIf((Date()-[Due])<=0,Remaining,0) AS [0-29Days],
IIf((Date()-[Due]) Between 1 And 29,Remaining,0) AS [30-59Days],
IIf((Date()-[Due]) Between 30 And 59,Remaining,0) AS [60-89Days]
IIf(((Date()-[Due])>=60,Remaining,0) AS [90+Days]
FROM AcctRec

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BJNK]

Actually this was the finished product:

SELECT Customer_ID, CustomerPO, Due, InvoiceDate,
IIf(([Due])>=Date(),Remaining,0) AS [0-29Days],
IIf(([Due]) <=Date()-1 AND [Due]>=Date()-29,Remaining,0) AS [30-59Days],
IIf(([Due]) <=Date()-30 AND [Due]>=Date()-59,Remaining,0) AS [60-89Days]
IIf(([Due])<=Date()-60,Remaining,0) AS [90+Days],
FROM AcctRec;

Just needed to add the [Due] after the AND. But thank you very much!

 

[PHV]

And you don't think my suggestion is simpler ?

 

[BJNK]

Well to be honest I havent had an internet connection until this afternoon when I replied, so I didn't see it... And thank you for your help here and in the other thread I just started