Stumper...

[Jason Lepack]

I have two tables:

actual_job_data:
job_id - long - fk to job table
job_data_dt - date/time - time that event occured
qty - long - running count from a PLC

theoretical_job_data: (marks data points for break/lunch/job start/job
end)
job_id - see above
job_theor_dt - date/time - time of event
qty - long - what the current cound "should" be

Now, I have a Crystal Report that takes a query with the following
fields:
dt - date/time - time of event
a_qty - actual quantity at event time
t_qty - theoretical quantity at event time

Now it doesn't matter if a_qty or t_qty are null at an event time. I
should be able to use this:

SELECT
job_data_dt AS dt,
qty AS a_qty,
Null AS t_qty,
job_id
FROM
actual_job_data
WHERE
job_id=[jobid]

UNION

SELECT
job_theor_dt AS dt,
Null AS a_qty,
qty AS t_qty
FROM
theoretical_job_data
WHERE
job_id=[jobid];

But for some reason the t_qty get's gaarbled where there should be
values...
dt ,a_qty ,t_qty
5/10/2007 3:15:11 PM , ,
5/10/2007 4:00:00 PM , ,??ç¿¿ä‚‹
5/10/2007 4:30:00 PM , ,??ç¿¿ä‚‹
5/10/2007 5:45:11 PM , ,

It should be:
dt a_qty t_qty
5/10/2007 3:15:11 PM , ,0
5/10/2007 4:00:00 PM , ,880
5/10/2007 4:30:00 PM , ,880
5/10/2007 5:45:11 PM , ,2500

I suspect this has something to do with how the Union works, but I
don't know.

Anyone have any ideas? As the subject says, I'm stumped.

Cheers,
Jason Lepack

 

[Ken Snell \(MVP\)]

May be because you're using a UNION connector, which causes ACCESS to group
the two subqueries' results and that can cause interesting problems with
memo fields as well as others.

Try using UNION ALL as the connector. That does not group the results.
--

Ken Snell
<MS ACCESS MVP>



I have two tables:

actual_job_data:
job_id - long - fk to job table
job_data_dt - date/time - time that event occured
qty - long - running count from a PLC

theoretical_job_data: (marks data points for break/lunch/job start/job
end)
job_id - see above
job_theor_dt - date/time - time of event
qty - long - what the current cound "should" be

Now, I have a Crystal Report that takes a query with the following
fields:
dt - date/time - time of event
a_qty - actual quantity at event time
t_qty - theoretical quantity at event time

Now it doesn't matter if a_qty or t_qty are null at an event time. I
should be able to use this:

SELECT
job_data_dt AS dt,
qty AS a_qty,
Null AS t_qty,
job_id
FROM
actual_job_data
WHERE
job_id=[jobid]

UNION

SELECT
job_theor_dt AS dt,
Null AS a_qty,
qty AS t_qty
FROM
theoretical_job_data
WHERE
job_id=[jobid];

But for some reason the t_qty get's gaarbled where there should be
values...
dt ,a_qty ,t_qty
5/10/2007 3:15:11 PM , ,
5/10/2007 4:00:00 PM , ,????
5/10/2007 4:30:00 PM , ,????
5/10/2007 5:45:11 PM , ,

It should be:
dt a_qty t_qty
5/10/2007 3:15:11 PM , ,0
5/10/2007 4:00:00 PM , ,880
5/10/2007 4:30:00 PM , ,880
5/10/2007 5:45:11 PM , ,2500

I suspect this has something to do with how the Union works, but I
don't know.

Anyone have any ideas? As the subject says, I'm stumped.

Cheers,
Jason Lepack

 

[John Spencer]

A guess!!! Perhaps the null is the first query is causing the field to be
interpreted as text. Try the following which may give the SQL engine enough
information to correctly type t_qty.

SELECT
job_data_dt AS dt,
qty AS a_qty,
IIF(1=2,CLng(0),Null) AS t_qty,
job_id
FROM
actual_job_data
WHERE
job_id=[jobid]

UNION

SELECT
job_theor_dt AS dt,
Null AS a_qty,
qty AS t_qty
FROM
theoretical_job_data
WHERE
job_id=[jobid];

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have two tables:

actual_job_data:
job_id - long - fk to job table
job_data_dt - date/time - time that event occured
qty - long - running count from a PLC

theoretical_job_data: (marks data points for break/lunch/job start/job
end)
job_id - see above
job_theor_dt - date/time - time of event
qty - long - what the current cound "should" be

Now, I have a Crystal Report that takes a query with the following
fields:
dt - date/time - time of event
a_qty - actual quantity at event time
t_qty - theoretical quantity at event time

Now it doesn't matter if a_qty or t_qty are null at an event time. I
should be able to use this:

SELECT
job_data_dt AS dt,
qty AS a_qty,
Null AS t_qty,
job_id
FROM
actual_job_data
WHERE
job_id=[jobid]

UNION

SELECT
job_theor_dt AS dt,
Null AS a_qty,
qty AS t_qty
FROM
theoretical_job_data
WHERE
job_id=[jobid];

But for some reason the t_qty get's gaarbled where there should be
values...
dt ,a_qty ,t_qty
5/10/2007 3:15:11 PM , ,
5/10/2007 4:00:00 PM , ,????
5/10/2007 4:30:00 PM , ,????
5/10/2007 5:45:11 PM , ,

It should be:
dt a_qty t_qty
5/10/2007 3:15:11 PM , ,0
5/10/2007 4:00:00 PM , ,880
5/10/2007 4:30:00 PM , ,880
5/10/2007 5:45:11 PM , ,2500

I suspect this has something to do with how the Union works, but I
don't know.

Anyone have any ideas? As the subject says, I'm stumped.

Cheers,
Jason Lepack

 

[Jason Lepack]

That was it Ken. Thanks so much!

 

[Jason Lepack]

A good guess! I was thinking that when I woke up this morning. Turns
out it was the Union All that fixed it though.

Thanks for your help.