DISTINCT in Access question

[The Unknown]

Hello. I'm making a module to load info from a spreadsheet into the right tables. I have a query that looks like this:

Set orig_clients = CurrentDb.OpenRecordset( _
        "(SELECT name, ssn, number, status FROM table WHERE (name IS NULL OR ssn IS NULL) AND number IS NOT NULL)" _
        & " UNION " _
        & "(SELECT DISTINCT name, ssn, number, status FROM table WHERE number IS NOT NULL AND name IS NOT NULL AND ssn IS NOT NULL)" _
        & "ORDER BY number")

It takes all the distinct entries where the name and ssn is defined and all the entries where name or ssn is null and loads them together in one set. My problem is I want the number column but if two names have two different numbers, the names will repeat. How can I find all the distinct names but also load the numbers?

Any help would be greatly appreciated

[Creosote]

I don't quite understand your question...but here is a rundown of DISTINCT.

DISTINCT will select rows based on the entire display result. So whatever fields you choose to display, it will evaluate all of them for their uniqueness.

So basically, I don't know what you are asking. Do you want multiple entries for each name? Or do you want the sum of the numeric columns for each name?

[vass0922]

distinct should give you exactly what you're asking for.
If you include the number column AND those numbers are different in each row it will give you seperate records for the two rows in the spreadsheet.

[ctaylor]

One option, not the most elegant by any means, is to run queries using docmd.openquery instead of using recordsets.

This way you could create an temporary table where the names field is indexed allowing for no duplicates.

You could then run either a series of APPEND queries (one for each case described in your UNION query), or alternately take the results of the UNION query into a second query. This second query could be converted into APPEND query writing to the temporary table.