Need Help with grouping items in a crosstab query!!!

[phishbfm]

ok,
Ive got a query that returns around 6000 entries. The info is basically items that are checked out from us. Each item is given a number, such as 7000 and a worth. what I want is a report on this query that lists on the left side each type of item, and then details about the number of that certain item that have been out for a certain period of time. basically outlined like this:

does anyone know how i could achieve this? do i need a really long IF statement? I need some sort of logic that will allow me to group the days something has been out into 1-29, 30-59, etc in a crosstab query or some other way in a report.

 

[dcx693]

There are a few methods, try this thread: Age bracket query

 

[phishbfm]

thanks for the reply...i cant view that sample DB right now because I'm at work and we dont have a zipping utility and theyre kinda selfish with what they give us access to(cant install anything!). but ill check it at home during my lunch. will it make a report possible that looks like the one above??

 

[dcx693]

I can't say for sure since I did not send the query output to a report, but I don't see why not.

 

[phishbfm]

ok, this is what i put in:

and this is what i got..

how would i be able to get this into a report somewhat like i have up above..with each individual assign number listed and then by it, the count and amount for each delinquent range?

 

[phishbfm]

bttt - anyone got any insight?

 

[Jon K]

Use the Crosstab Query Wizard to build a crosstab query based on the Assign, Range and CountOfAcct fields of your query.

Then switch to SQL view and amend the SQL statement to something like this:-

TRANSFORM First([CountOfAcct] & " - " & Format([SumOfDelAmt],"Currency"))
SELECT [Assign]
FROM [yourQuery]
GROUP BY [Assign]
PIVOT [Range];

 

[phishbfm]

that is incredible. thank you!!!