Show groups with zero values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to find a way where a grouped field displays items that have zero.

The data I have is organized thus:
Name Points Date
John 10 04/10/05
Bill 20 03/15/05
Tim 25 05/28/05
Bill 30 03/18/05

I have a totals query with Name being Grouped By; Points being Summed and
Date having a criteria (<Date())

In this case Tim has no points but I would like it to show that Tim has 0 in
the query result. I would like to see John 10; Bill 50 and Tim 0 as the
result.

I apologize if the description above is anguished.
 
Elaine,

Is this from 1 table or more than 1 with a join?

If it is more than 1 table, you can use a left/right join to get all of the
records from your Name table and just matching records from your date/points
table. If it is just one you might be able to use SumPoints: IIF([Date] <
Date(), sum([points]),0).

Jeff
 
This doesn't seem to show me that Tim is 0 (zero). Or perhaps I did something
wrong.

Jeff Shanker said:
Elaine,

Is this from 1 table or more than 1 with a join?

If it is more than 1 table, you can use a left/right join to get all of the
records from your Name table and just matching records from your date/points
table. If it is just one you might be able to use SumPoints: IIF([Date] <
Date(), sum([points]),0).

Jeff

Elaine said:
I am trying to find a way where a grouped field displays items that have zero.

The data I have is organized thus:
Name Points Date
John 10 04/10/05
Bill 20 03/15/05
Tim 25 05/28/05
Bill 30 03/18/05

I have a totals query with Name being Grouped By; Points being Summed and
Date having a criteria (<Date())

In this case Tim has no points but I would like it to show that Tim has 0 in
the query result. I would like to see John 10; Bill 50 and Tim 0 as the
result.

I apologize if the description above is anguished.
Click to expand...
Click to expand...
 
Sorry Elaine, I don't work weekends. I figured something out, it's not
pretty, but it works.

1. You need a table with just the names in it, no repeats.
2. The query is a two step process, but once you create query step 1 all you
do is make it the input for query step 2. tblNames is the table with just
the names in it and tblPoints is your old table.

Here are the 2 queries: (Step 1)

SELECT qryStep1.Name, Sum(nz([Points],0)) AS PointsEarned
FROM qryStep1
WHERE (((qryStep1.DateEarned)<Date())) OR (((qryStep1.DateEarned) Is Null))
GROUP BY qryStep1.Name;

I hope this helps.

Jeff
SELECT tblNames.Name, tblPoints.Points, tblPoints.DateEarned
FROM tblNames LEFT JOIN tblPoints ON tblNames.Name = tblPoints.Name;

Step 2:



Elaine said:
This doesn't seem to show me that Tim is 0 (zero). Or perhaps I did something
wrong.

Jeff Shanker said:
Elaine,

Is this from 1 table or more than 1 with a join?

If it is more than 1 table, you can use a left/right join to get all of the
records from your Name table and just matching records from your date/points
table. If it is just one you might be able to use SumPoints: IIF([Date] <
Date(), sum([points]),0).

Jeff

Elaine said:
I am trying to find a way where a grouped field displays items that have zero.

The data I have is organized thus:
Name Points Date
John 10 04/10/05
Bill 20 03/15/05
Tim 25 05/28/05
Bill 30 03/18/05

I have a totals query with Name being Grouped By; Points being Summed and
Date having a criteria (<Date())

In this case Tim has no points but I would like it to show that Tim has 0 in
the query result. I would like to see John 10; Bill 50 and Tim 0 as the
result.

I apologize if the description above is anguished.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top