Hey everyone,
I have two tables:
Contacts
Groups
I need to create a query that outputs a single list of all info for contacts and a column for each group which can change over time (Group names and whether a contact is in the group)
Here is what I came up with so far:
[tt]SELECT a.Number,
SUM(CASE GroupName WHEN 'Newsletter E-Mail' THEN 1 ELSE 0 END) AS 'NewsletterE-Mail'
FROM Contacts a Left Outer Join Groups b on a.Number = b.Number
GROUP BY a.Number[/tt]
I have only used 1 group so far to test.
There are a few issues:
1. This query doesn't give all info, only the Contact Number and groups. I tried to add more columns to return but got an error because they are not grouped.
2. This query isn't dynamic in terms of group names. If the group name changes or groups are added/deleted, the query won't run right.
Can someone point me in the right direction on this? I was looking at pivot table queries and got a bit lost for my application.
Thanks so much!
Ed
I have two tables:
Contacts
Number
Name
Address
...Groups
ContactID
Group Name
I need to create a query that outputs a single list of all info for contacts and a column for each group which can change over time (Group names and whether a contact is in the group)
Here is what I came up with so far:
[tt]SELECT a.Number,
SUM(CASE GroupName WHEN 'Newsletter E-Mail' THEN 1 ELSE 0 END) AS 'NewsletterE-Mail'
FROM Contacts a Left Outer Join Groups b on a.Number = b.Number
GROUP BY a.Number[/tt]
I have only used 1 group so far to test.
There are a few issues:
1. This query doesn't give all info, only the Contact Number and groups. I tried to add more columns to return but got an error because they are not grouped.
2. This query isn't dynamic in terms of group names. If the group name changes or groups are added/deleted, the query won't run right.
Can someone point me in the right direction on this? I was looking at pivot table queries and got a bit lost for my application.
Thanks so much!
Ed