Query showing unexpected results

[mabyn]

I've looked through messages on query and report and still can't find an
answer for what is happening. The query I have created is comprised of three
fields. I need the final query to show all records for one of the Fields
(RegistrationFee). To that I need the corresponding Company Name (Company)
and finally I need a column that shows any payments on the
account(PaymentsMade). I am doing something wrong because I am getting only
the list of Companies that have made Payments. Can someone tell me something
else to try? Thanks.

 

[Bob Barrows]

I've looked through messages on query and report and still can't find
an answer for what is happening. The query I have created is
comprised of three fields. I need the final query to show all records
for one of the Fields (RegistrationFee). To that I need the
corresponding Company Name (Company) and finally I need a column that
shows any payments on the account(PaymentsMade). I am doing something
wrong because I am getting only the list of Companies that have made
Payments. Can someone tell me something else to try? Thanks.

A picture is worth a thousand words:
Show us (in tabular format) a few rows of sample data (just enough to
illustrate the problem) Then show us (again, in tabular format) the rows
you want a query to return from that set of sample data.

 

[Jerry Whittle]

Adding to what Bob said, please provide the SQL statement for the problem
query. Go to design view the SQL view of that query. Copy and paste the sql
statement here.

 

[mabyn]

SQL -
SELECT Attendees.CompanyName, Registration.RegistrationFee,
Payments.PaymentAmount
FROM Attendees INNER JOIN (Registration INNER JOIN Payments ON
Registration.RegistrationID = Payments.RegistrationID) ON
Attendees.AttendeeID = Registration.AttendeeID;

I didn't know where the SQL was. I see now that it says JOIN and that is
probably my problem. (Still don't know how to correct)


Okay, I want it to end up like this in three columns, each with its own
Registration Fee. They will all have some sort of fee, even if it is $0 since
they have to register. They have not all paid. The fields come from three
different (linked) tables.

Company Name Registration Fee Payment Amount
A Great Company $ 500.00
Council on Aging $1000.00 $1,000.00
Best Law Firm Ever $1000.00 $500.00
HIJK, LLC $1000.00 $1000.00

 

[mabyn]

I should have added that the way the query looks now is:
Company Name Registration Fee Payment Amount
Council on Aging $1000.00 $1,000.00
Best Law Firm Ever $1000.00 $500.00
HIJK, LLC $1000.00 $1000.00

Any column that does not have a payment yet, just does not show up at all.
So, my record count of Registration Fees/Company Names is wrong.

 

[Jerry Whittle]

Try changing INNER JOIN to LEFT JOIN. If that doesn't work try RIGHT JOIN.