If items are in a field show this result if not show that result.

Usaskater

New Member
Joined
May 7, 2010
Messages
33
I have a table with the following in MS access 2007(don’tknow if this has to be done in a SQL statement).
Bag#
Item
Date
1
Pears
5/3/13
1
Apples
5/3/13
1
Oranges
5/3/13
1
Bananas
5/3/13
1
Lettuce
5/3/13
1
Milk
5/3/13
2
Pears<o:p></o:p>
5/3/13
2
Apples <o:p></o:p>
5/3/13
2
Oranges<o:p></o:p>
5/3/13
2
Bananas<o:p></o:p>
5/3/13
2
nuts<o:p></o:p>
5/3/13
2
Candy
5/3/13
2
Milk
5/3/13
<tbody> </tbody>
Since they are groupby the bag number and showing the max date. I would like to know how to createa query that would give the result of Bag 1. So if a bag has Candy OR nutsonly show bag 1.
And another query to show if a bag has NO candy or nuts showBag2
Thank you very much for your help in advance.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
I assume you have your bags backwards since Bag 1 has neither candy nor nuts. This should work, I think:

Bags with candy or nuts:
Code:
SELECT [Bag#] 
FROM Table1 
WHERE 
    (Item = 'Candy' OR Item = 'Nuts') 
    AND 
    [Date] = #5/3/2013#

Bags with no candy and no nuts:
Code:
SELECT [Bag#] 
FROM Table1 
WHERE 
    (Item <> 'Candy' AND Item <> 'Nuts') 
    AND 
    [Date] = #5/3/2013#
 

Usaskater

New Member
Joined
May 7, 2010
Messages
33
You are correct I had it wrong. Thank you very much for the help.

First one works.

Second one shows both bag one and bag two. I wanted <>nuts or <>candy I would like it to only show only bag1 and only one line (BAG1). Currently it shows all records line by line. I just created a sample db with this and it shows

idbagnoitemdatest
21Pears5/3/2013
31Apples 5/3/2013
41Oranges5/3/2013
51Bananas5/3/2013
61Lettuce5/3/2013
71Milk5/3/2013
82Pears5/3/2013
92Apples 5/3/2013
102Oranges5/3/2013
112Bananas5/3/2013
122nuts5/3/2013
132Candy5/3/2013
142Milk5/3/2013
<caption>Table2</caption>

<thead>


</thead>
<tbody>


</tbody>
<tfoot></tfoot>
bagnoitemdatest
1Pears5/3/2013
1Apples 5/3/2013
1Oranges5/3/2013
1Bananas5/3/2013
1Lettuce5/3/2013
1Milk5/3/2013
2Pears5/3/2013
2Apples 5/3/2013
2Oranges5/3/2013
2Bananas5/3/2013
2Milk5/3/2013
<caption>Query3</caption> <thead>
</thead><tbody>
</tbody><tfoot></tfoot>
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
I see. I think we need to write <>Candy Or <>Nuts as Not(=Candy Or =Nuts):

Try this:
Code:
SELECT DISTINCT [Bag#] 
FROM 
    Table1 As t
WHERE 
    t.[Bag#] NOT IN (
                    SELECT [Bag#] FROM Table1 
                    WHERE Table1.Item = 'Candy' OR Table1.Item = 'Nuts'
                    ) 
    AND 
    t.[Date] = #5/3/2013#;

Using the DISTINCT keyword will collapse the rows down to a single row for each "bag" found. That will work for the first query too:
Code:
SELECT [B]DISTINCT[/B] [Bag#] 
FROM Table1 
WHERE 
    (Item = 'Candy' OR Item = 'Nuts') 
    AND 
    [Date] = #5/3/2013#
 

Usaskater

New Member
Joined
May 7, 2010
Messages
33
Thanks again.
on the date side. What would it be to make it Max date in the code. Let say that I have a bunch of different dates but I would like the last date that it occured with the same type of query.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
Okay, this is only lightly tested but this may work:

Bags with candy or nuts:
Code:
	SELECT 
		TSUB.[Bag#], MAX(TSUB.[Date])
	FROM
		(
		SELECT t.[Bag#], t.[Date]
		FROM Table1 AS t
		WHERE t.Item='Candy' Or t.Item='Nuts'
		) AS TSUB

	GROUP BY 
		TSUB.[Bag#]

Bags without candy or nuts:
Code:
	SELECT 
		TSUB.[Bag#], MAX(TSUB.[Date])
	FROM
		(
		SELECT t.[Bag#], t.[Date] 
		FROM Table1 AS t
		WHERE (t.Item <> 'Candy' AND t.Item <> 'Nuts') 
		) AS TSUB

	GROUP BY 
		TSUB.[Bag#]
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top