Report from multiple tables problem

[reptar]

G'day everyone

I have two source tables "Contacts" and "OtherContacts". I have set u
a one to many relationship between the two tables. Ive constructed
OtherContacts subform to appear at the bottom of the Contacts form

The way my database is designed is so that the users can open up th
Contacts form, from there they can fill in details which will link t
the Contacts table. If that contact has addition contact information
then the OtherContacts subform is there for user input

My question is, how do i construct a Report to display all Contact an
OtherContact details. At the moment, i have managed to get a repor
from a query containing both tables which only displays the Contac
records where OtherContact fields have been filled in.

Is it possible to have a report which displays ALL my Contact
records, even if there is no OtherContacts record associated with it

 

[David Lloyd]

If I understand you correctly, the type of query you need is called a full
outer join, which Access does not currently support. However, there are
other ways to produce the same results, so this is not really an issue.

To obtain a combined result set (with or without OtherContacts), first
create a query that only shows Contacts without OtherContacts (where one of
the OtherContacts fields is null). Then UNION this query with your current
query for Contacts with OtherContacts.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


G'day everyone,

I have two source tables "Contacts" and "OtherContacts". I have set up
a one to many relationship between the two tables. Ive constructed a
OtherContacts subform to appear at the bottom of the Contacts form.

The way my database is designed is so that the users can open up the
Contacts form, from there they can fill in details which will link to
the Contacts table. If that contact has addition contact information,
then the OtherContacts subform is there for user input.

My question is, how do i construct a Report to display all Contact and
OtherContact details. At the moment, i have managed to get a report
from a query containing both tables which only displays the Contact
records where OtherContact fields have been filled in.

Is it possible to have a report which displays ALL my Contacts
records, even if there is no OtherContacts record associated with it?

 

[Douglas J Steele]

Actually, it sounds as though a LEFT JOIN is all that's required in this
situation.

SELECT Contacts.ID, Contacts.FIeld1,
Contacts.Field2, OtherContacts.Field1,
OtherContacts.Field2
FROM Contacts LEFT JOIN OtherContacts
ON Contacts.ID = OtherContacts.ID

 

[David Lloyd]

While my the solution in my previous post would work, after consuming my
first cup of coffee, the more direct approach is use an outer join on you
Contacts table so that all Contact records appear.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


G'day everyone,

I have two source tables "Contacts" and "OtherContacts". I have set up
a one to many relationship between the two tables. Ive constructed a
OtherContacts subform to appear at the bottom of the Contacts form.

The way my database is designed is so that the users can open up the
Contacts form, from there they can fill in details which will link to
the Contacts table. If that contact has addition contact information,
then the OtherContacts subform is there for user input.

My question is, how do i construct a Report to display all Contact and
OtherContact details. At the moment, i have managed to get a report
from a query containing both tables which only displays the Contact
records where OtherContact fields have been filled in.

Is it possible to have a report which displays ALL my Contacts
records, even if there is no OtherContacts record associated with it?

 

[reptar]

Thanks for both of your replies, because my table names differ fro
both tables, the UNION method seemed to have to have the exact fiel
names written for both SQL statements. Using the LEFT join metho
seems to do the job..

Thanks guy