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.
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,
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)
national id card
national id card
I hope this makes it a bit clearer as to what I'd like
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.
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?