query for a report

C

cporter

I need to generate a report regularly that covers the status of several
machines listed in a table. I have several queries that distill the
information needed but the end result only includes those machines that
were actually worked on and I need to report on all of those in the
list. How would you suggest I compare two queries/tables, pull in the
machid for those NOT worked on, and then auto-populate the required
data?



a partial listing of the tools required on the report

MACHID TYPE
KLA-407 I
KLA-460 I
KLA-638 I
KLA-700 I
KLA-755 I
KLA-774 I
KLA-017 I
KLA-501 I
KLA-947 I
KLA-779 I
KLA-009 I
ATQ-050 L
ATQ-049 L
ATQ-026 L
ATQ-031 L
ATQ-033 L
ATQ-047 L
EBM-154 L
EBM-160 L


SQL statement of the query gathering info for the report

SELECT qryIndicesPt5.MACHID, qryIndicesPt5.SumOfHD,
qryIndicesPt5.SumOfCount, qryIndicesmtbfPt5.SumOfmtbfhd,
qryIndicesmtbfPt5.SumOfCount, qryIndicesMSSC3.MaxOfSumOfSumOfCount,
([SumOfHD]-[SumOfmtbfhd])/[SumOfHD] AS BDR,
[MaxOfSumOfSumOfCount]/([qryIndicesPt5.SumOfCount]-[qryIndicesmtbfPt5.SumOfCount])
AS RR, ([qryIndicesPt5.SumOfCount]-[qryIndicesmtbfPt5.SumOfCount]) AS
OCC,
(((((Forms!frmRptDates!StopDate+Forms!frmRptDates!StopTime)-(Forms!frmRptDates!StartDate+Forms!frmRptDates!StartTime))*24)-[SumOfHD])+[SumOfmtbfhd])/([qryIndicesPt5.SumOfCount]-[qryIndicesmtbfPt5.SumOfCount])
AS mtbf,
(([SumOfHD]-[SumOfmtbfhd])/([qryIndicesPt5.SumOfCount]-[qryIndicesmtbfPt5.SumOfCount]))
AS mttr,
((((Forms!frmRptDates!StopDate+Forms!frmRptDates!StopTime)-(Forms!frmRptDates!StartDate+Forms!frmRptDates!StartTime))*24)-[SumOfHD])/(((Forms!frmRptDates!StopDate+Forms!frmRptDates!StopTime)-(Forms!frmRptDates!StartDate+Forms!frmRptDates!StartTime))*24)
AS avail
FROM (qryIndicesPt5 INNER JOIN qryIndicesmtbfPt5 ON
qryIndicesPt5.MACHID=qryIndicesmtbfPt5.MACHID) INNER JOIN
qryIndicesMSSC3 ON qryIndicesmtbfPt5.MACHID=qryIndicesMSSC3.MACHID;


Sample data

MACHID SumOfHD qryIndicesPt5.SumOfCount SumOfmtbfhd qryIndicesmtbfPt5.SumOfCount MaxOfSumOfSumOfCount BDR RR OCC mtbf mttr avail
ACS-215 6.08 8 2.75 4 2 54.79% 50.00% 4 179.17 0.83 99.16%
ASC-059 3.25 7 2.50 5 2 23.08% 100.00% 2 359.63 0.37 99.55%
ASC-071 3.00 8 1.00 4 1 66.67% 25.00% 4 179.50 0.50 99.58%
ASC-103 2.83 9 1.00 4 3 64.71% 60.00% 5 143.63 0.37 99.61%
 
T

Tom Ellison

Dear C. Porter:

Likely it is the case that you do not want all the JOINs to be INNER.
Whether you need LEFT or RIGHT JOINs will depend on details I cannot study
here. Perhaps you could work this out yourself by reading the documentation
on this and learning how it works. You can also replace your INNER JOINs
with all combinations of LEFT and RIGHT, either one or both. Best, however,
is that you learn how this works. You'll then not have a problem with it
again.

Tom Ellison


I need to generate a report regularly that covers the status of several
machines listed in a table. I have several queries that distill the
information needed but the end result only includes those machines that
were actually worked on and I need to report on all of those in the
list. How would you suggest I compare two queries/tables, pull in the
machid for those NOT worked on, and then auto-populate the required
data?



a partial listing of the tools required on the report

MACHID TYPE
KLA-407 I
KLA-460 I
KLA-638 I
KLA-700 I
KLA-755 I
KLA-774 I
KLA-017 I
KLA-501 I
KLA-947 I
KLA-779 I
KLA-009 I
ATQ-050 L
ATQ-049 L
ATQ-026 L
ATQ-031 L
ATQ-033 L
ATQ-047 L
EBM-154 L
EBM-160 L


SQL statement of the query gathering info for the report

SELECT qryIndicesPt5.MACHID, qryIndicesPt5.SumOfHD,
qryIndicesPt5.SumOfCount, qryIndicesmtbfPt5.SumOfmtbfhd,
qryIndicesmtbfPt5.SumOfCount, qryIndicesMSSC3.MaxOfSumOfSumOfCount,
([SumOfHD]-[SumOfmtbfhd])/[SumOfHD] AS BDR,
[MaxOfSumOfSumOfCount]/([qryIndicesPt5.SumOfCount]-[qryIndicesmtbfPt5.SumOfCount])
AS RR, ([qryIndicesPt5.SumOfCount]-[qryIndicesmtbfPt5.SumOfCount]) AS
OCC,
(((((Forms!frmRptDates!StopDate+Forms!frmRptDates!StopTime)-(Forms!frmRptDates!StartDate+Forms!frmRptDates!StartTime))*24)-[SumOfHD])+[SumOfmtbfhd])/([qryIndicesPt5.SumOfCount]-[qryIndicesmtbfPt5.SumOfCount])
AS mtbf,
(([SumOfHD]-[SumOfmtbfhd])/([qryIndicesPt5.SumOfCount]-[qryIndicesmtbfPt5.SumOfCount]))
AS mttr,
((((Forms!frmRptDates!StopDate+Forms!frmRptDates!StopTime)-(Forms!frmRptDates!StartDate+Forms!frmRptDates!StartTime))*24)-[SumOfHD])/(((Forms!frmRptDates!StopDate+Forms!frmRptDates!StopTime)-(Forms!frmRptDates!StartDate+Forms!frmRptDates!StartTime))*24)
AS avail
FROM (qryIndicesPt5 INNER JOIN qryIndicesmtbfPt5 ON
qryIndicesPt5.MACHID=qryIndicesmtbfPt5.MACHID) INNER JOIN
qryIndicesMSSC3 ON qryIndicesmtbfPt5.MACHID=qryIndicesMSSC3.MACHID;


Sample data

MACHID SumOfHD qryIndicesPt5.SumOfCount SumOfmtbfhd
qryIndicesmtbfPt5.SumOfCount MaxOfSumOfSumOfCount BDR RR OCC mtbf mttr
avail
ACS-215 6.08 8 2.75 4 2 54.79% 50.00% 4 179.17 0.83 99.16%
ASC-059 3.25 7 2.50 5 2 23.08% 100.00% 2 359.63 0.37 99.55%
ASC-071 3.00 8 1.00 4 1 66.67% 25.00% 4 179.50 0.50 99.58%
ASC-103 2.83 9 1.00 4 3 64.71% 60.00% 5 143.63 0.37 99.61%
Click to expand...
 
C

cporter

You are right about learning to do it myself. The problem is learning
all the jargon so I know what to look up or learning the proper syntax
to perform an operation; which is why most people post questions here.

The left join appears to be what I need. Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

programming complex query/report 3

Top