Combining two columns

[maudley]

Could somebody please help me with a problem I'm having. I think it will be easier if I draw what I would like to do with my database:


I have two tables:

[WorkID]-[Period]-[TrackcarDate]--[SD]--[Lat]
-- 1409 --- Bef ---- 07-Jan-05 ---- 2.1 -- 0.8
-- 1409 --- Aft ---- 27-Apr-06 ---- 1.2 -- 0.4
-- 1410 --- Bef ---- 27-Apr-06 ---- 3.2 -- 0.9
-- 1410 --- Aft ---- 26-Oct-06 ---- 1.3 -- 0.6
-- 1411 --- Bef ---- 26-Oct-06 ---- 2.4 -- 0.7
-- 1411 --- Aft ---- 28-Sep-07 ---- 1.1 -- 0.4

and

[WorkID]--[ID]--[Work Type]--[Work Date]
--1409 --- 23 ----- Tamp ---- 19-Apr-06
--1410 --- 23 ----- Stone ---- 04-Oct-06
--1411 --- 23 ----- Stone ---- 05-Sep-07

I would like to create the following table using a query?:

[ID] - [Maintenance] -- [Date]
23 --- Track car ---- 07-Jan-05
23 --- Tamp ------- 19-Apr-06
23 --- Track car ---- 27-Apr-06
23 --- Track car ---- 27-Apr-06
23 --- Stone ------- 04-Oct-06
23 --- Track car ---- 26-Oct-06
23 --- Track car ---- 26-Oct-06
23 --- Stone ------- 05-Sep-07
23 --- Track car ---- 28-Sep-07

Does anybody know if this is even possible?

Thanks in advance

 

[namliam]

Does anybody know if this is even possible?

Yes possible, its a simple "JOIN" of your workid column

 

[maudley]

Yes possible, its a simple "JOIN" of your workid column

I've tried what I think you are suggesting but it doesn't give the desired solution. Here is what I've tried:


SELECT [501 All Before After Action].WorkID, [501 All Before After Action].[Work Type] AS Maintenance, [501 All Before After Action].[Work Date] AS [Date]

FROM [501 All Before After Action] INNER JOIN [501 All TQ Before After Action] ON [501 All Before After Action].WorkID = [501 All TQ Before After Action].WorkID

UNION SELECT [501 All TQ Before After Action].WorkID, [501 All TQ Before After Action].Period, [501 All TQ Before After Action].[TQ Date]
FROM [501 All TQ Before After Action];


This gives the following solution:

[Work ID][Maintenance]--[Date]
1409 ------ before ----
1409 ------ after -----
1409 ------ Tamping --

I would like:
[ID]-----[Maintenance]----[Date]
20650 ------ Track car ----
20650------ Track car -----
20650------ Tamping --

Thanks for your help

 

[maudley]

I've solved the "before" "after" problem using:

IIf([501 All TQ Before After Action.Period] = "before" ,"Track car","Track car")

Still not sure what to do about the "ID" instead of Work ID