Getting limited records..don't know why

[Angi]

I have a query, Invoices. I created a form and a report off of it.
It's only giving me the first invoice, not all of them, and I don't
understand why. I used the Nwind db as a guide so I don't see the
problem. Please help me find the problem!! There's one one-to-many
relationship which is QuoteMain to OrdersDetails by pk QuoteID (fk in
ordersDetails). I've tried changing the join, but that didn't help.
There are currently two invoices in the QuoteMain table (5201 and
5202). It's only pulling up 5201. TIA!

SQL:
SELECT QuoteMain.*, OrdersDetails.ClassID, OrdersDetails.SubclassID,
OrdersDetails.SizeID, OrdersDetails.Quantity, OrdersDetails.UnitPrice,
OrdersDetails.ExtPrice, OrdersDetails.LineNumber, OrdersDetails.Notes,
CompMain.CompanyName, CompMain.Address1, CompMain.Address2,
CompMain.City, CompMain.State, CompMain.Zip, CompMain.Phone,
CompMain.Fax, ContactMain.Salutation, ContactMain.FirstName,
ContactMain.LastName, Subclass.SubClassName, Subclass.SDesc,
Size.txtHeight, Size.txtWidth, Size.txtDepth, Class.ClassName,
Class.ClassDesc, Size.txtSize, Subclass.SDesc,
OrdersDetails.OrderDetailID
FROM ((CompMain INNER JOIN QuoteMain ON CompMain.CoID = QuoteMain.CoID)
INNER JOIN ContactMain ON QuoteMain.ContactID = ContactMain.ContactID)
INNER JOIN (Class INNER JOIN ([Size] INNER JOIN (OrdersDetails INNER
JOIN Subclass ON OrdersDetails.SubclassID = Subclass.SubClassID) ON
Size.SizeID = OrdersDetails.SizeID) ON Class.ClassID =
OrdersDetails.ClassID) ON QuoteMain.QuoteID = OrdersDetails.QuoteID;

 

[Ken Snell [MVP]]

I am guessing that the reason you "see" only one record is because the form
is in Single Forms view .. you'll need to use the navigation buttons to move
to the next record.

As for the report, are the other records on separate pages?

I don't see any problem with the query so it must be in the design of the
form and report.

 

[Ken Snell [MVP]]

Also, note that your query will return records only if there are records in
each of the children tables that are joined to the main table in the query.
If you run the query by itself, does it show all desired records?

 

[Angi]

Ken,
i used the navigation buttons...it moves to a new record. There's only
one record, but there should be two. And no, same for the
report...it's only pulling up 5201, which is two pages...the ONLY two
pages. Since I don't have any criteria set in the query, I'm pretty
positive it's supposed to be pulling up all the records. That's why I
don't understand what's going on.

 

[Angi]

Sorry, replied before I saw this one. I tried that too...still only
pulls up 5201.

 

[Ken Snell [MVP]]

So the query is not returning all the records that you expect? If this is
correct, then there is no way for the form / report to have more records.

If the query is returning only one record, then likely there is at least one
child table without records, and that means that the other record won't
show. Try changing the INNER JOIN to LEFT JOIN everywhere in the query and
see if that shows you all expected records.

 

[Kuya_D]

Hello,

I to have an Invoice database with a sub form. I am having the same problem
as Angi. All of my records do not show in the query which means they are also
not showing on the report. I did however look in my child table and notice
they are not there either and do not understand why. Any help would be
appreciated.

Thanks!!

 

[Ken Snell [MVP]]

A normal join (INNER JOIN) will return records only where there are matches
in both the parent and child table (based on the joining field(s)). An outer
join (LEFT JOIN or RIGHT JOIN) will return all records from one table and
only the matching records from the other table. When you have your
situation, you need to use an outer join. Which one you use depends upon the
structure of the query. In the query design view, if you right-click on a
join line and select Edit, you can select the join type in the window that
opens.

 

[Angi]

Ok...I tried the LEFT JOIN idea. That didn't work. It kept telling me
about ambiguous outer joins. I eliminated tables to see if I could get
what I needed and it seems that the Contactmain table was my problem.
I removed it from the query and got all the records, I put it back on,
I got a duplicate record for each contact in that company. I put in a
right join on my contact table and now it works...but only if there are
records in the ordersdetails table. I've tried a LEFT and RIGHT join
with the QuoteMain table, but it doesn't work. I'm not sure yet if
this is a good or bad thing. It's essentially a blank quote, so why
print it. Now I have to figure out a way to make sure the Quote entry
form doesn't save the record if the subform is empty so that it will
release the Quote #.

Anyway, thank you for all your help!! I'm happy to see I wasn't the
only one who has trouble with those wonderful joins!!! Here's what I
ended up with:

SELECT QuoteMain.*, OrdersDetails.ClassID, OrdersDetails.SubclassID,
OrdersDetails.SizeID, OrdersDetails.Quantity, OrdersDetails.UnitPrice,
OrdersDetails.ExtPrice, OrdersDetails.LineNumber, OrdersDetails.Notes,
CompMain.CompanyName, CompMain.Address1, CompMain.Address2,
CompMain.City, CompMain.State, CompMain.Zip, CompMain.Phone,
CompMain.Fax, Subclass.SubClassName, Subclass.SDesc, Size.txtHeight,
Size.txtWidth, Size.txtDepth, Class.ClassName, Class.ClassDesc,
Size.txtSize, Subclass.SDesc, OrdersDetails.OrderDetailID,
ContactMain.FirstName, ContactMain.LastName
FROM (ContactMain RIGHT JOIN (CompMain INNER JOIN QuoteMain ON
CompMain.CoID = QuoteMain.CoID) ON ContactMain.ContactID =
QuoteMain.ContactID) INNER JOIN (Class INNER JOIN (Subclass INNER JOIN
([Size] INNER JOIN OrdersDetails ON Size.SizeID = OrdersDetails.SizeID)
ON Subclass.SubClassID = OrdersDetails.SubclassID) ON Class.ClassID =
OrdersDetails.ClassID) ON QuoteMain.QuoteID = OrdersDetails.QuoteID;