One-to-Many records all in one output file as one record 1

[DLynnTX]

I have a database that keeps up with certifications for various employees. I need to be able to choose a particular employee (no problem with that) and then I need that data to be exported into a PDF form. The form has the employee id, name, status, instructor and method at the top and then lists the certification, description and expiration date in the body of the form. The problem I am having is that if I export the query to an .XML file, I get breaks in the record instead of getting all of the certifications in one file. I figure I need to do something like a loop of the query and change the field names to Cert1, Desc1, ExpDate1, Cert2, Desc2, ExpDate2, etc and continue looping until all the records are there. However, I have NO idea how to do this. The number of certifications will not be the same for each employee, and the form holds a total of 15. At this point, I don’t think I’ll have more than 15 expiring at a time, so I’m not worried about that yet.

Here’s my query to get the data:
SELECT tblEmployees.[Employee ID#], tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.Status, tblEmployees.Notes, tblEmployeeTasks.Certification, tblTasks.Description, tblEmployeeTasks.[Expiration Date], tblEmployeeTasks.Instruction, tblEmployeeTasks.Method
FROM tblTasks INNER JOIN (tblEvaluators INNER JOIN (tblEvalMethod INNER JOIN (tblEmployees INNER JOIN tblEmployeeTasks ON tblEmployees.[Employee ID#] = tblEmployeeTasks.[Employee ID#]) ON tblEvalMethod.EvalMethod = tblEmployeeTasks.Method) ON tblEvaluators.Evaluator = tblEmployeeTasks.Instruction) ON tblTasks.[Task#] = tblEmployeeTasks.Certification
WHERE (((tblEmployees.[Employee ID#])=[Enter Empl#]));

Here’s a sample of what the above query output is:
Query1
Employee ID# LastName FirstName Status Notes Certification Description Expiration Date Instruction Method
br-245 Jones Sam Active 10533 4/15/2011 John Doe Online
br-245 Jones Sam Active 15049 How to hang crown molding 4/16/2011 John Doe Online
br-245 Jones Sam Active 15940 How to lay a floor 5/1/2011 John Doe Online

Thank you for your help. I have spent the last 3 hours reading posts and trying to find something that would help me with this before posting.

 

[dhookom]

You should learn to put the tt tags around your records so they look aligned correctly. Also, you provided the sample records but not how you would like them displayed.

I think you could use a multi-value crosstab as described in faq701-4524.

Duane
Hook'D on Access
MS Access MVP

 

[DLynnTX]

I have been working on the crosstab query for hours and can't get it to work. I am not totaling anything - simply wanting to get all the data in one record. So from my sample above I would want it to become:

EmplID|Lastname|Firstname|Status|Notes|Cert1|Desc1|ExpDate1|Cert2|Desc2|ExpDate2|Cert3|Desc3|ExpDate3|Instructor|Method

Obviously I have abbreviated the field names, but it would end up with one record and however many fields necessary for how many certifications the employee has.

I am going to go back to trying the crosstab query - any help would be greatly appreciated.

 

[dhookom]

I can't make heads or tails of your data in your first post. Nothing lines up and field values seem to be missing.

The first step is to create a ranking query that numbers the records consecutively within each employees records. Search on ranking query to find out how to number the records. The rank number will be used to number columns like Cert1.

Then you can use the solution I suggested for creating multiple columns in a crosstab.

Duane
Hook'D on Access
MS Access MVP

 

[DLynnTX]

Thank you dhookom. I'm sorry, I do not know how to post my data lined up. Here is a list of field names from the first post:

EmplID|Lastname|Firstname|Status|Notes|Certification|Description|ExpirationDate|Instructor|Method

The query pulls the data from 3 tables for 1 employee. The fields above are what are included. In my sample from the first post, the employee had 3 records. Then I need to take those records and include them all in one record, renaming the fields as necessary to include all the records (as I spelled out in my second post).

I will try your suggestion now. I'm sorry I have not been very good at explaining myself.

 

[DLynnTX]

Ok - I'm almost there. I used a Make Table query that added an autonumber field and that worked great to get the records numbered. Then I used the following crosstab query:

TRANSFORM First(IIf([FldName]="Cert",[Cert],IIf([FldName]="Desc",[Desc],IIf([FldName]="ExpDate",[ExpDate])))) AS DaVal
SELECT tblFormData.[Employee ID#]
FROM tblFormData, tblXtabColumns
GROUP BY tblFormData.[Employee ID#]
PIVOT [FldName] & [ID];

which does exactly as I needed it to. Now I need to add the columns back in that are not included in the crosstab query (i.e. Name, Status, Notes, Instructor, Method). Working on that now... shouldn't be a problem. Thank you so much for your help!