Join two table with one to many relation

[Shailesh Patel]

Dear:
I have two table in access database. And there is one to many relationship
between them.
e.g Master and Contacts table
One record in Master has many corresponding records (>=0) in Contacts.
I like to retrive all records in one sql statement.i.e one record form
Master and multiple records form Contacts.
How can I do this?
Thank you in advance for your reply.

Shailesh

 

[MGFoster]

Dear:
I have two table in access database. And there is one to many relationship
between them.
e.g Master and Contacts table
One record in Master has many corresponding records (>=0) in Contacts.
I like to retrive all records in one sql statement.i.e one record form
Master and multiple records form Contacts.
How can I do this?
Thank you in advance for your reply.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If I understand you correctly you want a result like this:

Master Contact
- ----------------- ------------------------
Joe Susan
Debbie
Harry
Elizabeth Ford
Lesley
Willem

Correct? Well, in "normal" Access (JET) you can't get that. You can
use ADO & a DataShaped query to get info formatted like that. See the
Access help article under the Contents heading "Microsoft ActiveX Data
Objects" subheading "Data Shaping."

You can get much the same thing using an INNER JOIN on the column(s)
that relate the two tables. The result set would look like this:

Master Contact
- ----------------- ------------------------
Joe Susan
Joe Debbie
Joe Harry
Elizabeth Ford
Elizabeth Lesley
Elizabeth Willem

E.g. (column ID is the relating column):

SELECT Master.*, Contact.*
FROM Master INNER JOIN Contact
ON Master.ID = Contact.ID
WHERE <criteria>


If you use this recordset in a report, you can Hide Duplicates on the
Master column by using the TextBox's Property "Hide Duplicates = Yes."

HTH

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQF9I24echKqOuFEgEQJ0lQCg7tTTolqs2r4s06ATz+RQeIV1xqMAoJEU
kJLsgWcpwbzaDAMuwd5YgpgM
=ChCI
-----END PGP SIGNATURE-----

 

[Shailesh Patel]

Dear:
You are close to the solution I need.
As you said:
Master Contact
- ----------------- ------------------------
Joe Susan
Debbie
Harry
Elizabeth Ford
Lesley
Willem
However, I need Contact column returned as follows.
Master Contact
-------------------------
Joe Susan Debbie Harry
Elizabeth Ford Lesley Willem

I am creating 3 alias of contact table but then it returns 6 records instead
of only 1.
Joe Susan Debbie Harry
Joe Susan Harry Debbie
Joe Debbie Harry Susan

How can I return only one row.

 

[MGFoster]

Dear:
You are close to the solution I need.
As you said:
Master Contact
- ----------------- ------------------------
Joe Susan
Debbie
Harry
Elizabeth Ford
Lesley
Willem
However, I need Contact column returned as follows.
Master Contact
-------------------------
Joe Susan Debbie Harry
Elizabeth Ford Lesley Willem

I am creating 3 alias of contact table but then it returns 6 records instead
of only 1.
Joe Susan Debbie Harry
Joe Susan Harry Debbie
Joe Debbie Harry Susan

How can I return only one row.

< snip >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What you want is a concatenated list of contacts. Access SQL can't
provide that. You'd have to create a function to create that list.
Here is an example of getting concatenated lists at this url:

http://www.mvps.org/access/modules/mdl0004.htm

You'd have to call it from your query like this:

SELECT Master, fConcatChild(<paramaters>)
FROM TableName
WHERE ... etc. ...

To understand the query results you show (above) it would be necessary
to see your SQL statement, and to know how the data is stored in your
tables.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQGB8hIechKqOuFEgEQKrFACgyynSLjZddy4IZe/3aL5AR/5pK+AAoM4p
rJmk7rJ1yxU/+ss1AA/QmDS+
=SsMW
-----END PGP SIGNATURE-----