Code for Multiple Duplicates

[mbogan]

I have two tables:

Table ONE:

NAME: ACCOUNTS
FIELDS: 1
FIELD 1: SSN

Table TWO:
NAME: 1999ab
FIELDS: Many

I have a query that creates a new table for ALL Social Security numbers that match Table TWO.

Here is the problem. Within Table Two there are alot of duplicate Social Security Numbers. I only need one row of data in my new table that contains this information. So, How can I get my duplicate query to only match ONE time. This will give me a new table that has the SSN from TABLE ONE once with the corresponding NAMES and ADDRESSES from TABLE TWO.

Here is the Code I am using for my initial duplicate search:

SELECT ACCOUNTS.* INTO [ACCOUNTS NEW]
FROM ACCOUNTS INNER JOIN 1999ab ON ACCOUNTS.SSN0 = [1999ab].SSN
WHERE (((ACCOUNTS.SSN0)=([1999ab].[SSN])));


Who can help my ADD this to my code above?

 

[Joe4]

Try adding the word DISTINCT to your query, i.e.:

SELECT DISTINCT ACCOUNTS.* INTO [ACCOUNTS NEW]
FROM ACCOUNTS INNER JOIN 1999ab ON ACCOUNTS.SSN0 = [1999ab].SSN
WHERE (((ACCOUNTS.SSN0)=([1999ab].[SSN])));

 

[mbogan]

I added the DISTINCT and received the same query results which included multiple records matching the SSN

I read somewhere about a FETCH command??

If not are there any other suggestions??

 

[Joe4]

I believe the DISTINCT command will only work if ALL your fields in your query are the same. If, for instance, you have duplicate SSNs, but they have a different field elsewhere in the query, I don't think it will work.

If that field that differs is not needed in the query, then you can remove it and then the query should hopfully work with DISTINCT.

 

[mbogan]

The TABLE TWO Data does have different information variations of data for each SSN number. I want to ONLY pull the first row that is found than move on. My TABLE TWO has over 300,000 records, so I cannot use the DISTINCT code.

There has got ot be a way to basically say, Upon a Match, move on to the next record.

Any more thoughts or IDEAS?? Again I read about a Fetch command, but I am not sure if it will work.

 

[Joe4]

I have never used FETCH, so I don't know anything about it.

If, in query mode, you click on the SUM icon, you will see a Totals line show up in the query box. One of the options is "First". What happens if you select this under the SSN field?