How-to extract data in this format?

[QB]

I have a table, 'tbl_data' which has the following fields:
UnitNo, Mot1Date, Mot1Hrs, Mot1No,Mot2Date, Mot2Hrs,Mot2No

Example Data
123 1/22/2010 2223 1 1/22/2010 3411 1
332 1/20/2010 156 1 1/18/2010 5467 2

I need to build a query that will list the Units per date also indicating
which Mot(1or2orboth) by Mot(x)No.

MotNo UnitNo Date Mot(x)
1 123 1/22/2010 Mot1 & Mot2
1 332 1/20/2010 Mot1
2 332 1/18/2010 Mot2

Could someone show me an example of the SQL statement which I could use to
build such an output of data.

Thank you for the help,

QB

 

[KARL DEWEY]

also indicating which Mot(1or2orboth) by Mot(x)No.
This part I do not understand.

Use a union query --
SELECT 1 AS MotNo, UnitNo, Mot1Date AS MotDate, Mot1Hrs AS MotHrs, Mot1No AS
Something
FROM tbl_data
UNION ALL SELECT 2 AS MotNo, UnitNo, Mot2Date AS MotDate, Mot2Hrs AS MotHrs,
Mot2No AS Something
FROM tbl_data;

 

[QB]

Karl,

I came up with something very similar, but this will list in double certain
days... So I added a group by. But this does not create the all important
Mot(x) field where it indicates Mot1, Mot2 or Mot1 & Mot2?

QB

 

[QB]

Ok, I am close to the final solution, but still need a hand to fine tune it.
Right now I can get a query to return the following

MotNo UnitNo Date Mot(x)
1 123 1/22/2010 Mot1
1 123 1/22/2010 Mot2
1 332 1/20/2010 Mot1
2 332 1/18/2010 Mot2

Now how can I use a Group By and concatenate the Mot(x) values into 1 entry
(Per below)?

MotNo UnitNo Date Mot(x)
1 123 1/22/2010 Mot1 & Mot2
1 332 1/20/2010 Mot1
2 332 1/18/2010 Mot2

QB

 

[KARL DEWEY]

Post your SQL.