Query Multiple Fields

[kevinwilson]

Aloha and mahalo to all that respond: Here's my dilemma, I'm trying to query multiple fields in a single table to produce the results below.

The following resembles my table (fields are in bold):
jobID tech1 tech2 tech3 tech1remarks tech2remarks tech3remarks
1 joe mike sam installed hd pulled fiber OJT
2 mike sam joe replaced nic reinstalled os polished fiber
3 joe sam deinstall pc process drmo

These are the results I'm trying to achieve in a query:
Technician JobID Remarks
joe 1 installed hd
2 polished fiber
3 deinstall pc

mike 1 pulled fiber
2 replaced nic

sam 1 OJT
2 reinstalled os
3 processed drmo

Seems simple enough, ...though I apparently have an acute case of brain lock.

 

[PauloRico]

Your table design is the problem here. When designing a table, it is best to think of it as representing a single entity. With your table, to find out how many jobs Mike has worked on, you have to analyse three seperate fields in one table. Your design should be-

JobID, Tech, TechNotes

with three seperate records as in your example (will there always be three?)

 

[kevinwilson]

PauloRico, thanks for the quick response.

My table design however requires multiple technician fields as there can be as little as one technician or as many as five technicians working a single job. Additionally, for each technician responding to the job there are corresponding "remarks" or "TechNotes" for each. Hope this answers your question.

 

[scriverb]

Create a UNION query and paste the following SQL into the SQL Window. Update the red table name to the name of your table.

Select A.Tech1 as Technician, A.JobID, A.Tech1Remarks as Remarks
FROM tblTechTable as A
WHERE A.Tech1 is Not Null
UNION
Select B.Tech2 as Technician, B.JobID, B.Tech2Remarks as Remarks
FROM tblTechTable as B
WHERE B.Tech2 is Not Null
UNION
Select C.Tech3 as Technician, C.JobID, C.Tech3Remarks as Remarks
FROM tblTechTable as C
WHERE C.Tech3 is Not Null
ORDER BY Technician, JobID;

If you need assistance creating this type of query just post back and we can provide instructions.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[kevinwilson]

Scriverb,

Worked like a charm, much mahalo's!

 

[PauloRico]

Hi Kevin,

Your reply highlighted what I was driving at (but, as usual, I didn't explain myself good enough). I'll have another go.

You gave an example with three technicians, using three groups of fields in your table for each technician. But you stated that there could be one or five. How would you cope with five.

What I was trying to say, is that there could be any number of technicians working on a job. So what you have to do is seperate these into there own records (it's easy to add a record, not so easy to keep increasing the number of fields in your table when you reach a limit).

I would recommend reading the Access Developers Handbook from Sybex. There is a great chapter in it that describes database design to the third normal form, in a very easy to understand context. It helped me out no end.

Hope this hepls.

Paul