Combine related table info

  • Thread starter Thread starter Joel
  • Start date Start date
J

Joel

TIA:

I have a family table with a family address.

I have related a related family members table with familly member info

Is there a way through queries to end up with a single record that has the
family address from the family table and each family members info in the same
record.

I want to be able to have one table of data to do a mail merge with the
family address and each family members info like cell phone number.

Thanks,
Joel
 
This still gives me multiple records for each family member with the family
address.

SELECT Addresses.Ad1, Members.MFirstName, Members.MLastName
FROM Addresses INNER JOIN Members ON Addresses.FamilyID = Members.FamilyID;

I'm trying to get to one record with address and each family name in the
same record for example:

123 elm st, john smith, mary smith where the address came from address table
and john and mary were 2 related records in members table;

Any additional help??

Thanks,
Joel
 
Do you want John Smith and Mary Smith in one field or do you want the
two values in separate fields?

Also, do you want more than set of values? That is implied in the
statement that you want the cell phone in addition to the names.

You might take a look at one of the concatenate functions. I use a
generic Concatenate() function from Duane Hookom.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Your SQL statement would look something like the following.
SELECT Addresses.Ad1,
Concatenate("SELECT Members.MFirstName & ' ' & Members.MLastName & '
Cell: ' & CellPhone FROM Members WHERE FamilyID = " & Addresses.ID & "
ORDER BY MFirstName", Chr(13) & Chr(10)) as FamilyMembers
FROM Addresses

That should return Ad1 and a list of family members with each member
being separated (within the field) by a new line.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thanks John:

I would want the data in separate fields:
So one record in the dynaset would contain:
address field from main table
name field 1st related record
email 1st related record
phone1st related record
second name field 2nd related record
second email 2nd related record
second phone 2nd related record

the address would only be once and the name, email, and phone would be
repeated as fields as many times as there would be related records.
so:
123 elm/john/johnemail/mary/maryemail as seperate fields for example (where
one address and 2 related records.)

Does that clarify.

Thanks for any additional information.

Joel
 
Hello John:

For grins I tried the following and it doesn't work????

SELECT addresses.mcity1, Concatenate("SELECT mFirstName & ' (' & mlastname
&')' FROM members WHERE FamilyID =" & [FamilyID]) AS FirstNames
FROM addresses;

I get "undefined function concatenate" error??

What do think the problem is??

Thanks,

Joel
 
Sounds like you didn't copy Duane's Concatenate function from the website
John cited.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Joel said:
Hello John:

For grins I tried the following and it doesn't work????

SELECT addresses.mcity1, Concatenate("SELECT mFirstName & ' (' & mlastname
&')' FROM members WHERE FamilyID =" & [FamilyID]) AS FirstNames
FROM addresses;

I get "undefined function concatenate" error??

What do think the problem is??

Thanks,

Joel

John Spencer said:
Do you want John Smith and Mary Smith in one field or do you want the
two values in separate fields?

Also, do you want more than set of values? That is implied in the
statement that you want the cell phone in addition to the names.

You might take a look at one of the concatenate functions. I use a
generic Concatenate() function from Duane Hookom.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Your SQL statement would look something like the following.
SELECT Addresses.Ad1,
Concatenate("SELECT Members.MFirstName & ' ' & Members.MLastName & '
Cell: ' & CellPhone FROM Members WHERE FamilyID = " & Addresses.ID & "
ORDER BY MFirstName", Chr(13) & Chr(10)) as FamilyMembers
FROM Addresses

That should return Ad1 and a list of family members with each member
being separated (within the field) by a new line.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Click to expand...
Click to expand...
 
you got it,

thanks
Joel

Douglas J. Steele said:
Sounds like you didn't copy Duane's Concatenate function from the website
John cited.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Joel said:
Hello John:

For grins I tried the following and it doesn't work????

SELECT addresses.mcity1, Concatenate("SELECT mFirstName & ' (' & mlastname
&')' FROM members WHERE FamilyID =" & [FamilyID]) AS FirstNames
FROM addresses;

I get "undefined function concatenate" error??

What do think the problem is??

Thanks,

Joel

John Spencer said:
Do you want John Smith and Mary Smith in one field or do you want the
two values in separate fields?

Also, do you want more than set of values? That is implied in the
statement that you want the cell phone in addition to the names.

You might take a look at one of the concatenate functions. I use a
generic Concatenate() function from Duane Hookom.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Your SQL statement would look something like the following.
SELECT Addresses.Ad1,
Concatenate("SELECT Members.MFirstName & ' ' & Members.MLastName & '
Cell: ' & CellPhone FROM Members WHERE FamilyID = " & Addresses.ID & "
ORDER BY MFirstName", Chr(13) & Chr(10)) as FamilyMembers
FROM Addresses

That should return Ad1 and a list of family members with each member
being separated (within the field) by a new line.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Joel wrote:
This still gives me multiple records for each family member with the
family
address.

SELECT Addresses.Ad1, Members.MFirstName, Members.MLastName
FROM Addresses INNER JOIN Members ON Addresses.FamilyID =
Members.FamilyID;

I'm trying to get to one record with address and each family name in
the
same record for example:

123 elm st, john smith, mary smith where the address came from address
table
and john and mary were 2 related records in members table;

Any additional help??

Thanks,
Joel

:

Joel wrote:
TIA:

I have a family table with a family address.

I have related a related family members table with familly member
info

Is there a way through queries to end up with a single record that
has the
family address from the family table and each family members info in
the same
record.

I want to be able to have one table of data to do a mail merge with
the
family address and each family members info like cell phone number.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'd make a query something like this:

SELECT M.first_name, M.last_name, A.address, A.city, A.state,
A.postal_code
FROM FamilyAddresses AS A INNER JOIN FamilyMembers As M ON A.family_id
=
M.family_id
WHERE .... your criteria ....

Substitute your column & table names.

The INNER JOIN should use the relationship columns between the two
tables (the ON A.family_id = M.family_id).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSULhFYechKqOuFEgEQItxwCfZBVcoeeefyX+tsIOmObdc6eiQCcAoKvN
uetvnph5lXJkeawOQoYWM5r6
=HtHQ
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
Click to expand...
 
Back
Top