quandary on selecting certian records

  • Thread starter Thread starter javablood
  • Start date Start date
J

javablood

I query a table to pull records containing IDs and values for a specific
period. Each ID will have records with either values equal to zero or a
combination of records with values equal to zero and greater than zero. I
perform a series of calculations on those records for which there are values
greater than zero and then report the results. I want the report to show all
the IDs for that period but for those IDs that have values equal to zero and
greater than zero, I only want the greater than zero values. I am able to
create the queries and perform the calculations but I am stuck on how to
select only those IDs that have values greater than zero (when the ID has
values are greater than and equal to zero) without losing those IDs that only
have values equal to zero. Any ideas are welcome!

Thanks,
 
javablood said:
I query a table to pull records containing IDs and values for a
specific period. Each ID will have records with either values equal
to zero or a combination of records with values equal to zero and
greater than zero. I perform a series of calculations on those
records for which there are values greater than zero and then report
the results. I want the report to show all the IDs for that period
but for those IDs that have values equal to zero and greater than
zero, I only want the greater than zero values. I am able to create
the queries and perform the calculations but I am stuck on how to
select only those IDs that have values greater than zero (when the ID
has values are greater than and equal to zero) without losing those
IDs that only have values equal to zero. Any ideas are welcome!

Thanks,
Click to expand...

I'm a little lost. A picture is worth a thousand words. Could you show
us a few rows of sample data in tabular format that illustrate your
problem and then show us the results, also in tabular format, that you
want to get from that sample data? Just show the fewest number of fields
to allow you to illustrate the problem - don't try to recreate your
entire table in a newsgroup post ... :-)

something like;
ID val1 val2
1 25 0
2 25 200
etc.

and the desired results in similar format.
 
Hi,

Try something like this, as adjusted for your table, query, and field
names:

SELECT A.ID, A.The_Date, A.The_Value
FROM tblThe_Values AS A
WHERE (((A.The_Date) Between #2/1/2001# And #2/28/2001#) AND
((A.The_Value)=0) AND ((Not Exists (select * from tblThe_Values as B where
B.ID = A.ID and B.The_Date between #2/1/01# and #2/28/01# and B.The_Value <>
0))=True)) OR (((A.The_Date) Between #2/1/2001# And #2/28/2001#) AND
((A.The_Value)<>0));

The WHERE clause has two parts separated by "OR". The first chooses
records within the date range, that have a value of 0 and that do not have
any records for that same ID, in that same date range, that have values not
equal to 0. The second part chooses records within the date range that have
values not equal to 0, which eliminates those records for that ID with values
of 0.

Clifford Bass
 
Clifford,

WOW!!! That did the trick and I will have to look at it more closely to
learn how it did what it did. Thank you so mcuh!!!
 
Back
Top