One record into Two Records

[mswilson16]

I have a database that was designed by an x-employee. I am currently creating reports for it. I can not alter the design of the tables. I have a table called "tblDelivery", which has fields:

EventID
Delivery Date
Delivery Time
Pickup Date
Pickup Time

What I need to do is create a daily schedule for pickups/Deliveries. It is easy to create the schedule for either the pickup or delivery but I want a complete Delivery/Pickup list that would look like:

EventID Time Type
1 9.00 am Delivery
2 10.00 am Delivery
3 10.30 am Delivery
1 10.45 am Pickup
4 11.00 am Delivery
2 11.30 am Pickup
4 13.45 pm Pickup
3 15.00 pm Pickup

So as you can see I would need to create two records for everyone one record (a pickup record and delivery record). I have done this by creating 2 queries. But now I need to make the 2 queries into one long list of pickups and deliveries.

If any one can help me out then that would be create. I have read all other posts and it seems that a union Query is what I am looking for. But I get a syntax error when ever I run it. here is the SQL:

**********************
SELECT Tbl_Delivery.EventID, Tbl_Delivery.pickdate, eventsxt.pickfrtime, Tbl_Delivery.picktotime
FROM Tbl_Delivery
WHERE (((Tbl_Delivery.EventID)=3448))

UNION SELECT Tbl_Delivery.EventID, Tbl_Delivery.del_date,Tbl_Delivery.equipleave, Tbl_Delivery.equipdeliv
FROM Tbl_Delivery
WHERE (((Tbl_Delivery.EventID)=3448));
******************

If anyone has any idea of how to help that would be great. Thanks in advance.

Wilson

 

[smedvid]

Ok... ur almost there....

When joining data via a UNION clause, the field names must be the same. Let me know if that worked...

SELECT
Tbl_Delivery.EventID,
Tbl_Delivery.pickdate AS DATE1,
eventsxt.pickfrtime AS TIME1,
Tbl_Delivery.picktotime AS TIME2
FROM Tbl_Delivery
WHERE (((Tbl_Delivery.EventID)=3448))
UNION
SELECT Tbl_Delivery.EventID,
Tbl_Delivery.del_date AS DATE1,
Tbl_Delivery.equipleave AS TIME1,
Tbl_Delivery.equipdeliv AS TIME2
FROM Tbl_Delivery
WHERE (((Tbl_Delivery.EventID)=3448));


Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[mswilson16]

It didn't work! :-( I am reporting through Access onto a FoxPro Database. I keep getting a error message of:

"ODBC--Call Failed"
"[Microsoft][ODBC Visual FoxPro Driver]Syntax Error.(#200)"

 

[smedvid]

Ok,

I would start small an work ur way up to see the problem...

SELECT
Tbl_Delivery.EventID,
Tbl_Delivery.pickdate AS DATE1,
FROM Tbl_Delivery
UNION
SELECT Tbl_Delivery.EventID,
Tbl_Delivery.del_date AS DATE1,
FROM Tbl_Delivery;

Then Add fields... May not need AS clause?


Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[mswilson16]

Still didn't work, I am assuming that it is because of the ODBC link. Is there another way in which I could get around this????

The ODBC link is not always reliable, do you know a way that either I could improve the link, or a different way to get all the required records?

 

[smedvid]

Well... is the AS syntax correct? Another less attractive option is to import the data into an Access table...

Perhaps try to run the UNION clause with just the EventID to see if the Union is working?

????


Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[mswilson16]

The syntax is correct... I have copied the table into access and it runs fine. I didn't want to do it this way but at least it works now.

Thanks for the help!!!

Wilson

 

[smedvid]

Great... Perhaps check your ODBC driver for FoxPro and make sure it is correct/latest... The SQL Union should work fine when connecting to remote databases...

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...