Union Queries and Foreign Keys

[Russ Rollins]

I have a Union Query that combines two tables with identical
fields/data types. The problem I am having is trying to get the
resulting recordset to display the names of the organizations instead
of the OrgID value. The recordsets that display when running the
Queries that the Union Query is based on display the Organization name
properly. Is there a way to do this using a Union Query?
Thanks,
russr
First they ignore you. Then they laugh at you.
Then they fight you. Then you win....Mahatma Gandhi

 

[Marshall Barton]

I have a Union Query that combines two tables with identical
fields/data types. The problem I am having is trying to get the
resulting recordset to display the names of the organizations instead
of the OrgID value. The recordsets that display when running the
Queries that the Union Query is based on display the Organization name
properly. Is there a way to do this using a Union Query?

Post the queries so we can see what you are woring with.

Normally, you need to Join the table with the name to the
thatle with the orgID so you can include both fields in the
result.

 

[John W. Vinson]

I have a Union Query that combines two tables with identical
fields/data types. The problem I am having is trying to get the
resulting recordset to display the names of the organizations instead
of the OrgID value. The recordsets that display when running the
Queries that the Union Query is based on display the Organization name
properly. Is there a way to do this using a Union Query?

My guess is that the organization is a LOOKUP field in the tables upon which
these queries are based. Lookup fields are really pretty limited - the'll get
you from Point A to Point B but get totally lost beyond C (and a Union query
is about M or N on that scale <g>).

You can include the organization name in two ways: one would be to create two
queries joining your exiting two tables to the Organization table by
OrganizationID, including the organization name, and UNIONING these two
queries; or, create a query Joining the stored UNION query with the
Organization table.

John W. Vinson [MVP]

 

[Russ Rollins]

Thanks Marshall, here is the Union Query:

SELECT OrgContacts.OrgID, OrgContacts.FirstName, OrgContacts.LastName,
OrgContacts.Title, Organizations.Address1, Organizations.Address2,
Organizations.City, Organizations.Region, Organizations.PostalCode,
Organizations.Country
FROM Organizations INNER JOIN OrgContacts ON Organizations.OrgID =
OrgContacts.OrgID
UNION
SELECT Practitioners.OrgID, Practitioners.FirstName,
Practitioners.LastName, Practitioners.Title, Practitioners.Address1,
Practitioners.Address2, Practitioners.City, Practitioners.Region,
Practitioners.PostalCode, Practitioners.Country
FROM Organizations INNER JOIN Practitioners ON Organizations.OrgID =
Practitioners.OrgID;

I need to be able to display the name of the organization instead of
the ID. The OrgID fields are Foreign Keys in these two tables
(OrgContacts and Practitioners) that get their data from the
Organizations Data.

Russ Rollins

Post the queries so we can see what you are woring with.

Normally, you need to Join the table with the name to the
thatle with the orgID so you can include both fields in the
result.

First they ignore you. Then they laugh at you.
Then they fight you. Then you win....Mahatma Gandhi

 

[Russ Rollins]

On Sat, 30 Jun 2007 19:45:09 -0600, John W. Vinson

Thanks John.,
You are correct that the organization is a LOOKUP field (combo box).
The client wanted a drop-down box in the form to select organization
names from. There are only three tables (Organizations, OrgContacts,
and Practitioners). The OrgID is a Foreign Key in the last two tables,
and a Primary Key in the Organization table. I am trying to use the
Union Query to combine the OrgContacts & Practitioners tables.
Russ Rollins

My guess is that the organization is a LOOKUP field in the tables upon which
these queries are based. Lookup fields are really pretty limited - the'll get
you from Point A to Point B but get totally lost beyond C (and a Union query
is about M or N on that scale <g>).

You can include the organization name in two ways: one would be to create two
queries joining your exiting two tables to the Organization table by
OrganizationID, including the organization name, and UNIONING these two
queries; or, create a query Joining the stored UNION query with the
Organization table.

John W. Vinson [MVP]

First they ignore you. Then they laugh at you.
Then they fight you. Then you win....Mahatma Gandhi

 

[Marshall Barton]

Thanks Marshall, here is the Union Query:

SELECT OrgContacts.OrgID, OrgContacts.FirstName, OrgContacts.LastName,
OrgContacts.Title, Organizations.Address1, Organizations.Address2,
Organizations.City, Organizations.Region, Organizations.PostalCode,
Organizations.Country
FROM Organizations INNER JOIN OrgContacts ON Organizations.OrgID =
OrgContacts.OrgID
UNION
SELECT Practitioners.OrgID, Practitioners.FirstName,
Practitioners.LastName, Practitioners.Title, Practitioners.Address1,
Practitioners.Address2, Practitioners.City, Practitioners.Region,
Practitioners.PostalCode, Practitioners.Country
FROM Organizations INNER JOIN Practitioners ON Organizations.OrgID =
Practitioners.OrgID;

I need to be able to display the name of the organization instead of
the ID. The OrgID fields are Foreign Keys in these two tables
(OrgContacts and Practitioners) that get their data from the
Organizations Data.

Why can't you just include the Organizations.OrgName field
in the query?

O see from your response to John that you said that you
don't have an OrgName field. Instead you have OrgID as a
lookup field based on a value list. This is a complete
No-No. There should be a lookup **table** with the OrgID
and the OrgName (and I don't see why that should not be in
the Organizations table). The combo box on the form can
then use the RowSource query:
SELECT OrgID, OrgName
FROM Organizations
ORDER BY OrgName

 

[Russ Rollins]

Why can't you just include the Organizations.OrgName field
in the query?

O see from your response to John that you said that you
don't have an OrgName field. Instead you have OrgID as a
lookup field based on a value list. This is a complete
No-No. There should be a lookup **table** with the OrgID
and the OrgName (and I don't see why that should not be in
the Organizations table).

Actually, that is how I did it. The OrgID in the OrgContacts and
Practitioners tables are foreign keys that get their info (using
RowSource) from the Organizations table, where OrgID is the primary
key. There is a field called Organization in the Organizations table,
but not in the other two. By setting the Column Count in the combo box
to 2, and setting the Column Width property to 0";1" the tables and
the record set from the underlying queries (underlying the union
query) display the Organization name in the OrgID fields for the
OrgContacts and Practitioners tables/select queries, but the record
set from the union query does not.
Hope this provides more clarity.
Thanks for helping,
Russ Rollins

the combo box on the form can
then use the RowSource query:
SELECT OrgID, OrgName
FROM Organizations
ORDER BY OrgName

First they ignore you. Then they laugh at you.
Then they fight you. Then you win....Mahatma Gandhi

 

[John W. Vinson]

On Sat, 30 Jun 2007 19:45:09 -0600, John W. Vinson

Thanks John.,
You are correct that the organization is a LOOKUP field (combo box).
The client wanted a drop-down box in the form to select organization
names from. There are only three tables (Organizations, OrgContacts,
and Practitioners). The OrgID is a Foreign Key in the last two tables,
and a Primary Key in the Organization table. I am trying to use the
Union Query to combine the OrgContacts & Practitioners tables.

If you're assuming that you must use a Table Lookup field in order to provide
a dropdown box on a form, you're yet another victim of the Microsoft "Lookup
Wizard" misfeature. That's certainly one way to create a combo box; but you
can also have the OrgContacts and Practioners table have only a numeric
ContactID. On the Form you can have a combo box using the toolbox wizard. It's
not necessary to use the Lookup Wizard to do this!!!

THat said... your UNION query could be

SELECT OrgContacts.This, OrgContacts.That, OrgContacts.TheOther,
Organizations.OrgName
FROM Organizations INNER JOIN OrgContacts
ON OrgContacts.OrgID = Organizatons.OrgID
UNION < or UNION ALL as appropriate >
SELECT Practitioners.This, Practitioners.That, Practitioners.TheOther,
Organizations.OrgName
FROM Organizations INNER JOIN Practitioners
ON Practitioners.OrgID = Organizatons.OrgID


John W. Vinson [MVP]

 

[Marshall Barton]

Actually, that is how I did it. The OrgID in the OrgContacts and
Practitioners tables are foreign keys that get their info (using
RowSource) from the Organizations table, where OrgID is the primary
key. There is a field called Organization in the Organizations table,
but not in the other two. By setting the Column Count in the combo box
to 2, and setting the Column Width property to 0";1" the tables and
the record set from the underlying queries (underlying the union
query) display the Organization name in the OrgID fields for the
OrgContacts and Practitioners tables/select queries, but the record
set from the union query does not.

Then, the name already exists in the Organizations table and
your query alredy Joins to that table. This means that the
name field is available and all you need to do is include it
in the select field list:

SELECT OrgContacts.OrgID, OrgContacts.FirstName,
OrgContacts.LastName, OrgContacts.Title,
Organizations.Organization,
Organizations.Address1, Organizations.Address2,
Organizations.City, Organizations.Region,
Organizations.PostalCode, Organizations.Country
FROM Organizations INNER JOIN OrgContacts
ON Organizations.OrgID = OrgContacts.OrgID
UNION
SELECT Practitioners.OrgID, Practitioners.FirstName,
Practitioners.LastName, Practitioners.Title,
Organizations.Organization,
Practitioners.Address1, Practitioners.Address2,
Practitioners.City, Practitioners.Region,
Practitioners.PostalCode, Practitioners.Country
FROM Organizations INNER JOIN Practitioners
ON Organizations.OrgID = Practitioners.OrgID

You may be able to use the more efficient UNION ALL instead
of UNION.

It probably is not something I need to know, but why is one
query getting the address stuff from the org table and the
other is not?

 

[Russ Rollins]

Thanks for your help!
Russ

If you're assuming that you must use a Table Lookup field in order to provide
a dropdown box on a form, you're yet another victim of the Microsoft "Lookup
Wizard" misfeature. That's certainly one way to create a combo box; but you
can also have the OrgContacts and Practioners table have only a numeric
ContactID. On the Form you can have a combo box using the toolbox wizard. It's
not necessary to use the Lookup Wizard to do this!!!

THat said... your UNION query could be

SELECT OrgContacts.This, OrgContacts.That, OrgContacts.TheOther,
Organizations.OrgName
FROM Organizations INNER JOIN OrgContacts
ON OrgContacts.OrgID = Organizatons.OrgID
UNION < or UNION ALL as appropriate >
SELECT Practitioners.This, Practitioners.That, Practitioners.TheOther,
Organizations.OrgName
FROM Organizations INNER JOIN Practitioners
ON Practitioners.OrgID = Organizatons.OrgID


John W. Vinson [MVP]

First they ignore you. Then they laugh at you.
Then they fight you. Then you win....Mahatma Gandhi

 

[Russ Rollins]

Thanks very much
Russ

Then, the name already exists in the Organizations table and
your query alredy Joins to that table. This means that the
name field is available and all you need to do is include it
in the select field list:

SELECT OrgContacts.OrgID, OrgContacts.FirstName,
OrgContacts.LastName, OrgContacts.Title,
Organizations.Organization,
Organizations.Address1, Organizations.Address2,
Organizations.City, Organizations.Region,
Organizations.PostalCode, Organizations.Country
FROM Organizations INNER JOIN OrgContacts
ON Organizations.OrgID = OrgContacts.OrgID
UNION
SELECT Practitioners.OrgID, Practitioners.FirstName,
Practitioners.LastName, Practitioners.Title,
Organizations.Organization,
Practitioners.Address1, Practitioners.Address2,
Practitioners.City, Practitioners.Region,
Practitioners.PostalCode, Practitioners.Country
FROM Organizations INNER JOIN Practitioners
ON Organizations.OrgID = Practitioners.OrgID

You may be able to use the more efficient UNION ALL instead
of UNION.

It probably is not something I need to know, but why is one
query getting the address stuff from the org table and the
other is not?

First they ignore you. Then they laugh at you.
Then they fight you. Then you win....Mahatma Gandhi