Query and Report

[blacktide]

I have a query that has 4 Tech fields and 4 Hour fields and a few other fields that are not pertinent to my question.

When the Tech opens the query it will ask for the Tech's initials at each Tech field. The Tech entering his/her initials will appear in only one of the fields at a time and another Tech's initials can appear in the other three Tech fields.

If I enter MR for the Tech I will end up with some thing like this:

Tech_1 = MR Hours_1 = 3 Tech_2 = SB Hours_2 =1 Tech_3 = K Hours_3 = 5
Tech_1 = K Hours_1 = 4 Tech_2 = MR Hours_2 =6 Tech_3 = SA Hours_3 = 9

What I need the query to do is only show the hours for the Initials that were entered. Maybe there is a way to do this when I run the report?


Thanks,

Michael

 

[The_Doc_Man]

If the query is reflecting the underlying structure of its parent table, your problem is normalization. Your table is badly un-normalized.

tblTicket
fldTicketID, autonumber, prime key
stuff related to the action, event, trouble ticket, request, or whatever this is.

tblWorkDone
fldTicketID, long, foreign key
fldTechWhen, date - time at which work started
fldTechInit, text - initials of technician
fldTechHrs, SINGLE or DOUBLE (your choice) - time spent

The combo of TicketID and TechWhen and TechInit should be unique, so is a candidate for being a (compound) prime key.

Make the Ticket and WorkDone tables have a relation on TicketID, where you have one (Ticket) to many (WorkDone). Referential Integrity is your call, but is usually recommended.

Now a JOIN query on TicketID will list every tech who worked on a given item. If nobody worked on it, the item won't be in the query. Sort this by TicketID and by TechWhen to get the list of who worked on the project and the order in which they worked.

From here, you can build reports with TicketID as a group header and WorkDone as detail line-items.

 

[blacktide]

So.....

I am taking this with a sense of humor and hope that you do to. So Basically what you are telling me is 1) my database is craparific (which doesn't surprise me) and 2) I would be better off starting over?

 

[blacktide]

Not understanding

Ok so maybe I don't really understand what you are wanting.

I have made a test db and I am trying to follow the instructions you outlined, however I am not 100% that it will work.

Please keep in mind I have to allow for the posibility that more than one Tech worked on 1 ticket.