Cocantenate Date and Time from Progressive Records?

[bowieknife]

I exported data (table) from an Oracle source to excel and then imported this data into an Access DBase. Unfortunately, in the export process date/time fields were split so that the first line in the table gives the following info:

Record: 1
Carrier: SouthWay
Pickup Date: 1/1/06
Delivey Date: 1/4/06

Record: 2
Carrier: Null
Pickup Date: 8:00am
Delvery Date: 10:00am

How can I make a query concantenate the date and time fields so that I do not have to go back and alter 2700 records?

Please help.

JB

 

[lynchg]

If it is always as you describe, with the first record having a value for carrier and the two dates and the very next record ALWAYS being associated with the previous and ALWAYS having a null carrier value, it can be done.

If the is an autonumber field and the numbers in that field are sequential, you can use that field. If not, add one, and check the data after it is populated to make sure that the number sequence in the field is sequential and all of the data is as I described above.

Then write something like this:

UPDATE yourTable, yourTable AS yourTable_2 SET yourTable.PickupDate = yourTable.PickupDate & " " & yourTable_2.PickupDate,
yourTable.DeliveryDate & " " & yourTable_2.DeliveryDate
WHERE yourTable.Carrier Is Null AND yourTable.ID yourTable.ID + 1;

But try the Select form first to see if you get what you expect:

Select yourTable.PickupDate & " " & yourTable_2.PickupDate,
yourTable.DeliveryDate & " " & yourTable_2.DeliveryDate
FROM yourTable, yourTable AS yourTable_2
WHERE yourTable.Carrier Is Null AND yourTable.ID yourTable.ID + 1;

I hope this is claer and helpful.

 

[PHV]

Why not simply try to fix the export process ?

 

[bowieknife]

lynchg,
Thanks for the assist. Unfortunately I keep geeting Syntax Error messages on the query (due to the &). And yes, the records are in sequential order, with 1 having the date, carrier, etc, and 2 having only data for the date, but that is actually the time (the second half of the first date record). It progresses along nicely with groupings 3-4, 5-6, etc.

Here is the code with my inputs so far:

UPDATE TblCMS, TblCMS AS TblCMS_2 SET TblCMS.Actual_Load_Date = TblCMS.Actual_Load_Date & " " & TblCMS_2.Actual_Load_Date,
TblCMS.Depart_Plant_Date & " " & CMS_2.Depart_Plant_Date, TblCMS.Expected_Dlv_Date From & " " & CMS_2.Expected_Dlv_Date From,TblCMS.Expected_Dlv_Date_To & " " & CMS_2.Expected_Dlv_Date_To,TblCMS.Actual_Dlv_Date & " " & CMS_2.Actual_Dlv_Date,TblCMS.Actual_Depart_Dlv_Date & " " & CMS_2.Actual_Depart_Dlv_Date,
WHERE yourTable.Carrier Is Null AND TblCMS.Record,TblCMS.Record + 1;

Question: Where is the TblCMS_2 coming from?