Union query to merge fields into one record

[dabooj via AccessMonster.com]

hi,

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:

ExaminerId - ExaminerName - UGProgType - UGProgDesc - PGProgType -
PGProgDesc
1 - John - UG - UG BSC Chemistry
- PG - MA Chemistry
2 - Joe - UG - Bsc Hons 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?

Any help would be greatly appreciated.

thank you,

Shuja

 

[Marshall Barton]

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:

ExaminerId - ExaminerName - UGProgType - UGProgDesc - PGProgType -
PGProgDesc
1 - John - UG - UG BSC Chemistry
- PG - MA Chemistry
2 - Joe - UG - Bsc Hons 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.

A UNION query can't do that, you need to use a function in a
basic Select query for the one side table. Then function is
used to collect the related data from the many side table.
There are many of these functions available one the web.
Here's a popular one:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

 

[dabooj via AccessMonster.com]

Hi,

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,

Shuja.

I have a query which shows the info i require. It shows Examiners & the
programme types they are associated to.

[quoted text clipped - 31 lines]

one but to show them as seperate fields within the same record rather than
appearing as 2 records in the query.

A UNION query can't do that, you need to use a function in a
basic Select query for the one side table. Then function is
used to collect the related data from the many side table.
There are many of these functions available one the web.
Here's a popular one:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'