Query Using VBA

[Adam McReynolds]

I have 3 fields on a table for 3 types of repair techs in a unit repair cycle. I am trying to see query data for a single tech where they can be the tech in any or all of the three fields. The three fields are Rework,Repair, and QC.

I have used an "OR" statement to find records that will have the single tech in either of the 3 tech fields but this data needs to be reduced down to be easier to read.

My questions is how can I group by these three fields and show when the tech say, "Reworked" a unit, with a subtotal count for each group? So when a report is ran on that tech I can see the records they "Reworked" with a subtotal count for that group, the records they "Repaired" with a subtotal count for that group, and so on.

Here is my code so far using a combo box for the techID:

Dim strWhere

strWhere = "ReworkTech = '" & Me.cmb_tech_daily & "' Or RepairTech = '" & Me.cmb_tech_daily & "' OR QC_Tech = '" & Me.cmb_tech_daily & "'"
DoCmd.OpenReport "RPT_RF_TECH_DAILY_ALL", acViewReport, , strWhere

Thanks for any help.

 

[pr2-eugin]

I hate to say this, but there is your answer for what is the big deal in normalizing the table. In an Ideal DB world this information will go into a specialSkillsTable, where each employee will be added as a row, so if Paul is skilled only in Rework then he will have only one record in this table, where as if Eugin is skilled in all three works then three records for Eugin so on and so forth.

This way the result you are looking for will be a simple GROUP BY query, but now, you need to write excessive code to "try and get" the most realistic value. If you could change the DB structure I suggest you do it now !

 

[Adam McReynolds]

I hate to say this, but there is your answer for what is the big deal in normalizing the table. In an Ideal DB world this information will go into a specialSkillsTable, where each employee will be added as a row, so if Paul is skilled only in Rework then he will have only one record in this table, where as if Eugin is skilled in all three works then three records for Eugin so on and so forth.

This way the result you are looking for will be a simple GROUP BY query, but now, you need to write excessive code to "try and get" the most realistic value. If you could change the DB structure I suggest you do it now !

Thanks for the reply. I understand what you are saying but not sure how I would normalize it. I have a table that holds each repair record and has a tech for each section of the repair cycle. Isn't this appropriate since it is work on that record?

I am rethinking my attempt and now I am attempting to just get number count of records each tech did for the day based on their techID being in one of these fields and if it is then having a "time out" which designates they completed the unit under that section of the repair cycle.

Here is that code below. My syntax is off though:

Dim strWhere
Dim reworkWhere
Dim repairWhere
Dim qcWhere

reworkWhere = "ReworkTech = '" & Me.cmb_tech_daily & "' And Not IsNull(ReworkTimeOut)"
repairWhere = "RepairTech = '" & Me.cmb_tech_daily & "' And Not IsNull(RepairTimeOut)"
qcWhere = "QC_Tech = '" & Me.cmb_tech_daily & "' And Not IsNull(QC_TimeOut)"

strWhere = "' & reworkWhere & " Or " & repairWhere & " Or " & qcWhere & '"
DoCmd.OpenReport "RPT_MODULE_REPAIRS", acViewReport, , strWhere