Re: [MS_AccessPros] help with query

 

Duane,

You are right , but its not my data to change.

What you showed is so helpful.

Exactly what I need.

Thanks

Sarah

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Sarah,
Your data needs to be normalized. Each combination of diet and ID should create a record in a junction table. You can get there by creating a union query.

Select ID, 1 as Num, Diet1 as Diet
From [ your table]
Union all
Select ID, 2, Diet2
From [ your table]
Where Diet2 is not null
Union all
Select ID, 3, Diet3
From [ your table]
Where Diet3 is not null
Union all
Select ID, 4, Diet4
From [ your table]
Where Diet4 is not null;

You can now create a totals query based on the union query.I

I would change the table structure of it was my application.

Duane

On May 21, 2019, at 8:22 AM, "sarahk@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

I have a query with participants name and up to 4 special diets for each: ID, diet1, diet2, diet3, diet4.

I need to count the total numbers for each diet. I think I would need to use TRANSFORM COUNT(diet1) etc, but since it is 4 separate columns I am not sure how to do this  and how to GROUP it so that I get only one column:

eg :NOSUGAR total nn

NOSALT total nn

the total would be  based on all 4 columns combined.

All help is greatly appreciated.

Sarah

__._,_.___

---

Posted by: sarahk@schemesoftware.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (19)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___