Help with SQL

[Clear_Eyes]

Hello,
My project group is working on creating a nutritional database, and we seem to be stuck with some of the inner join queries. I have a few business oriented questions that I'm trying to write queries for and I was wondering if anyone knew the proper SQL. Apologies if this isn't the appropriate place to be posting.

How would I write the SQL to answer:
-What is the most popular food by dining hall? (or what are the top 5)




Access DB file is attached. {Deleted by moderator because might have contained personal data}

[rpeare]

if you can convert it to something prior to 2010 I can take a look

[rzw0wr]

I think I have 2.
The average transactions per day I am still working on.

top 5 meals per day per dinning hall:

SELECT TOP 5 Count(Food.Food_Name) AS Popular, Food.Food_Name, Dining_Services.HallID
FROM (Food INNER JOIN (Clients INNER JOIN [Transaction] ON Clients.PID = Transaction.PID) ON Food.Food_ID = Transaction.Food_ID) INNER JOIN Dining_Services ON (Transaction.Food_ID = Dining_Services.Food_ID) AND (Food.Food_ID = Dining_Services.Food_ID)
GROUP BY Food.Food_Name, Dining_Services.HallID
ORDER BY Count(Food.Food_Name) DESC;

The average cal per client:

SELECT Clients.Client_Fname, Clients.Client_Lname, Avg(Food.Calories) AS AvgOfCalories
FROM Food INNER JOIN (Clients INNER JOIN [Transaction] ON Clients.PID = Transaction.PID) ON Food.Food_ID = Transaction.Food_ID
GROUP BY Clients.Client_Fname, Clients.Client_Lname;

Dale

[rzw0wr]

rpeare, please double check my queries.
qryAverageCal and qryTop5Meals

Thanks


HokieHealthFinal2.zip
saved as mdb.
Dale

[rpeare]

I don't think your query is doing what he wants, he wants to see the most popular food items at each of the possible locations.

I don't think your tables are set up correctly to do what you want. If your dining services is supposed to be a list of what foods are available at which locations it's definitely wrong. If that's not the intent I don't know what it's for because you've set the foodID as the primary key in a one to one relationship with your FOOD table.

Not only that, but your transactions (I'm assuming this is food bought) there's no location ID in there to tell you which dining location the item was purchased from.

From what I'm looking at the table TRANSACTIONS should be modified to include the location (HALL_ID). Right now it only has the PID (person identifier) and FOOD_ID (food identifier) without that third piece of information you can't determine anything about which food location the used.

[rzw0wr]

rpeare, The tables are as they were uploaded from the OP.

I was not sure about the 2 queries at all.

Thanks for the feedback.

I will leave this in you capable hands.

Dale

[Clear_Eyes]

Thank you all for the helpful responses. I made some of the changes mentioned earlier and scrubbed some of the personal information. I didn't realize there were design flaws, hopefully this is better.

[rpeare]

SELECT Dining_Hall.Hall_Name, Food.Food_Name, Count(Transaction.Food_ID) AS CountOfFood_ID
FROM Food INNER JOIN (Dining_Hall INNER JOIN [Transaction] ON Dining_Hall.Hall_ID = Transaction.HallID) ON Food.Food_ID = Transaction.Food_ID
GROUP BY Dining_Hall.Hall_Name, Food.Food_Name
ORDER BY Dining_Hall.Hall_Name, Food.Food_Name;

This is the SQL for finding the counts of foods by purchase location

[Clear_Eyes]

This was extremely helpful. Thank you rpeare. I had one additional question that maybe you could answer. How could we list every single food for every client (as in List a client name, and for that specific name list all 55 food, and for the next client list those same 55 foods, etc)? The end goal being that we could filter the results based off of their nutrition goals. For example if a client had a goal of "Weight Loss" the foods listed for them would be < 300 calories, or a goal of "Weight Gain" the foods listed would be > 300 calories, or a goal of "Cardio Only" the foods listed would be > 30 total_carbs, and a goal of "Maintain" would just list all of the food.
We were trying to use a inner join, and then a union, but we're just not able to get the correct results.

[rpeare]

create this query:

SELECT Clients.PID, Food.Food_ID
FROM Clients, Food;

This is called a cartesian join, notice there is no link between the two, this means that a record will be created for every possible food for every possible client.

name this query qryClientMealCartesian.

Then create this query:

SELECT Clients.Client_Fname, Clients.Client_Lname, Clients.Client_Weight, qryClientMealCartesian.PID, Clients.Client_Height, Nutrition_Goals.Goal_Name, Food.Food_Name, Food.Calories, Food.Protein, Food.[Total Carbs], Food.[Total Fat], Food.Potassium, Food.Cholesterol, Food.Sodium
FROM Nutrition_Goals RIGHT JOIN ((qryClientMealCartesian LEFT JOIN Clients ON qryClientMealCartesian.PID = Clients.PID) LEFT JOIN Food ON qryClientMealCartesian.Food_ID = Food.Food_ID) ON Nutrition_Goals.Goal_ID = Clients.Goal_ID
ORDER BY Clients.Client_Lname, Clients.Client_Weight, qryClientMealCartesian.PID;