query to overcome field type problem?

I have a contacts table... let's say 2 fields- ID and email.
I have a groups table... it has a number of fields that are linked back to
the contacts field already (contact 1 thru nn), so that the groups table just
has a contact id number and I look up their email from the other table. One
field is also the "primary contact" (similarly linked)... for the person who
"owns" that group.

Works nicely so far, (thanks, techrat!). Until I try to send email to that
primary contact to have them verify the details.... Their email id doesn't
show up in the wizard, since it's just a number.

So I'm trying to write a query to also select the email address and return
it in my results... without any luck so far. I'm getting sql syntax errors.
Here's the kind of query I'm attempting:

SELECT GroupName, Primary,
(select Contacts.[E-mail Address] where Contacts.ID = Primary)
FROM Groups;

suggestions for a newbie? thanks

I believe you should be using a join rather than a subquery.

SELECT GroupName, Primary, [E-mail Address]
FROM Groups JOIN Contacts on Contacts.ID = Groups.Primary;

Also, if you multiple contact fields in the Groups table, yhou might want to
consider normalizing your table structures.

--
Duane Hookom
Microsoft Access MVP


"Slushpump" wrote:

I have a contacts table... let's say 2 fields- ID and email.
I have a groups table... it has a number of fields that are linked back to
the contacts field already (contact 1 thru nn), so that the groups table just
has a contact id number and I look up their email from the other table. One
field is also the "primary contact" (similarly linked)... for the person who
"owns" that group.

Works nicely so far, (thanks, techrat!). Until I try to send email to that
primary contact to have them verify the details.... Their email id doesn't
show up in the wizard, since it's just a number.

So I'm trying to write a query to also select the email address and return
it in my results... without any luck so far. I'm getting sql syntax errors.
Here's the kind of query I'm attempting:

SELECT GroupName, Primary,
(select Contacts.[E-mail Address] where Contacts.ID = Primary)
FROM Groups;

suggestions for a newbie? thanks

Thanks, Duane. My table design was an attempt (possibly incorrect) to
normalize things. Here's what I'm doing:

-starting with the contacts template, with several hundred records, from
Office 2007. That part is mostly dandy.
-added a second "groups" table... I have 40 groups, each with up to 10
members. We have a primary contact, that "owns" the group content. I need
to mail out to them the list of names in their group to verify that I have
the right people. Once I update the table with their email replies, then I
use the contacts table to verify all the details for each person in one or
more groups. Since 1 person can belong to multiple groups, I was just
trying to save the "id" (key) field that represents that person in the groups
table, so that I wouldn't have all this information duplicated.

But this means that when I create the form to email out to the groups
owners, I need to resolve all those ID numbers into email addresses for them
to confirm. In thinking further though, if they make a change (eg: add
another email address to their group), then that's not going to be much good,
as the update logic when I process their return email won't know how to
insert that new record into the contacts table anyway (plus, I'd need to
modify the changed groups entry to repopulate it with contact ID numbers
anyway.)

Perhaps I should give up and just maintain actual email addresses in the
groups table and stop trying to (badly) normalize the tables by using an ID
number?

I wonder if creating a view might help instead? So much for my amateur
attempts to normalize! Thanks for the help.

"Duane Hookom" wrote:

(snip)
Also, if you multiple contact fields in the Groups table, yhou might want to
consider normalizing your table structures.

--
Duane Hookom
Microsoft Access MVP

You should have one table with the PersonID and PersonEmail. Then another
table of groups with one record per group and fields like GroupID and
GroupTitle. A third table would be the junction table which would contain one
record per group per person and have at least these two fields:
PersonID
GroupID

--
Duane Hookom
Microsoft Access MVP


"Slushpump" wrote:

Thanks, Duane. My table design was an attempt (possibly incorrect) to
normalize things. Here's what I'm doing:

-starting with the contacts template, with several hundred records, from
Office 2007. That part is mostly dandy.
-added a second "groups" table... I have 40 groups, each with up to 10
members. We have a primary contact, that "owns" the group content. I need
to mail out to them the list of names in their group to verify that I have
the right people. Once I update the table with their email replies, then I
use the contacts table to verify all the details for each person in one or
more groups. Since 1 person can belong to multiple groups, I was just
trying to save the "id" (key) field that represents that person in the groups
table, so that I wouldn't have all this information duplicated.

But this means that when I create the form to email out to the groups
owners, I need to resolve all those ID numbers into email addresses for them
to confirm. In thinking further though, if they make a change (eg: add
another email address to their group), then that's not going to be much good,
as the update logic when I process their return email won't know how to
insert that new record into the contacts table anyway (plus, I'd need to
modify the changed groups entry to repopulate it with contact ID numbers
anyway.)

Perhaps I should give up and just maintain actual email addresses in the
groups table and stop trying to (badly) normalize the tables by using an ID
number?

I wonder if creating a view might help instead? So much for my amateur
attempts to normalize! Thanks for the help.

"Duane Hookom" wrote:

(snip)
Also, if you multiple contact fields in the Groups table, yhou might want to
consider normalizing your table structures.

--
Duane Hookom
Microsoft Access MVP

Appreciate the guidance, Duane... the lightbulb is starting to come on! My
guess, however is that convincing the "contacts template" to work with 3
tables and properly perform updates to the correct places might be a larger
challenge than I'm capable of solving, given my newb knowledge level.

Unless anyone has any obvious miracle cures, I think I'll revert to an out
of the box contact template and add a groups table... then will manually
manage the relationships and updates.... This will meet my immediate need,
although more manually intensive than I'd hoped. It'll also stop me from
consuming more than my fair share of the discussion group bandwidth... :-)

If there's any apps I could purchase to do this, I'd be happy to take a
look.. but Mr. Google hasn't coughed any up so far that seem to do this.

thanks again....

"Duane Hookom" wrote:

You should have one table with the PersonID and PersonEmail. Then another
table of groups with one record per group and fields like GroupID and
GroupTitle. A third table would be the junction table which would contain one
record per group per person and have at least these two fields:
PersonID
GroupID

--
Duane Hookom
Microsoft Access MVP


"Slushpump" wrote:

Thanks, Duane. My table design was an attempt (possibly incorrect) to
normalize things. Here's what I'm doing:

-starting with the contacts template, with several hundred records, from
Office 2007. That part is mostly dandy.
-added a second "groups" table... I have 40 groups, each with up to 10
members. We have a primary contact, that "owns" the group content. I need
to mail out to them the list of names in their group to verify that I have
the right people. Once I update the table with their email replies, then I
use the contacts table to verify all the details for each person in one or
more groups. Since 1 person can belong to multiple groups, I was just
trying to save the "id" (key) field that represents that person in the groups
table, so that I wouldn't have all this information duplicated.

But this means that when I create the form to email out to the groups
owners, I need to resolve all those ID numbers into email addresses for them
to confirm. In thinking further though, if they make a change (eg: add
another email address to their group), then that's not going to be much good,
as the update logic when I process their return email won't know how to
insert that new record into the contacts table anyway (plus, I'd need to
modify the changed groups entry to repopulate it with contact ID numbers
anyway.)

Perhaps I should give up and just maintain actual email addresses in the
groups table and stop trying to (badly) normalize the tables by using an ID
number?

I wonder if creating a view might help instead? So much for my amateur
attempts to normalize! Thanks for the help.

"Duane Hookom" wrote:

(snip)
Also, if you multiple contact fields in the Groups table, yhou might want to
consider normalizing your table structures.

--
Duane Hookom
Microsoft Access MVP