query list

[Andy Tag]

Hi I am Andy and having trouble creating a query. I have three mechanic fields in my database. Up to three mechanics can be assigned to a record. Along with some dates and other equipment fields. I want the query to return a list of mechanics by day. If it were one mechanic per record it would list, but with up to three mechanics in each record it doesn't list. Kind of like the transpose option in excel.
Appreciate anyone's thoughts.
I.e
Day | Mechanic name | Eqp. Name
Monday mechanic1 equipA
Tuesday mechanic1 equipB
Monday mechanic2 equipA
Tuesday mechanic2 equipC

 

[Ranman256]

use the query wizard and make a CROSSTAB query. (just like excel traspose)
it will ask you to set the row, the column and what is in the field.

 

[jdraw]

Please describe in plain English the business you are hoping to automate with this database .
Just as you would tell an 8 yr old.

 

[Andy Tag]

Purpose, generate a weekly maintenance schedule. The schedule need to be sorted by mechanic, by day of week.

 

[RW00]

Hi Andy,
you don't describe how your data is structured, but I assume that you have three mechanic fields. If this is the case, then surely you could use a union operator along the lines of

SELECT [Day], [Mechanic1]
FROM [Table_Name]
WHERE [Mechanic1] IS NOT NULL
UNION
(SELECT [Day], [Mechanic2]
FROM [Table_Name]
WHERE [Mechanic2] IS NOT NULL
UNION
SELECT [Day], [Mechanic3]
FROM [Table_Name]
WHERE [Mechanic3] IS NOT NULL)

 

[Andy Tag]

The database fields are Equipment , ScheduleDate, Mechanic1, Mechanic2, Mechanic3, misc...

Problem creating a chronological query of mechanics by day of week.

 

[plog]

You need to structure your data properly. When you start numerating field names (Mechanic1, Mechanic2, Mechanic3, etc.) you are doing it wrong and need a new table. All of the mechnic fields need to come out of your existing table and into a new table (Mechanics). Here's how your tables should look:

ExistingTable
ExistingTableID, Equipment, ScheduleDate, misc

MechanicsTable
ExistingTableId, Mechanic, MechanicNumber


Then you can JOIN those 2 tables via the ExistingTableID and have all your data available for however you need it. I would focus on fixing your structure because you will just have more headaches down the line if you keep it in this improper one.

 

[Andy Tag]

Like what I see from RW00's union query suggestion. Pretty sure this will work. Thanks Andy Tag

SELECT mechanic.Mechanic1, Weekday([date],2) AS weeday, mechanic.equipment
FROM mechanic
WHERE (((mechanic.Mechanic1) Is Not Null));

UNION ALL SELECT mechanic.Mechanic2, Weekday([date],2) AS weeday, mechanic.equipment
FROM mechanic
WHERE (((mechanic.Mechanic2) Is Not Null));

UNION ALL SELECT mechanic.Mechanic3, Weekday([date],2) AS weeday, mechanic.equipment
FROM mechanic
WHERE (((mechanic.Mechanic3) Is Not Null));