Dynamic Query and Field Values (2K +)

Topic

New Reply

Greetings All!
I’m working on a solution to generate a report for our accounting dept. It involves creating several queries dynamically, reason being that a particular field will come from one of several possible tables based on the value of a field in the same query. Essentially – the multiple tables are all named as such: tblPhCodes5012, tblPhCodes5020, tblPhCodes5033….etc. Although my code does not work, I think it illustrates the idea I am trying to accomplish:

Set dbs = CurrentDb

strSQL = “SELECT tblUtilEquip.EquipNum, tblUtilEquip.JobNum, tblUtilEquip.Date, tblUtilEquip.Code1, tblUtilEquip.Code1Hours, tblPhCodes” & tblUtilEquip.JobNum & “.CostCodes, tblEquipList.DailyRate ” & _
“FROM tblPhCodes” & tblUtilEquip.JobNum & ” INNER JOIN (tblEquipList INNER JOIN tblUtilEquip ON tblEquipList.EquipNum = tblUtilEquip.EquipNum) ON tblPhCodes” & tblUtilEquip.JobNum & “.PhaseCode = tblUtilEquip.Code1 ” & _
“ORDER BY tblUtilEquip.EquipNum, tblUtilEquip.JobNum, tblUtilEquip.Date;”

Set qdf = dbs.CreateQueryDef(“qryCode”, strSQL)

I’m trying to use the value of [tblUtilEquip.JobNum] to determine which [tblPhCode….] to use, which may change from record to record.

Is this possible? Any advice is sincerely appreciated.

Thank you in advance.

Reply | Quote

Replies

I would create a Union query with the Job Number in each and then join this query to your other query.

Select field1, field2,…..5012 as JobNum
From tblPhCodes5012
union
Select field1, field2,…..5020 as JobNum
From tblPhCodes5020
union….

Then
strSQL = “SELECT tblUtilEquip.EquipNum, tblUtilEquip.JobNum, tblUtilEquip.Date, tblUtilEquip.Code1, tblUtilEquip.Code1Hours, UnionQUERY.CostCodes, tblEquipList.DailyRate ” & _
“FROM UnionQUERY.JobNum & ” INNER JOIN (tblEquipList INNER JOIN tblUtilEquip ON tblEquipList.EquipNum = tblUtilEquip.EquipNum) ON UnionQUERY.PhaseCode = tblUtilEquip.Code1 ” & _
“ORDER BY tblUtilEquip.EquipNum, tblUtilEquip.JobNum, tblUtilEquip.Date;”

Reply | Quote

Genius sir! Much obliged.

Reply | Quote