SQL help - Join Problem?

  • Thread starter Thread starter mabyn
  • Start date Start date
M

mabyn

I need all of my Attendees records to show up on a table report I am doing.
This is the query I have at this point. I tried to change the "INNER JOIN" to
"LEFT JOIN" and found that was not the fix to my problem. Every Attendee will
not have every field filled.

SELECT [Attendees].[Advertise Name], [Attendees].[CompanyName],
[Attendees].[Credit], [Attendees].[Commission], [Attendees].[Responsible
Party], [Attendees].[Advocate AE], [Attendees].[WBRZ AE],
[Registration].[Deposit Received], [Registration].[Release Signed],
[Registration].[Exhbr Confirm], [Registration].[RegistrationFee],
[Registration].[FeeScheduleID], [Fee Schedules].[FeeDescription], [Fee
Schedules].[Fee], [Payments].[PaymentAmount], [Payments].[Paid To],
[Registration].[Confirm In]
FROM Attendees INNER JOIN (([Fee Schedules] INNER JOIN Registration ON [Fee
Schedules].[FeeScheduleID]=[Registration].[FeeScheduleID]) INNER JOIN
Payments ON [Registration].[RegistrationID]=[Payments].[RegistrationID]) ON
[Attendees].[AttendeeID]=[Registration].[AttendeeID];
 
This query comes from 3 tables. I need all of the data to show up in the
query (and ultimately my report)
 
I need all of my Attendees records to show up on a table report I am doing.
This is the query I have at this point. I tried to change the "INNER JOIN"
to
"LEFT JOIN" and found that was not the fix to my problem. Every Attendee
will
not have every field filled.
(...)
Click to expand...

That is the usual thing when no match is found: since no match is found in
the "unpreserved table" (the one at the right of the words LEFT JOIN, when
you use a LEFT JOIN), which record from that table could be used ? That is
why the fields from this unpreserved table are then filled with NULL. It
indicates that one of the ON criteria was without match.


Vanderghast, Access MVP
 
Actully, it comes from 4 tables (Attendees, Fee Schedules, Registration, and
Payments).

Have you tried it with all of the joins as "LEFT JOINS"

It could also have to do with the sequence of the joins. Which table has
the most records (I would think it is probably Registration, unless you have
Attendees that have not registered, which is probably not a good idea), so
start with that table.

Then add the Attendees table, and join the two with a join that includes all
records from Registration, and only those from Attendees that match.

Then do the same with the [Fee Schedules] and [Payments], ensuring that the
joins indicate "Include all records from Registration and only those that
match from [ ].

Now, add all the fields you want into the grid. This should give you all of
the registrations, the attendee info, fee info, and payment info for each
registration. If you only want the record for the Attendees, then add a
WHERE clause like:

WHERE [Attendees].[AttendeeID] IS NULL

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



mabyn said:
This query comes from 3 tables. I need all of the data to show up in the
query (and ultimately my report)
--
Mabyn


mabyn said:
I need all of my Attendees records to show up on a table report I am doing.
This is the query I have at this point. I tried to change the "INNER JOIN" to
"LEFT JOIN" and found that was not the fix to my problem. Every Attendee will
not have every field filled.

SELECT [Attendees].[Advertise Name], [Attendees].[CompanyName],
[Attendees].[Credit], [Attendees].[Commission], [Attendees].[Responsible
Party], [Attendees].[Advocate AE], [Attendees].[WBRZ AE],
[Registration].[Deposit Received], [Registration].[Release Signed],
[Registration].[Exhbr Confirm], [Registration].[RegistrationFee],
[Registration].[FeeScheduleID], [Fee Schedules].[FeeDescription], [Fee
Schedules].[Fee], [Payments].[PaymentAmount], [Payments].[Paid To],
[Registration].[Confirm In]
FROM Attendees INNER JOIN (([Fee Schedules] INNER JOIN Registration ON [Fee
Schedules].[FeeScheduleID]=[Registration].[FeeScheduleID]) INNER JOIN
Payments ON [Registration].[RegistrationID]=[Payments].[RegistrationID]) ON
[Attendees].[AttendeeID]=[Registration].[AttendeeID];
Click to expand...
Click to expand...
 
Yes, I saw it was four tables after I sent the last reply. woops
My biggest table is ATTENDEES...which is linked to REGISTRATION. (Don't
worry about the names of these files...they are off.) My other two tables
PAYMENTS and FEE SCHEDULES are linked only to REGISTRATION. How do I say that
in a query?
--
Mabyn


Dale Fye said:
Actully, it comes from 4 tables (Attendees, Fee Schedules, Registration, and
Payments).

Have you tried it with all of the joins as "LEFT JOINS"

It could also have to do with the sequence of the joins. Which table has
the most records (I would think it is probably Registration, unless you have
Attendees that have not registered, which is probably not a good idea), so
start with that table.

Then add the Attendees table, and join the two with a join that includes all
records from Registration, and only those from Attendees that match.

Then do the same with the [Fee Schedules] and [Payments], ensuring that the
joins indicate "Include all records from Registration and only those that
match from [ ].

Now, add all the fields you want into the grid. This should give you all of
the registrations, the attendee info, fee info, and payment info for each
registration. If you only want the record for the Attendees, then add a
WHERE clause like:

WHERE [Attendees].[AttendeeID] IS NULL

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



mabyn said:
This query comes from 3 tables. I need all of the data to show up in the
query (and ultimately my report)
--
Mabyn


mabyn said:
I need all of my Attendees records to show up on a table report I am doing.
This is the query I have at this point. I tried to change the "INNER JOIN" to
"LEFT JOIN" and found that was not the fix to my problem. Every Attendee will
not have every field filled.

SELECT [Attendees].[Advertise Name], [Attendees].[CompanyName],
[Attendees].[Credit], [Attendees].[Commission], [Attendees].[Responsible
Party], [Attendees].[Advocate AE], [Attendees].[WBRZ AE],
[Registration].[Deposit Received], [Registration].[Release Signed],
[Registration].[Exhbr Confirm], [Registration].[RegistrationFee],
[Registration].[FeeScheduleID], [Fee Schedules].[FeeDescription], [Fee
Schedules].[Fee], [Payments].[PaymentAmount], [Payments].[Paid To],
[Registration].[Confirm In]
FROM Attendees INNER JOIN (([Fee Schedules] INNER JOIN Registration ON [Fee
Schedules].[FeeScheduleID]=[Registration].[FeeScheduleID]) INNER JOIN
Payments ON [Registration].[RegistrationID]=[Payments].[RegistrationID]) ON
[Attendees].[AttendeeID]=[Registration].[AttendeeID];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top