Anne- Do you always have no more than four approvals? Even so, consider changing your table design to something like this:
tblDocuments: DocNo, Title tblApprovers: ApproverID, ApproverName tblDocApprovers: DocNo, ApproverSeq, ApproverID But to solve your immediate problem, you need multiple copies of the "name" table in your query: SELECT tblDocuments.DocNo, tblDocuments.Title, Approver1.ApproverName As Approval1, Approver2.ApproverName As Approval2, Approver3.ApproverName As Approver3, Approver4.ApproverName As Approver4 FROM (((tblDocuments LEFT JOIN tblApprovers As Approver1 ON tblDocuments.Approval1 = Approver1.ApproverID) LEFT JOIN tblApprovers As Approver2 ON tblDocuments.Approval2 = Approver2.ApproverID) LEFT JOIN tblApprovers As Approver3 ON tblDocuments.Approval3 = Approver3.ApproverID) LEFT JOIN tblApprovers As Approver4 ON tblDocuments.Approval4 = Approver4.ApproverID John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com/access/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Anne W. Sent: Friday, August 24, 2007 12:22 AM To: [email protected] Subject: [ms_access] Make table ID number show related text multiple times in report I don't know how to search for this. Sorry. I have created a table that has a list of names and each has an id number. I have a form with 4 drop down boxes that select a name (there can be 1 to 4 on each form). These are people who need to approve a change to a document. When the name is selected, the id number goes into a table under Approval 1, Approval 2, etc. (as needed). Now, I want to create a report from those forms. I cannot figure out how to get the ID numbers to show the names on the report and it's driving me crazy. I have tried several queries and can get one to show what I want but I cannot figure out how to get (in this case) the other six records. I want to have something like this: Doc. No. Title Approval 1 Approval 2 Approval 3 089P0002 Document Control John Smith Julie Brown 022MU012 Design Change John Smith Barbara Smith Joe Baker 012P0003 Parts Julie Brown I keep wondering if there is a way to write an expression in the report that would make the number equal the matching title from the table that contains that information? Thank you anyone! Anne W. Yahoo! Groups Links
