Crosstab Query using date ranges

[boliches]

I am trying to get a crosstab query (in access 2000) to group data by date range.

TRANSFORM Sum(tblInvoice.InvBalance) AS SumOfInvBalance
SELECT tblInvoice.DealerID, Sum(tblInvoice.InvBalance) AS [Total Of InvBalance]
FROM tblInvoice
GROUP BY tblInvoice.DealerID
PIVOT Format(Now(),"ww");

I know the above code is wrong! My question is how do I get the query to group outstanding Invoice Balances (InvBalance) into the following date ranges (InvDate):
0 - 29 days, 30 - 59 days, 60 - 89 days, 90+ days

I am trying to develop a Debtors List by Dealer (DealerID) eg.

0 - 29 days 30 - 59 days 60 - 89 days 90+ days
Dealer1 £50 £250
Dealer2 £234 £453 £5
Dealer3 £23 £68 £1000


Hope this makes sense

[NeoPa]

I'm no good with CrossTabs at all, but couldn't you make up a field in your source (or underlying) query that uses the Switch() function to return the groupings that you want :

Switch([InvDate]-Date()<30,"A",[InvDate]-Date()<60,"B",[InvDate]-Date()<90,"C",TRUE,"D")

Grouping by the result of this should do what you want.

[boliches]

I'm no good with CrossTabs at all, but couldn't you make up a field in your source (or underlying) query that uses the Switch() function to return the groupings that you want :

Switch([InvDate]-Date()<30,"A",[InvDate]-Date()<60,"B",[InvDate]-Date()<90,"C",TRUE,"D")

Grouping by the result of this should do what you want.

Hi
Will this work in MS Access 2000?

[NeoPa]

You still need to incorporate it into your CrossTab query properly, but the function call itself should work in Access 2K, yes.

[boliches]

You still need to incorporate it into your CrossTab query properly, but the function call itself should work in Access 2K, yes.

BAFFLED!!

Have tried the following, plus variations of, to no avail!

TRANSFORM Sum(tblInvoice.InvBalance) AS SumOfInvBalance
SELECT tblInvoice.DealerID, Count(tblInvoice.DealNo) AS CountOfDealNo, Sum(tblInvoice.InvBalance) AS [Total Of InvBalance]
FROM tblInvoice
GROUP BY tblInvoice.DealerID
PIVOT Switch([InvDate] Between Now() And Now()=30,"0-30 Days",[InvDate] Between Date()+30 And Date()+60,"30 - 60 Days",[InvDate] Between Date()+60 And Date()+90,"60 - 90 Days",[InvDate]-Date()+90,"90+ Days");

Any ideas?

Thanks

[MMcCarthy]

Try this ...

TRANSFORM Sum(tblInvoice.InvBalance) AS SumOfInvBalance
SELECT tblInvoice.DealerID, Count(tblInvoice.DealNo) AS CountOfDealNo, Sum(tblInvoice.InvBalance) AS [Total Of InvBalance]
FROM tblInvoice
GROUP BY tblInvoice.DealerID
PIVOT  IIf([InvDate] Between Now() And Now()+30,"0-30 Days",
IIf([InvDate] Between Date()+30 And Date()+60,"30 - 60 Days",IIf([InvDate] Between Date()+60 And Date()+90,"60 - 90 Days","90+ Days")));
 

Mary

[boliches]

Try this ...

TRANSFORM Sum(tblInvoice.InvBalance) AS SumOfInvBalance
SELECT tblInvoice.DealerID, Count(tblInvoice.DealNo) AS CountOfDealNo, Sum(tblInvoice.InvBalance) AS [Total Of InvBalance]
FROM tblInvoice
GROUP BY tblInvoice.DealerID
PIVOT  IIf([InvDate] Between Now() And Now()+30,"0-30 Days",
IIf([InvDate] Between Date()+30 And Date()+60,"30 - 60 Days",IIf([InvDate] Between Date()+60 And Date()+90,"60 - 90 Days","90+ Days")));
 

Mary

Hi Mary
I think we are somewhere close, but at this moment all records are being placed in 90+ days whereas some records are within 30 - 60 days range etc.

I am trying to create a list of customers outstanding invoices by date range ( a "Debtors List") so that I know which are the oldest ane therefore need 'chasing' for payment. Am I going in the right direction with a Crosstab Query? Or is there a simpler solution?

Thanks.

[boliches]

Hi Mary
I think we are somewhere close, but at this moment all records are being placed in 90+ days whereas some records are within 30 - 60 days range etc.

I am trying to create a list of customers outstanding invoices by date range ( a "Debtors List") so that I know which are the oldest ane therefore need 'chasing' for payment. Am I going in the right direction with a Crosstab Query? Or is there a simpler solution?

Thanks.

Hi Mary

SOLVED IT!!!

Thank you so much for your help (and "NeoPa")

I changed the "+" to a "-" (Iif Now()-30 And Now()-60 ) and HAPPY DAYS! it works.

You guys are Brilliant!

Bruce

[boliches]

Next problem:

I would like to filter the data by "Broker", but get a message:
The Microsoft Jet Database Engine does not recognize "Forms!frmMainForm.Text265" as a valid field name or expression.

The field Text265 is on a form (frmMainForm) and holds the logged on "Brokers" initials. I have entered this as the criteria in the query but it gives the above massage when run.

I have tried : "Like", "=" but still get the message, any ideas as to how I filter the data to a given Brokers Initials?

Thanks

[MMcCarthy]

Next problem:

I would like to filter the data by "Broker", but get a message:
The Microsoft Jet Database Engine does not recognize "Forms!frmMainForm.Text265" as a valid field name or expression.

The field Text265 is on a form (frmMainForm) and holds the logged on "Brokers" initials. I have entered this as the criteria in the query but it gives the above massage when run.

I have tried : "Like", "=" but still get the message, any ideas as to how I filter the data to a given Brokers Initials?

Thanks

Open the properties for Text265 in form design view. Under the other tab check the Name property. Is it Text265?

[NeoPa]

Boliches,
Sorry I couldn't get back to you before now, but your version of what I posted was quite different from mine.
Switch is the better function in this case I believe, but using Date() reather than Now() is absolutely imperative here.

It seems you have it working, so good for you (and Mary of course), but I would nevertheless recommend (whether you use Switch(), IIf() etc whatever), that you use Date() but not Now().

[boliches]

Open the properties for Text265 in form design view. Under the other tab check the Name property. Is it Text265?

Hi

Thanks for your interest in my problem. I am using the Text265 text box in various forms to filter data with success, however it only becomes unrecognizable in this crosstab query! I have used the filter in another crosstab, again with success, whilst trying to create the finished query.

It doesnt seem to make much sense.

Any ideas?

[boliches]

Boliches,
Sorry I couldn't get back to you before now, but your version of what I posted was quite different from mine.
Switch is the better function in this case I believe, but using Date() reather than Now() is absolutely imperative here.

It seems you have it working, so good for you (and Mary of course), but I would nevertheless recommend (whether you use Switch(), IIf() etc whatever), that you use Date() but not Now().

Thanks for returning,

I was using both Date() and Now() in the code and have now changed all to Date(). (Still working!)

Regarding "Switch()" I just couldnt get it to work, I bet I was just being thick, or my coding was incorrect or something equally stupid. I would still like to try this function now just for the hell of it, but feel I need to be pointed in the right direction.

But only if you have the time!

Thanks

[MMcCarthy]

Hi

Thanks for your interest in my problem. I am using the Text265 text box in various forms to filter data with success, however it only becomes unrecognizable in this crosstab query! I have used the filter in another crosstab, again with success, whilst trying to create the finished query.

It doesnt seem to make much sense.

Any ideas?

Are you sure frmMainForm is open when this query is running?

[NeoPa]

Thanks for returning,

I was using both Date() and Now() in the code and have now changed all to Date(). (Still working!)

Great.

Regarding "Switch()" I just couldnt get it to work, I bet I was just being thick, or my coding was incorrect or something equally stupid. I would still like to try this function now just for the hell of it, but feel I need to be pointed in the right direction.

But only if you have the time!

Thanks

Switch([InvDate]-Date()<30,"A", _
       [InvDate]-Date()<60,"B", _
       [InvDate]-Date()<90,"C", _
       TRUE,"D")

How you incorporate this function call into your CrossTab I'll leave up to you (and or Mary), but essentially it is a multiple If / ElseIf construct.
I've laid it out in the relevant pairs of parameters to make it more obvious for you (Same code as before).
In each pair, the first is the comparison, and the second is the result to be returned. [InvDate]-Date() is a calculation that returns the number of days until [InvDate]. If the first pair are ignored ([InvDate]-Date()<30 is FALSE) then the second pair cut in, BUT, at this stage you already know that it is >=30. This means the second test is equivalent to 30<[InvDate]-Date()<60. The last pair has a test of TRUE, which means that one result is always returned.

[boliches]

Are you sure frmMainForm is open when this query is running?

Yes definitely open, with the text box correctly filled in!

[MMcCarthy]

Yes definitely open, with the text box correctly filled in!

Ok click in the criteria and then click on the build wizard. Choose the correct form and then select Text265 form the list and then click Value. See if that makes any difference.

[boliches]

Ok click in the criteria and then click on the build wizard. Choose the correct form and then select Text265 form the list and then click Value. See if that makes any difference.

In the crosstab query I have put the following:

TRANSFORM Sum(tblInvoice.InvBalance) AS SumOfInvBalance
SELECT tblInvoice.DealerID, Count(tblInvoice.DealNo) AS CountOfDealNo, Sum(tblInvoice.InvBalance) AS [Total Of InvBalance]
FROM tblInvoice
WHERE (((tblInvoice.Broker)=[Forms]![frmMainForm]![Text265]))
GROUP BY tblInvoice.DealerID
PIVOT IIf([InvDate] Between Date() And Date()-30,"0-30 Days",IIf([InvDate] Between Date()-30 And Date()-60,"30 - 60 Days",IIf([InvDate] Between Date()-60 And Date()-90,"60 - 90 Days",IIf([InvDate]=Date()-90,"90+ Days"))));

Couldnt see where to put "Value" in the Build - hence still not working!

Regards

[MMcCarthy]

In the crosstab query I have put the following:

TRANSFORM Sum(tblInvoice.InvBalance) AS SumOfInvBalance
SELECT tblInvoice.DealerID, Count(tblInvoice.DealNo) AS CountOfDealNo, Sum(tblInvoice.InvBalance) AS [Total Of InvBalance]
FROM tblInvoice
WHERE (((tblInvoice.Broker)=[Forms]![frmMainForm]![Text265]))
GROUP BY tblInvoice.DealerID
PIVOT IIf([InvDate] Between Date() And Date()-30,"0-30 Days",IIf([InvDate] Between Date()-30 And Date()-60,"30 - 60 Days",IIf([InvDate] Between Date()-60 And Date()-90,"60 - 90 Days",IIf([InvDate]=Date()-90,"90+ Days"))));

Couldnt see where to put "Value" in the Build - hence still not working!

Regards

It doesn't need Value. When you say it's not working do you mean you are getting an error or just no results?

[boliches]

It doesn't need Value. When you say it's not working do you mean you are getting an error or just no results?

Sorry for the delay in replying!

I get an error message:

The Microsoft Jet Database Engine does not recognize '[Forms]![frmMainForm]![Text265]' as a valid field name or expression.

Any ideas?

[boliches]

Great.

Switch([InvDate]-Date()<30,"A", _
       [InvDate]-Date()<60,"B", _
       [InvDate]-Date()<90,"C", _
       TRUE,"D")

How you incorporate this function call into your CrossTab I'll leave up to you (and or Mary), but essentially it is a multiple If / ElseIf construct.
I've laid it out in the relevant pairs of parameters to make it more obvious for you (Same code as before).
In each pair, the first is the comparison, and the second is the result to be returned. [InvDate]-Date() is a calculation that returns the number of days until [InvDate]. If the first pair are ignored ([InvDate]-Date()<30 is FALSE) then the second pair cut in, BUT, at this stage you already know that it is >=30. This means the second test is equivalent to 30<[InvDate]-Date()<60. The last pair has a test of TRUE, which means that one result is always returned.

For some reason everything (all results) end up in column "A", whereas the results should be in there appropriate section! eg.

Dealer    Count     Total   30- 60 Days  60 - 90 Days
Adams       1     £74.91                     £74.91
Magnus     18  £1,582.63     £146.88      £1,435.75
Murketts   18    £937.06     £292.28        £644.78

[NeoPa]

For some reason everything (all results) end up in column "A", whereas the results should be in there appropriate section! eg.

Dealer    Count     Total   30- 60 Days  60 - 90 Days
Adams       1     £74.91                     £74.91
Magnus     18  £1,582.63     £146.88      £1,435.75
Murketts   18    £937.06     £292.28        £644.78

You don't say what SQL you used Boliches.
My function (as I thought I'd made very clear) only enables you to determine which group each item is in. It is not a Cross-Tab query.
Or did you even mean to quote my post at all?

[boliches]

You don't say what SQL you used Boliches.
My function (as I thought I'd made very clear) only enables you to determine which group each item is in. It is not a Cross-Tab query.
Or did you even mean to quote my post at all?

As I stated earlier I was interested in your idea of using the Switch finction, however Yes I am using a crosstab query, I did enquire as to how and where I would use the Swich() - prehaps we have been at crossed purposes!

I have the crosstab working producing the required results, so thanks for your time.

Regards