Once again, I turn to the masters for help that I thought I ought to
know by now.
I have tblAttendees and tblGuests. tblGuests has a field GuestOf
linked to tblAttendees field AttendeeID.

I have a form based on tblAttendees with a subform based on tblGuests.

I believe everything seems normalized and smooth but what I would like
to do is include the original Attendee in my subform of Guests.

I was able to do this when I created the DB with all the Guests under
one table. However, unique information about Attendees has to be kept
so they really need their own table. I have tried to create a UNION
query (which I'm not experienced much at) but to no success. I don't
even know if a UNION query is the right approach. Its a small DB and
would be fine with a redesign if thats what it takes.
Here is my UNIONqry code:


SELECT tblGuest.GuestID, tblGuest.GuestOfAttendee, tblGuest.FirstName,
tblGuest.LastName, tblGuest.IsAttendee, tblGuest.DOB, tblGuest.Sex
FROM tblGuest
UNION SELECT
tblAttendee.AttendeeID,tblAttendee.FirstName,tblAt tendee.LastName,tblAttendee.DOB,tblAttendee.Sex,tb lAttendee.MiddleName,tblAttendee.City
FROM tblAttendee
WHERE tblAttendee.AttendeeID = tblGuest.GuestOfAttendee
ORDER BY tblGuest.IsAttendee, tblGuest.DOB;

Thanks for any help!
-WhathaveIdone?

(1) If the Subform Control showing Guests is in the Detail which identifies
the Attendee, why would it be helpful to include the Attendee in the list of
Guests in the Subform Control?

(2) To include the attendee in the list of Guests in the Form embedded in
the Subform Control (assuming you are not using the Subform Control
information to Update, which you can't do using a UNION query), shouldn't be
all that difficult. Create a Query to extract the same information that you
keep on guests from the Attendees table, and but create a GuestOf calculated
field in that Query filled with the Attendee's identification comparable to
the GuestOf field in the Guests table. UNION that with the Query that
obtains Guest information, and use that as the Record Source of the Form
embedded in the Subform Control.

You may note that I have been (or tried to be) careful to NOT use the word
"subform" alone, because that is misleading and can be confusing. Access
has no object called a "Subform", but does have a Subform Control, into
which you can embed either a Form or Datasheet. Sometimes it can be very
confusing when we use "verbal shorthand" and talk about a "subform", as to
whether we are talking about the Subform Control or the Form or Datasheet
embedded therein.

Larry Linson
Microsoft Office Access MVP


"WhathaveIdone?" <brasus04 (AT) gmail (DOT) com> wrote

In a union query the sub-queries must have the same number of fields in the
SELECT clauses and the fields must be in the same order and the fields must be
of compatible types. Also, the order by clause uses the field names of the
first sub-query to impose order on the rows that are returned (no table names).

Your union query might need to look like the following. I am guessing on
field types and the data represented by the fields. For instance, I am
guessing the GuestOfAttendee is a foreign key field for AttendeeID.

SELECT tblGuest.GuestOfAttendee as AttendeeID
, tblGuest.GuestID,
, tblGuest.FirstName, tblGuest.LastName
, tblGuest.IsAttendee, tblGuest.DOB, tblGuest.Sex,
, Null as MiddleName, Null As City
FROM tblGuest
UNION ALL
SELECT tblAttendee.AttendeeID
, Null as GuestID,
,tblAttendee.FirstName,tblAttendee.LastName
, False as IsAttendee ,tblAttendee.DOB,tblAttendee.Sex
,tblAttendee.MiddleName,tblAttendee.City
FROM tblAttendee
ORDER BY isAttendee, DOB

The link between the form and the sub-form would be AttendeeID.

As noted the results of a union query cannot be updated, deleted, or appended.

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

On 1/6/2011 12:14 PM, WhathaveIdone? wrote: