generate report from twoquery???

[dulija]

I have a problem with report, don’t know how to generate one report from two similar query (queryARRIVALS and queryDEPARTURES).
That two queries show data from tables (tableARRIVALS and tableDEPARTURES) just for current date.

queryARRIVALS has next fields:

company.....aircrafID......from.....timearrival

Air France...AF1234.......London......12:00.
Air France ...AF5002......Moscow.....15:30
British.........BA2345.....Athina........15:45

queryDEPARTURES has next fields:

company.....aircrafID........to.....timedeparture

British.........BA2000.......Athina .......10:00
Air France.....AF1234.......London.......16:00
Air France .....AF3000 ....Moscow ......21:00

And report should look like this:

“REPORT NAME”
COMPANY.......AIRCRAFT ID......FROM/TO.....TIMEARR......TIMEDEP

British________________________________________________

........................BA2345............Athina.. .......15:45
........................BA2000 ...........Athina ..........................10:00

Air France ____________________________________________
........................AF1234 .......... London .........................16:00
........................AF3000............Moscow ........................21:00
........................AF1234............London ........12:00 .-
........................ AF5002...........Moscow .......15:30 -

 

[dulija]

I try to make report based on UNION query, everything seems ok accept , report doesn’t have two column for time field( first for time arrival( TIMEARR) and second for time departure (TIMEDEP)) just one. How can I fix that?

thanks

 

[KeithG]

Use a union query to return the results from both queries into one result set

SQL statement 1

Union

SQL statement 2

 

[pbaldy]

As answered elsewhere:

SELECT company, aircraftID, [from], timearrival AS TimeArrive, Null AS TimeDepart, nopax
FROM queryARRIVALS
UNION ALL
SELECT company, aircraftID, to, Null AS TimeArrive, timedeparure AS TimeDepart, nopax
FROM queryDEPARTURES

 

[dulija]

Now I have other problem, can't get value in report for second time field.
I hope someone would help. (pls see attached sample)


thanks in advance

 

[pbaldy]

Sorry, I dropped the ball on this one. I was playing with why it happens and forgot to post the result. Change the Null to "" in the UNION:

SELECT DATEARR, COMPANY, REGISTRATION, ARRIVALFROM, TIMEARR AS ARRIVALTIME, "" AS DEPARTURETIME, PAX, CREW, REMARK
FROM queryARRIVALS
UNION ALL
SELECT DATEDEP, COMPANY, REGISTRATION, DEPARTURETO, "" AS ARRIVALTIME, TIMEDEP AS DEPARTURETIME , PAX, CREW, REMARK
FROM queryDEPARTURES;

That fixes the report. If it loses the format, You might need to add a Format function to the times in the query.

 

[dulija]

And again

THANK YOU VERY MUCH