Unanswered: Union query to merge fields into one record
I have a query which shows the info i require. It shows Examiners & the
programme types they are associated to.
Each examiner must have 1 programme type but can have a max 2 programme types.
The query shows the data as expected. If the examiner has 1 programme type
then there is one record for them but if they have 2 prog types then 2
records appear. This is because the query is based on 2 tables: tblExaminer
and tblProgType. There is a one-to-many relationship between these but i have
added restrictive code to only allow a max of 2 records per examiner. The
query looks like this:
ExaminerId - ExaminerName - ProgType - ProgDesc
1 - John - UG - UG BSC Chemistry
1 - John - PG - MA Chemistry
2 - Joe - UG - Bsc Hons Biology
3 - Jim - PG - PHd physics
what i need is the ug & pg info appearing on one record line for the examiner
so only one line (one record) exists for each examiner. eg:
ExamId - Name - UGPrgType - UGPrgDesc - PGPrgType - PGPrgDesc
1 - John - UG - BSC Chemistry - PG - MA Chemistry
2 - Joe - UG - Bsc Hon Biology - -
3 - Jim - - - PG - PHd physics
I have been trying to get this working using a union query but i can't seem
to get me finger on it. It seems to be an issue that may be quite common
within a relational database but i can't seem to find a solution for this.
Please note this is not an attempt to concatenate the 2 seperate fields into
one but to show them as seperate fields within the same record rather than
appearing as 2 records in the query.
Anyone have any ideas how i need to do this?
would a cross tab query work in a MS Word mail merge document?
The data within the query needs to be placed in letters using the mail merge facility within MS word and i'm not sure it will work with a crosstab query.
I'm not trying to concatenate the data into 1 field.
I'm trying to get all the fields in one record for an examiner.
Each examiner can have 1 or 2 prog type records. when they have 2 prog type records then the query will show 2 records for the examiner (1 with prog type UG & 1 with PG).
I need all this info but i want it showin in one record (not one field).
The reason for this is that this query will be used in a mail merge Word document to send examiners details of the programmes they are involved in.
only 1 letter will be sent to each examiner even if they have 2 prog type records so both prog type records need to appear in the same mail merge letter.
With my current query there are two records for an examiner with 2 prog type records. This means that 2 letters will be generated for that examiner unless i can somehow add the fields together to form a single record for the examiners.
I think i found a solution.
I think you can do what i want in a cross tab query. You just have to customise the rows you want to see. A crosstab query itself does not seem to work for Microsoft Word Mailmerges but if you create a new ordinary query and use the crosstab query to transfer the data to the new query, you can then use the new ordinary query in a mailmerge.
This seems to work. If anyone can see any problems with this method then please let me know.
Thanks for all your help guys,
Although it looks like you came up with a solution using a query against a crosstab query, there is another way that uses a single query.
SELECT tblExaminer.ExamID, tblExaminer.Name, Max(IIf([ProgType]="UG","UG","")) AS UGPrgType, Max(IIf([ProgType]="UG",[ProgDesc],"")) AS UGPrgDesc, Max(IIf([ProgType]="PG","PG","")) AS PGPrgType, Max(IIf([ProgType]="PG",[ProgDesc],"")) AS PGPrgDesc
FROM tblExaminer LEFT JOIN tblProgType ON tblExaminer.ExamID = tblProgType.ExamID
GROUP BY tblExaminer.ExamID, tblExaminer.Name;