Analyze the "one" by querying the "many" 3

[keun]

I have tblDiet which contains information about food people eat during each "MealID" and I want to analyze the foods in the context of the MealID. In other words, let's say I want:

1. Every MealID where only pears were eaten
2. Every MealID where only pears and oranges were eaten
3. And the cherry on top: Every MealID where apples were not eaten

Right now I am figuring this stuff out by concatenating the food per MealID into one field and running queries against those records. I think you all probably know a better way.

name,MealID,qty,food,Date and Time
abe,A001,1,orange,1/2/2013 5:51
abe,A001,3,pear,1/2/2013 5:52
abe,A001,3,orange,1/2/2013 5:53
abe,A001,2,orange,1/2/2013 5:54
abe,A002,2,plum,1/2/2013 5:45
abe,A002,1,orange,1/2/2013 5:46
abe,A002,7,onion,1/2/2013 5:49
abe,A003,2,orange,1/2/2013 6:00
abe,A003,3,orange,1/2/2013 6:04
abe,A003,2,pear,1/2/2013 6:12
abe,A003,1,orange,1/2/2013 6:13
jen,A004,2,orange,1/3/2013 7:15
jen,A004,3,orange,1/3/2013 7:16
jen,A004,2,orange,1/3/2013 7:22
jen,A004,1,orange,1/3/2013 7:24
jen,A005,2,orange,1/3/2013 7:22
jen,A005,1,orange,1/3/2013 7:24
jen,A005,3,pear,1/3/2013 7:27
jen,A006,3,apple,1/3/2013 8:31
jen,A006,3,pear,1/3/2013 8:37
jen,A006,1,orange,1/3/2013 8:50
jen,A006,2,orange,1/3/2013 8:53
lisa,A007,2,orange,1/3/2013 7:18
lisa,A007,1,onion,1/3/2013 7:21
lisa,A007,3,orange,1/3/2013 7:27
lisa,A007,1,apple,1/3/2013 7:41
lisa,A008,2,orange,1/2/2013 5:43
lisa,A008,1,orange,1/2/2013 5:45
lisa,A008,2,orange,1/2/2013 5:50
lisa,A008,2,banana,1/2/2013 5:58
lisa,A009,2,banana,1/2/2013 7:15
lisa,A009,2,orange,1/2/2013 7:15
lisa,A009,1,orange,1/2/2013 7:17
lisa,A009,2,orange,1/2/2013 7:19
mike,A010,1,orange,1/2/2013 7:25
mike,A010,3,orange,1/2/2013 7:27
mike,A010,2,orange,1/2/2013 7:28
mike,A011,2,pear,1/2/2013 8:22
mike,A011,3,pear,1/2/2013 8:24
mike,A011,3,pear,1/2/2013 8:27


I joined this forum in 2005. I am still a hack.

 

[Andrzejek]

[pre]tblSomeTable
name MealID qty food Date and Time
abe A001 1 orange 1/2/2013 5:51
abe A001 3 pear 1/2/2013 5:52
abe A001 3 orange 1/2/2013 5:53
abe A001 2 orange 1/2/2013 5:54
abe A002 2 plum 1/2/2013 5:45
abe A002 1 orange 1/2/2013 5:46
abe A002 7 onion 1/2/2013 5:49
abe A003 2 orange 1/2/2013 6:00
[/pre]
1.
Select Distinct MealID
From tblSomeTable
Where food = 'pears'

2.
Select Distinct MealID
From tblSomeTable
Where food in ('pears', 'oranges')

3.
Select Distinct MealID
From tblSomeTable
Where food <> 'apples'
or
Select Distinct MealID
From tblSomeTable
Where food not in ('apples')


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[keun]

This is cool. Numbers one and two are good. Number three is not working, I get all MealIDs, even the ones with apples.

I joined this forum in 2005. I am still a hack.

 

[SkipVought]

Select Distinct MealID
from
Where NOT MealID In
(
Select District MealID
From
Where Food in ('apple')
)

 

[Andrzejek]

Instead of 'apple[highlight #FCE94F]s[/highlight]' try 'apple'

Actually you should have a table for all the food, something like:

[pre]
tblFood
FoodID FoodDesc
1 apple
2 orange
3 onion
4 plum[/pre]

And instead of food itself in your original table, you should have FoodID instead.

Then your SQL would be:[tt]

Select Distinct MealID
From tblSomeTable
Where food <> 1[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[keun]

Skip:
That appears to work (after I changed District to Distinct)

Andy:
This data comes to me in a huge flat log so while I prefer to work with the data like you mention, I am making do with the best I can.

I am using 'apple' so for example, the data:

name MealID qty food Date and Time
lisa A007 2.00 orange 1/3/2013 7:18:00
lisa A007 1.00 onion 1/3/2013 7:21:00
lisa A007 3.00 orange 1/3/2013 7:27:00
lisa A007 1.00 apple 1/3/2013 7:41:00
lisa A008 2.00 orange 1/2/2013 5:43:00
lisa A008 1.00 orange 1/2/2013 5:45:00
lisa A008 2.00 orange 1/2/2013 5:50:00
lisa A008 2.00 banana 1/2/2013 5:58:00

and the query:

SELECT DISTINCT MealID
FROM tblSomeTable
WHERE (((food) Not In ('apple')));

gets me:
MealID
A007
A008


I joined this forum in 2005. I am still a hack.

 

[PHV]

SELECT DISTINCT MealID
FROM tblSomeTable
WHERE MealID NOT IN (SELECT MealID FROM tblSomeTable WHERE food='apple')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

I misunderstood (or misinterpreted) your number 3. question. But you’ve got the answer from Skip. I didn’t see Skip’s post because I was typing mine, so disregard my last post.
As you can see PHV gave you the same answer as Skip did (Skip just omitted the table’s name in his SQL. Oooops)

I hope you’ve got your answers.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PHV]

Another way:

SELECT DISTINCT A.MealID
FROM tblSomeTable A LEFT JOIN (
SELECT MealID FROM tblSomeTable WHERE food='apple'
) B ON A.MealID=B.MealID
WHERE B.MealID IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[keun]

Well, I was wrong when I said that number two was working.

I am trying to find MealIDs where ONLY pears or oranges are eaten.

Select Distinct MealID
From tblSomeTable
Where food in ('pears', 'oranges')

Is returning every MealID


I joined this forum in 2005. I am still a hack.

 

[dhookom]

From your sample data, every meal has either pears or oranges so returning every MealID is accurate.

Duane
Hook'D on Access
MS Access MVP

 

[keun]

First, thanks for hanging in there with me on this.

I am looking to limit the results to where the food eaten was EXCLUSIVELY pears or oranges.

So mealid A002 should be out because of plum and onion.

abe,A001,1,orange,1/2/2013 5:51
abe,A001,3,pear,1/2/2013 5:52
abe,A001,3,orange,1/2/2013 5:53
abe,A001,2,orange,1/2/2013 5:54
abe,A002,2,plum,1/2/2013 5:45
abe,A002,1,orange,1/2/2013 5:46
abe,A002,7,onion,1/2/2013 5:49

In my real data I have sessions where people have various events. Every session has a "logged in" event and maybe a "logged out" event (unless the system logs them out in which case there is no "logged out" event in the record). There are usually a bunch of other events also during the session. I want to find the sessions where the user only logs in and logs out, with no other events recorded. Hence looking for sessions where there are only pears or oranges.

I joined this forum in 2005. I am still a hack.

 

[dhookom]

How about SQL which assumes the MealID and Food columns create a unique index:

Select MealID, Count(*) as NumOfFoods
 From tblSomeTable
 Where food in ('pears', 'oranges')
GROUP BY MealID
HAVING Count(*) = 2

Duane
Hook'D on Access
MS Access MVP

 

[keun]

That returns A007. I am thinking because there are two instances of "orange" in that MealID (but there is also apple and onion).

Is there a way I can create a temp table or query which lists all of the unique foods except pears and oranges. Then use that table as a sort of "not criteria" where I can say "give me every MealID where the foods in this list are not included." I am thinking of approaching this from a position of exclusion rather than exclusive inclusion.

I joined this forum in 2005. I am still a hack.

 

[dhookom]

Why would there be two instances of orange? Would that ever happen in your real data?

I expect you could create a query that groups by meal and food to get rid of duplicates.

Duane
Hook'D on Access
MS Access MVP

 

[keun]

Yes, my real data has users logging the same event multiple times in one session. Your idea about the query to get rid of dupes is a good one. I am going to try that; I think it will work!

I joined this forum in 2005. I am still a hack.

 

[keun]

Grouping solved the problem. I use a couple of queries to get to the solution but it got me there. I appreciate the help.

I joined this forum in 2005. I am still a hack.

 

[SkipVought]

@keun, would you please share with the other members who are following this thread with interest and will do so in the future, exactly HOW you used, "a couple of queries to get to the solution.

 

[keun]

I made it work in my "real" data but here is what I think is the way I did it. Running qryC gets me the MealIDs where only pear and orange are in the food field.

If there is a way to make this happen in one query I'd be stoked.

qryA

SELECT tblSomeTable.MealID, tblSomeTable.food
FROM tblSomeTable
GROUP BY tblSomeTable.MealID, tblSomeTable.food;

qryB

SELECT qryA.MealID, qryA.food
FROM qryA
WHERE (((qryA.food)<>"pear" And (qryA.food)<>"orange"));

qryC

SELECT tblSomeTable.MealID
FROM qryB RIGHT JOIN tblSomeTable ON qryB.MealID = tblSomeTable.MealID
GROUP BY tblSomeTable.MealID, qryB.MealID
HAVING (((qryB.MealID) Is Null));

I joined this forum in 2005. I am still a hack.