Multiple Tables in Query not Listing Data.

  • Thread starter Thread starter Ty
  • Start date Start date
T

Ty

I cannot seem to get this correct. I am trying to run a query on two
databases one of which is linked to the other. I am trying to get the query
to display a 0 for the total defects counted from one table based on the date
and time from another table. So far when I run the query I get only the
counts from the values that are not null.

Here is the SQL Statement:

SELECT Pro_Total_by_Cell.Cell, Pro_Total_by_Cell.Shift,
Pro_Total_by_Cell.Time, Pro_Total_by_Cell.ProTotal, Pro_Total_by_Cell.Time1,
Hourly_Defects_by_Cell3.CountofFC, Control_Limit_by_Cell.UCL,
IIf(IsNull([CountofFC]),"0",[CountofFC]/[ProTotal]) AS [Percent Defective]
FROM TimeCovTable INNER JOIN (Control_Limit_by_Cell INNER JOIN
(Hourly_Defects_by_Cell3 INNER JOIN Pro_Total_by_Cell ON
(Hourly_Defects_by_Cell3.Cell = Pro_Total_by_Cell.Cell) AND
(Hourly_Defects_by_Cell3.Time2 = Pro_Total_by_Cell.Time1)) ON
Control_Limit_by_Cell.Cell = Hourly_Defects_by_Cell3.Cell) ON
(TimeCovTable.TimeID = Pro_Total_by_Cell.Time1) AND (TimeCovTable.Time =
Pro_Total_by_Cell.Time)
GROUP BY Pro_Total_by_Cell.Cell, Pro_Total_by_Cell.Shift,
Pro_Total_by_Cell.Time, Pro_Total_by_Cell.ProTotal, Pro_Total_by_Cell.Time1,
Hourly_Defects_by_Cell3.CountofFC, Control_Limit_by_Cell.UCL;


Results below.

Cell Shift Time ProTotal Time1 CountofFC UCL Percent Defective
CELL 2 1 8:00 AM 46 8 1 0.045 2.17391304347826E-02



If you need further explanation please let me know.
 
Take a look in Access HELP for the Nz() function. This may allow you to
display, say, a zero when the underlying field is Null.

NOTE: based on the SQL you provided, you are telling Access to ONLY show
you data when ALL the tables having matching records. I suspect you mean to
be asking Access to show you ALL of one table's records, plus ANY matching
records from the other. If so, in the query design view, these join
properties are shown as an arrowhead-line, rather than a simple line.

In the SQL, it shows as a LEFT JOIN (or RIGHT) instead of an INNER JOIN.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
If you have nulls then you must use a left or right join.
I did not test this --
SELECT Pro_Total_by_Cell.Cell, Pro_Total_by_Cell.Shift,
Pro_Total_by_Cell.Time, Pro_Total_by_Cell.ProTotal, Pro_Total_by_Cell.Time1,
Hourly_Defects_by_Cell3.CountofFC, Control_Limit_by_Cell.UCL,
IIf(IsNull([CountofFC]),"0",[CountofFC]/[ProTotal]) AS [Percent Defective]
FROM TimeCovTable INNER JOIN (Control_Limit_by_Cell INNER JOIN (
Pro_Total_by_Cell LEFT JOIN Hourly_Defects_by_Cell3 ON
(Hourly_Defects_by_Cell3.Cell = Pro_Total_by_Cell.Cell) AND
(Hourly_Defects_by_Cell3.Time2 = Pro_Total_by_Cell.Time1)) ON
Control_Limit_by_Cell.Cell = Hourly_Defects_by_Cell3.Cell) ON
(TimeCovTable.TimeID = Pro_Total_by_Cell.Time1) AND (TimeCovTable.Time =
Pro_Total_by_Cell.Time)
GROUP BY Pro_Total_by_Cell.Cell, Pro_Total_by_Cell.Shift,
Pro_Total_by_Cell.Time, Pro_Total_by_Cell.ProTotal, Pro_Total_by_Cell.Time1,
Hourly_Defects_by_Cell3.CountofFC, Control_Limit_by_Cell.UCL;
 
Back
Top