All records

[Guest]

I am trying to produce a stock report from a
1) Product table (ProductID)
2) Inventory table (productID & Prorec)
3) Order detail table (productID & Quantity)

When I create a query table for 2) or 3) to give totals by product some
products do not show (These have no sales or inventory) but I would like to
see them.

Is there any way of sorting this out?
Rgs
Tony

 

[Allen Browne]

Presumably you have a query that combines the tables as the source for the
report.

Open the query in design view.
Right-click the line joining the tables in the upper pane of the query
design window. Access pops up a dialog that offers 3 options. Choose the 2nd
or 3rd one--something like:
All records from Product, and any matches from OrderDetail.

This is known as an outer join. More info in this article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Guest]

Hey, I've have just recently been having the same problem with my queries.
Unfortunately, after I changed the relationship between my two tables, it
still did not show all records from one table.

I have a table called lines. It has the values "Line 1", "Line 2", etc. I
have linked it to a query that finds the sum of any "Holds" from each line.
However, when I run the query, any Line that does not have any holds does not
show up. I want it to show a zero if there are no holds. I tried your
previous suggestion, but I was not able to get the desired output. Any help
would be great. Thank you.

 

[Guest]

Oh, I should probably include my SQL as well:

SELECT tblLines.Line, Sum(qrySelectHoldsResults.CurrentHoldQty) AS
SumOfCurrentHoldQty
FROM qrySelectHoldsResults RIGHT JOIN tblLines ON qrySelectHoldsResults.Line
= tblLines.Line
WHERE (((qrySelectHoldsResults.HoldDate) Between
NZ([forms]![frmMain]![txtStartDate],0) And
NZ([forms]![frmMain]![txtEndDate],Date())))
GROUP BY tblLines.Line;