Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2002
    Location
    Guernsey
    Posts
    13

    Unanswered: Merging fields query problem

    Hi.

    I have a table that consists of (and has to consist of) 3 fields: Name, ID Type 1 and ID Type 2.
    Name is obvious and just names the person
    ID Types 1 and 2 both can contain types of identification, passports, drivers license etc.
    However, in one of my queries, I require being able to count how many ID's are in the form of a passport, how many as driving licenses, etc and so would like to generate a query with one field, ID, with all the data from ID Type 1 and ID Type 2 in a big list so that I can count them.

    Thanks,

    Adam

  2. #2
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    I'm not quite sure I follow you but have a look at the grouping (Totals) function in a query to give you a start.
    Access XP & WinXP Pro

  3. #3
    Join Date
    Mar 2002
    Location
    Guernsey
    Posts
    13
    OK I've just re-read my post and realised how confusing it sounded.
    I'll try and explain it a bit simpler.
    From my initial table with ID1 and ID2, I would like to create a query that produces one field (lets call it ID3) which contained all the data from both ID1 and ID2, for example,

    tbl_1
    Name | Id1 | Id2
    Dave passport d.license
    Chris d.license national id card
    Becky passport national id card
    (the id's are not restricted to these three however)

    qry_Results (what I'd like to achieve)
    ID
    passport
    d.license
    d.license
    national id card
    passport
    national id card

    I hope this makes it a bit clearer as to what I'd like

    Thanks
    Adam

  4. #4
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    The easiest way I can see how to do this would be to count the occurences of each type of ID in the different fields:

    with field ID1 create a query and add the field ID1 twice to the query, click the totals function and select "group by" for the first ID1 field and "count" for the second ID1 field. Ths is will give a count of each type of ID held in the field ID1.

    Repeat this for your field ID2, you can count them - probably easier to export each result in excel.

    I hope this makes sense? - there are more "refined" ways of doing this but this will be the easiest.
    Access XP & WinXP Pro

  5. #5
    Join Date
    Mar 2002
    Location
    Guernsey
    Posts
    13
    It sort of does, and I see how it will work, but you see I'd like it in the format I originally suggested so that I can create a chart from the query, which I don't think I'd be able to do with your suggestion? or would i?

  6. #6
    Join Date
    Nov 2002
    Location
    Sweden
    Posts
    14
    Have you tried this?

    SELECT Id1 AS Id3 FROM tbl_1
    UNION ALL SELECT Id2 FROM tbl_1;

    //Sebastian
    Sebastian

  7. #7
    Join Date
    Mar 2002
    Location
    Guernsey
    Posts
    13
    Superb!!! So simple yet so effective.

    Thanks Sebastian

    Adam

Posting Permissions

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