Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Query and Field Values (2K +)

    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.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Query and Field Values (2K +)

    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;"
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Query and Field Values (2K +)

    Genius sir! Much obliged.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •