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.Deal erID, Count(tblInvoic e.DealNo) AS CountOfDealNo, Sum(tblInvoice. InvBalance) AS [Total Of InvBalance]
FROM tblInvoice
GROUP BY tblInvoice.Deal erID
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!frmMainF orm.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!frmMainF orm.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?