Need Multiple Values on Same field In Crosstab 1

[jdegeorge]

Hi

I have a crosstab query that counts a field for me. I also want to count that same field for a date range (i.e. # of items 0-15, # of items 16-20, etc.).

Here's what the results should look like:
Owner Totals 0-15 16-20 21-25 26-30 Over 30
Anderson, LaTayna 6
DeGeorge, Jim 24
DiLaurenzio, Rob 9
Frasciello, Giulio 13
Gadson, Reggie 8
Geremia, Charlie 7
Ittner, Tom 3
Mascialino, Bob 8
Olgiati, Roberta 2

I'm using this SQL

TRANSFORM Count(tblGaps.[Gap#]) AS [CountOfGap#]
SELECT tblPassword.User AS Owner, Count(tblGaps.[Gap#]) AS Totals
FROM tblGaps LEFT JOIN tblPassword ON tblGaps.NFS_Owner = tblPassword.[UserID#]
GROUP BY tblPassword.User
ORDER BY tblGaps.DateDue
PIVOT tblGaps.DateDue;

but all that does is give me each date as the column heading. Is there a way to do this so I can get my desired column headings and a count of the DueDate field with conditions?



Jim DeGeorge

 

[dhookom]

What do you mean by "count that same field for a date range (i.e. # of items 0-15..."? How do you derive the 0-15 or 16-20?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jdegeorge]

Duane

I want to take the DateDue field and count how many records have a date value between 0-15 days old, 16-20 days old, 21-25, etc.

"[DateDue]-Date()" with condition <"16" Or Between "16" And "20" Or Between "21" And "25" Or Between "26" And "30" Or >"30" and then a count of the [Gap#] field.

Hope I'm explaining this clearly.

Jim DeGeorge

 

[dhookom]

SELECT tblPassword.User AS Owner, Count(tblGaps.[Gap#]) AS Totals, 
Sum(abs(DateDue-Date()<16)) as GLT16,
Sum(abs(DateDue-Date()>=16 AND DateDue-Date()<20)) as G16_20,
Sum(abs(DateDue-Date()>=20 AND DateDue-Date()<25)) as G20_25,
--etc--
FROM tblGaps LEFT JOIN tblPassword ON tblGaps.NFS_Owner = tblPassword.[UserID#]
GROUP BY tblPassword.User
ORDER BY tblPassword.User ;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

I should have also asked what you plans are for when the grouping changes? I would place this stuff in a lookup table for the grouping rather than using hard-coded values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jdegeorge]

Duane to the rescue (again!)

Worked perfectly. Much better than messing with a crosstab query. Thanks, and enjoy the star.

Jim DeGeorge

 

[jdegeorge]

Duane

Last question. This gives me the totals going across, but is there a way to get the totals of the columns?

Jim DeGeorge

 

[dhookom]

You get the totals of columns when you use the query in a form or report. If you think you need column totals in a query then think about:
1) your Row Heading grouping level
2) union queries

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jdegeorge]

Duane

I'll take a look at your totalling suggestions. But I have to admit one thing...I lied when I said "one last question".

You said

I should have also asked what you plans are for when the grouping changes? I would place this stuff in a lookup table for the grouping rather than using hard-coded values.

For the query that started this whole thread, I really need to keep those date heading paramters. However, there will be times when I will need to allow for flexible column headings. Would this be difficult to do?

Jim DeGeorge

 

[dhookom]

You would create a lookup table like:
[tt][blue]
tblDateDiffs
=================
DaysFrom DaysTo Title
0 5 GLT5
6 10 G06_10
11 20 G11_20
[/blue][/tt]
You could add this table to a query and set up a field and criteria like:
[green]
Field: DateDiff("D", Date(),DateDue)
Criteria: Between [DaysFrom] and [DaysTo}
[/green]
Then build a crosstab with the [Title] field as the Column Heading.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jdegeorge]

Duane

I created tblDateDiffs and added the table and that field expression w/ criteria to my crosstab query.

I get an error message that there are ambiguous outer joins, probably because there's nothing to join the new table to?

Here's the SQL

TRANSFORM Count(tblGaps.[Gap#]) AS [CountOfGap#]
SELECT tblItemStatus.Item_Status AS Status, Count(tblGaps.[Gap#]) AS Totals
FROM tblDateDiffs, tblGaps LEFT JOIN tblItemStatus ON tblGaps.Gap_Status = tblItemStatus.Item_StatusID
WHERE (((DateDiff("d",Date(),[DateDue])) Between [DaysFrom] And [DaysTo]))
GROUP BY tblItemStatus.Item_Status
PIVOT tblDateDiffs.Title;

I must be missing something.

Jim DeGeorge

 

[dhookom]

First make a query of tblDateDiffs and tblGaps. Then add this to tblItemStatus with the LEFT JOIN to make your crosstab.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jdegeorge]

Duane

Should have opened a new thread for this. You definitely deserve another star for this one! Talk about flexibility!

Thanks ever so much!

Jim DeGeorge