Query with multiple common points

J

Jon

I've been asked to come up with a report and I'm having difficulty with
my limited Access skills in trying to construct the Access query
that'll get me what they want. I'm hoping someone can help me out
here...

I've gotten it the background work down so there are two subqueries
which I now need to join together for the final output; let's call them
qryEveryTime and qryDownTime. qryEveryTime has the following fields:

Date, ShiftNum, Actual, Est

qryDownTime has the following fields:

Date, ShiftNum, Down

The final value which I need to come up with is this: Est/(Actual-Down)

Here's the gotcha which is giving me a headache: while Date and
ShiftNum are the same values in both queries, qryDownTime will not
always have a value for each occurance in qryEveryTime. Here's a small
snippet of the output for each subquery:

qryEveryTime
Date ShiftNum Actual Est
02-Oct-06 1 473 378
02-Oct-06 2 440 370
03-Oct-06 1 392 421
03-Oct-06 2 187 168
04-Oct-06 1 474 230
04-Oct-06 2 262 220
05-Oct-06 1 470 357
05-Oct-06 2 430 289
06-Oct-06 1 469 219
06-Oct-06 2 444 382

qryDownTime
Date ShiftNum Down
02-Oct-06 1 93
02-Oct-06 2 4
03-Oct-06 1 10
04-Oct-06 1 31
05-Oct-06 1 146
06-Oct-06 1 120
06-Oct-06 2 65

As you can see, there can be a value in qryEveryTime that is not
duplicated in qryDownTime. For instance the proper calculation for
shift 1 on Oct 2 should be 378/(473-93) or .9947, while the result for
shift 2 on 10/5 should be 289/430 or .6721. Yet the query I have right
now:

SELECT qryDownTime.Date, qryDownTime.ShiftNum, qryDownTime.Process,
qryEveryTime!Est/(qryEveryTime!Actual-qryDownTime!Down) AS TotalEff
FROM qryDownTime, qryEveryTime
WHERE (((qryEveryTime.Date)=[qryDownTime]![Date]) AND
((qryEveryTime.ShiftNum)=[qryDownTime]![ShiftNum]) AND
((qryEveryTime.Process)=[qryDownTime]![Process]));

is not returning any value at all for shift 2 for 10/5.

What am I doing wrong, and what should I be doing instead here??
 
S

Smartin

Jon said:
I've been asked to come up with a report and I'm having difficulty with
my limited Access skills in trying to construct the Access query
that'll get me what they want. I'm hoping someone can help me out
here...

I've gotten it the background work down so there are two subqueries
which I now need to join together for the final output; let's call them
qryEveryTime and qryDownTime. qryEveryTime has the following fields:

Date, ShiftNum, Actual, Est

qryDownTime has the following fields:

Date, ShiftNum, Down

The final value which I need to come up with is this: Est/(Actual-Down)

Here's the gotcha which is giving me a headache: while Date and
ShiftNum are the same values in both queries, qryDownTime will not
always have a value for each occurance in qryEveryTime. Here's a small
snippet of the output for each subquery:

qryEveryTime
Date ShiftNum Actual Est
02-Oct-06 1 473 378
02-Oct-06 2 440 370
03-Oct-06 1 392 421
03-Oct-06 2 187 168
04-Oct-06 1 474 230
04-Oct-06 2 262 220
05-Oct-06 1 470 357
05-Oct-06 2 430 289
06-Oct-06 1 469 219
06-Oct-06 2 444 382

qryDownTime
Date ShiftNum Down
02-Oct-06 1 93
02-Oct-06 2 4
03-Oct-06 1 10
04-Oct-06 1 31
05-Oct-06 1 146
06-Oct-06 1 120
06-Oct-06 2 65

As you can see, there can be a value in qryEveryTime that is not
duplicated in qryDownTime. For instance the proper calculation for
shift 1 on Oct 2 should be 378/(473-93) or .9947, while the result for
shift 2 on 10/5 should be 289/430 or .6721. Yet the query I have right
now:

SELECT qryDownTime.Date, qryDownTime.ShiftNum, qryDownTime.Process,
qryEveryTime!Est/(qryEveryTime!Actual-qryDownTime!Down) AS TotalEff
FROM qryDownTime, qryEveryTime
WHERE (((qryEveryTime.Date)=[qryDownTime]![Date]) AND
((qryEveryTime.ShiftNum)=[qryDownTime]![ShiftNum]) AND
((qryEveryTime.Process)=[qryDownTime]![Process]));

is not returning any value at all for shift 2 for 10/5.

What am I doing wrong, and what should I be doing instead here??
Click to expand...

There is no ShiftNum = 2 on 10/5 in the qryDownTime table, so no records
are returned for this combination, and therefore, no calculation is
performed.

Suggest you rewrite this using an outer join that converts a null to
zero. Something like:

SELECT qryDownTime.Date, qryDownTime.ShiftNum, qryDownTime.Process,
qryEveryTime!Est/(qryEveryTime!Actual-NZ(qryDownTime!Down,0)) AS TotalEff
FROM qryDownTime RIGHT JOIN qryEveryTime
ON qryEveryTime.Date=[qryDownTime]![Date] AND
qryEveryTime.ShiftNum=[qryDownTime]![ShiftNum] AND
qryEveryTime.Process=[qryDownTime]![Process];

Should also be said, "Date" is a reserved word and should be changed in
your tables if at all possible. At a minimum, wrap all references to the
field in [square brackets] to avoid confusing the engine.
 
J

Jon

Smartin said:
Jon said:
I've been asked to come up with a report and I'm having difficulty with
my limited Access skills in trying to construct the Access query
that'll get me what they want. I'm hoping someone can help me out
here...

I've gotten it the background work down so there are two subqueries
which I now need to join together for the final output; let's call them
qryEveryTime and qryDownTime. qryEveryTime has the following fields:

Date, ShiftNum, Actual, Est

qryDownTime has the following fields:

Date, ShiftNum, Down

The final value which I need to come up with is this: Est/(Actual-Down)

Here's the gotcha which is giving me a headache: while Date and
ShiftNum are the same values in both queries, qryDownTime will not
always have a value for each occurance in qryEveryTime. Here's a small
snippet of the output for each subquery:

qryEveryTime
Date ShiftNum Actual Est
02-Oct-06 1 473 378
02-Oct-06 2 440 370
03-Oct-06 1 392 421
03-Oct-06 2 187 168
04-Oct-06 1 474 230
04-Oct-06 2 262 220
05-Oct-06 1 470 357
05-Oct-06 2 430 289
06-Oct-06 1 469 219
06-Oct-06 2 444 382

qryDownTime
Date ShiftNum Down
02-Oct-06 1 93
02-Oct-06 2 4
03-Oct-06 1 10
04-Oct-06 1 31
05-Oct-06 1 146
06-Oct-06 1 120
06-Oct-06 2 65

As you can see, there can be a value in qryEveryTime that is not
duplicated in qryDownTime. For instance the proper calculation for
shift 1 on Oct 2 should be 378/(473-93) or .9947, while the result for
shift 2 on 10/5 should be 289/430 or .6721. Yet the query I have right
now:

SELECT qryDownTime.Date, qryDownTime.ShiftNum, qryDownTime.Process,
qryEveryTime!Est/(qryEveryTime!Actual-qryDownTime!Down) AS TotalEff
FROM qryDownTime, qryEveryTime
WHERE (((qryEveryTime.Date)=[qryDownTime]![Date]) AND
((qryEveryTime.ShiftNum)=[qryDownTime]![ShiftNum]) AND
((qryEveryTime.Process)=[qryDownTime]![Process]));

is not returning any value at all for shift 2 for 10/5.

What am I doing wrong, and what should I be doing instead here??
Click to expand...

There is no ShiftNum = 2 on 10/5 in the qryDownTime table, so no records
are returned for this combination, and therefore, no calculation is
performed.

Suggest you rewrite this using an outer join that converts a null to
zero. Something like:

SELECT qryDownTime.Date, qryDownTime.ShiftNum, qryDownTime.Process,
qryEveryTime!Est/(qryEveryTime!Actual-NZ(qryDownTime!Down,0)) AS TotalEff
FROM qryDownTime RIGHT JOIN qryEveryTime
ON qryEveryTime.Date=[qryDownTime]![Date] AND
qryEveryTime.ShiftNum=[qryDownTime]![ShiftNum] AND
qryEveryTime.Process=[qryDownTime]![Process];

Should also be said, "Date" is a reserved word and should be changed in
your tables if at all possible. At a minimum, wrap all references to the
field in [square brackets] to avoid confusing the engine.
Click to expand...

That query looks like it works... all I had to do was change the first
items in the SELECT to say "SELECT qryEveryTime.Date,
qryEveryTime.ShiftNum, qryEveryTime.Process", otherwise the fields come
back as empty for 10/5 shift 2 (as you pointed). :)
 

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

RANK 1
Running sum 3
count dates in multiple columns 3
Running Sum in Query 2
Combine 2 Charts 2007 Excel 1
Taking the sum between two dates 2
Sum Function that does not include 0 15
Advanced problem using auto-number and datediff to find readmissio 1

Top