Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    30

    Exclamation Unanswered: Union query to merge fields into one record

    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:

    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?

    Any help would be greatly appreciated.

    thank you,

    Shuja

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    A union query is not really what you want here (you could go the other way with one). Try playing with the crosstab query wizard and see if you get what you want.
    Paul

  3. #3
    Join Date
    Sep 2006
    Posts
    30

    Exclamation

    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.

    Shuja

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Not sure, never tried. However, why do you not want to concatenate the values into 1 field? I suspect that would work fine in a mail merge.
    Paul

  5. #5
    Join Date
    Sep 2006
    Posts
    30

    Exclamation

    Hi,

    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.

    Hope this helps clear the situation.

    Thanks,

    Shuja.

  6. #6
    Join Date
    Sep 2006
    Posts
    30
    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.

  7. #7
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Hi Shuja,

    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;



    TD

Posting Permissions

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