Query to return one record from a field that has multiple records for a customer

[pixar1264]

I have a table that I am querying off of, that for some of our customers has more then one entry. I am looking for a formula or a condition that will have it return the data with the first letter of the alphabet only and not multiple letters. Here is the data I'm working with:

Record Key	ID Type	Description	State
383326	C	Alien Registration
413064	A	Driver's License	NY
413067	A	Driver's License	NY
413067	D	Other Identification	NY
413068	A	Driver's License	NY
413097	B	Passport
413100	A	Driver's License	NY
413100	D	Other Identification	NY
413115	B	Passport
413218	D	Other Identification	NY
430565	C	Alien Registration	NY

I want it to only return the following:

Record Key	ID Type	Description	State
383326	C	Alien Registration
413064	A	Driver's License	NY
413067	A	Driver's License	NY
413068	A	Driver's License	NY
413097	B	Passport
413100	A	Driver's License	NY
413115	B	Passport
413218	D	Other Identification	NY
430565	C	Alien Registration	NY

I'm still somewhat new to this world of data grooming and I've tried a couple of different things, but haven't been able to figure it out yet. Any help would be appreciated, thanks in advance!!

[Bob Fitz]

Perhaps you just need to set criteria value of "A" in Type ID column in your query.

[June7]

That looks like you want only the A records or only the License records. Can apply a filter for those parameters.

SELECT * FROM tablename WHERE [Type ID] = "A";

SELECT * FROM tablename WHERE [Description ID] = "License";

Advise no spaces in naming convention.

[pixar1264]

The problem is that some people don't have an A, they may have only a B (Passport), C (Foreign Id), or D (other ID) So I would need that data to return for those customers. Sorry I didn't explain it great, I was having a hard time putting it into words.

[NOEL71]

I agree with bob fitz

[June7]

Maybe TOP N qualifier will work, review http://allenbrowne.com/subquery-01.html#TopN

or

SELECT * FROM tablename WHERE [Type ID] = DMin("[Type ID], "tablename", "[Record Key]=" & [Record Key]);

Is Record Key a number field?

[pixar1264]

I updated my original post to try to better explain what I was trying to do. I don't only want the "A"'s or only Driver Licenses since not everyone has that option and it would not return the letters for the other customers. I just want the "A"'s for people where either that is the only option for them or for people with multiple letters, including an "A" to return only that A for that specific customer.

I hope that makes a little more sense!!

[pixar1264]

No, all of the fields are text fields...

[June7]

Text fields will need apostrophe delimiters for the parameter, data/time uses # character.

"[Record Key]='" & [Record Key] & "'"

[pixar1264]

Ok, I'm going to give that a try this morning. Hopefully a fresh mind helps too

[lfpm062010]

Are you trying to get the first Alpha (sorted ID Type) record for each customer?

If that is true, just sort the records (Record Key and ID Type). Then using "Group BY" and "First" of "ID Type" should give you what you need.

SELECT customers.[Record Key], First(customers.[ID Type]) AS [FirstOfID Type], First(customers.Description) AS FirstOfDescription, First(customers.State) AS FirstOfState
FROM customers
GROUP BY customers.[Record Key]
ORDER BY customers.[Record Key], First(customers.[ID Type]);

[pixar1264]

That TOP N subquery worked out. Thank you for pointing me in the right direction, I was trying to do too much in one shot!!

[pixar1264]

Are you trying to get the first Alpha (sorted ID Type) record for each customer?

If that is true, just sort the records (Record Key and ID Type). Then using "Group BY" and "First" of "ID Type" should give you what you need.

SELECT customers.[Record Key], First(customers.[ID Type]) AS [FirstOfID Type], First(customers.Description) AS FirstOfDescription, First(customers.State) AS FirstOfState
FROM customers
GROUP BY customers.[Record Key]
ORDER BY customers.[Record Key], First(customers.[ID Type]);

Ahh...seeing this shows me where I went wrong when I tried that query. I did not do the sort when I had picked to order it by first. Mental note has been made, thank you for your input lfpm062010!!