Quick Question - Query Help

S

Sean

I have a query and it is pulling 3 fields, Item, year, qtySold. How do I
write the query to show me only the items that sold 0 in year 2004 and 2005?

Thanks
 
M

Marshall Barton

Sean said:
I have a query and it is pulling 3 fields, Item, year, qtySold. How do I
write the query to show me only the items that sold 0 in year 2004 and 2005?
Click to expand...


That's a little too vague to be quick. Is there only one
record per item per year or are there multiple records for
each item in a year or maybe there are no records for an
item if none were sold in a year.
 
F

FatMan

Sean:
If I understand you question correctly all you should have to do is type the
following in on the criteria line of the query by example grid under the
column/field heading QtySold Year
0 =2004 Or =2005

Hope it helps,
FatMan
 
S

Sean

There will only be 1 record per item per year. Example

Item year QtySold
222 2004 1000
222 2005 500

etc.

Thanks
 
S

Sean

This does not work becuase if an item sold in 2004 and did not sell in 2005
it still shows in the results. I need the item to show only if there was 0
sales in 2004 and 0 sales in 2005.
 
M

Marshall Barton

If there is exactly one record for every item in each year,
then I think this should do it:

SELECT Y1.Item, Y1.Year
FROM thetable As Y1 INNER JOIN thetable As Y2
ON Y1.Item = Y2.Item
WHERE Y1.Year = 2004 AND Y2.Year = 2005
AND Y1.Qty = 0 AND Y2.Qty = 0
 
Top