Unique Records

E

Ernst Guckel

Hi,

Maybe i am going about this all wrong... I have a CSV file that has 167
records in it. In each record there is a company name, a contact name and
other data... i am trying to create 2 append queries... one that appends the
company data to tblCompanies and another to append contact data to
tblContacts... they are linked via the CompanyID... my append query adds ALL
company records... even if there are more than one contact per conpany. I
just want one company added. Then the contacts can be added to the contacts
table with an innor join?? to the tblContacts...

Here is the select query i am using to get the right data to show up but I
want Distinct for "CompanyBusinessName, Address1, Address2, City, State, and
Zip"
and i want other fields to populate as well but for the first occurance...
i dont want them to be used in the "Distinct" key work.. Here is what I
have...

Any help would be great...

SELECT DISTINCT tblA.CardScanData.Company, tblA.CardScanData.Address1,
tblA.CardScanData.Address2, tblA.CardScanData.City, tblA.CardScanData.Zip
FROM (SELECT CardScanData.Company, CardScanData.Address1,
CardScanData.Address2, CardScanData.City, CardScanData.State,
CardScanData.Zip, CardScanData.[2ndAddress1], CardScanData.[2ndAddress2],
CardScanData.[2ndCity], CardScanData.[2ndState], CardScanData.[2ndZip],
CardScanData.MainPhone, CardScanData.WebPage FROM CardScanData) AS tblA;
 
K

KARL DEWEY

Open tblCompanies in design view and create an index for Company field and
set to unique, No Duplicates.
When you append it will only allow one.
 
E

Ernst Guckel

I could do that but i am looking for a uniqueness over multiple fields...

KARL DEWEY said:
Open tblCompanies in design view and create an index for Company field and
set to unique, No Duplicates.
When you append it will only allow one.
--
KARL DEWEY
Build a little - Test a little


Ernst Guckel said:
Hi,

Maybe i am going about this all wrong... I have a CSV file that has 167
records in it. In each record there is a company name, a contact name and
other data... i am trying to create 2 append queries... one that appends the
company data to tblCompanies and another to append contact data to
tblContacts... they are linked via the CompanyID... my append query adds ALL
company records... even if there are more than one contact per conpany. I
just want one company added. Then the contacts can be added to the contacts
table with an innor join?? to the tblContacts...

Here is the select query i am using to get the right data to show up but I
want Distinct for "CompanyBusinessName, Address1, Address2, City, State, and
Zip"
and i want other fields to populate as well but for the first occurance...
i dont want them to be used in the "Distinct" key work.. Here is what I
have...

Any help would be great...

SELECT DISTINCT tblA.CardScanData.Company, tblA.CardScanData.Address1,
tblA.CardScanData.Address2, tblA.CardScanData.City, tblA.CardScanData.Zip
FROM (SELECT CardScanData.Company, CardScanData.Address1,
CardScanData.Address2, CardScanData.City, CardScanData.State,
CardScanData.Zip, CardScanData.[2ndAddress1], CardScanData.[2ndAddress2],
CardScanData.[2ndCity], CardScanData.[2ndState], CardScanData.[2ndZip],
CardScanData.MainPhone, CardScanData.WebPage FROM CardScanData) AS tblA;
Click to expand...
Click to expand...
 
K

KARL DEWEY

If you click on Indexes you can create an index using multiple fields.
--
KARL DEWEY
Build a little - Test a little


Ernst Guckel said:
I could do that but i am looking for a uniqueness over multiple fields...

KARL DEWEY said:
Open tblCompanies in design view and create an index for Company field and
set to unique, No Duplicates.
When you append it will only allow one.
--
KARL DEWEY
Build a little - Test a little


Ernst Guckel said:
Hi,

Maybe i am going about this all wrong... I have a CSV file that has 167
records in it. In each record there is a company name, a contact name and
other data... i am trying to create 2 append queries... one that appends the
company data to tblCompanies and another to append contact data to
tblContacts... they are linked via the CompanyID... my append query adds ALL
company records... even if there are more than one contact per conpany. I
just want one company added. Then the contacts can be added to the contacts
table with an innor join?? to the tblContacts...

Here is the select query i am using to get the right data to show up but I
want Distinct for "CompanyBusinessName, Address1, Address2, City, State, and
Zip"
and i want other fields to populate as well but for the first occurance...
i dont want them to be used in the "Distinct" key work.. Here is what I
have...

Any help would be great...

SELECT DISTINCT tblA.CardScanData.Company, tblA.CardScanData.Address1,
tblA.CardScanData.Address2, tblA.CardScanData.City, tblA.CardScanData.Zip
FROM (SELECT CardScanData.Company, CardScanData.Address1,
CardScanData.Address2, CardScanData.City, CardScanData.State,
CardScanData.Zip, CardScanData.[2ndAddress1], CardScanData.[2ndAddress2],
CardScanData.[2ndCity], CardScanData.[2ndState], CardScanData.[2ndZip],
CardScanData.MainPhone, CardScanData.WebPage FROM CardScanData) AS tblA;
Click to expand...
Click to expand...
Click to expand...
 
E

Ernst Guckel

Ok thats done. It does a good job preventing duplicates but when i run the
append query it does not add any of the companies that exist more than once.
Here is the SQL:

INSERT INTO tblCompanies ( CompanyBusinessName, CompanyAddress1,
CompanyCity, CompanyState, CompanyWebsite, CompanyAddress2 )
SELECT DISTINCT CardScanData.Company, CardScanData.Address1,
CardScanData.City, CardScanData.State, CardScanData.WebPage,
CardScanData.Address2
FROM CardScanData;


KARL DEWEY said:
If you click on Indexes you can create an index using multiple fields.
--
KARL DEWEY
Build a little - Test a little


Ernst Guckel said:
I could do that but i am looking for a uniqueness over multiple fields...

KARL DEWEY said:
Open tblCompanies in design view and create an index for Company field and
set to unique, No Duplicates.
When you append it will only allow one.
--
KARL DEWEY
Build a little - Test a little


:

Hi,

Maybe i am going about this all wrong... I have a CSV file that has 167
records in it. In each record there is a company name, a contact name and
other data... i am trying to create 2 append queries... one that appends the
company data to tblCompanies and another to append contact data to
tblContacts... they are linked via the CompanyID... my append query adds ALL
company records... even if there are more than one contact per conpany. I
just want one company added. Then the contacts can be added to the contacts
table with an innor join?? to the tblContacts...

Here is the select query i am using to get the right data to show up but I
want Distinct for "CompanyBusinessName, Address1, Address2, City, State, and
Zip"
and i want other fields to populate as well but for the first occurance...
i dont want them to be used in the "Distinct" key work.. Here is what I
have...

Any help would be great...

SELECT DISTINCT tblA.CardScanData.Company, tblA.CardScanData.Address1,
tblA.CardScanData.Address2, tblA.CardScanData.City, tblA.CardScanData.Zip
FROM (SELECT CardScanData.Company, CardScanData.Address1,
CardScanData.Address2, CardScanData.City, CardScanData.State,
CardScanData.Zip, CardScanData.[2ndAddress1], CardScanData.[2ndAddress2],
CardScanData.[2ndCity], CardScanData.[2ndState], CardScanData.[2ndZip],
CardScanData.MainPhone, CardScanData.WebPage FROM CardScanData) AS tblA;
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

but when i run the append query it does not add any of the companies that
exist more than once.
I do not understand this comment. Are you saying if a company is listed
twice in your CardScanData table it does not append a single record for it?
 
E

Ernst Guckel

Yes. Weird...

KARL DEWEY said:
exist more than once.
I do not understand this comment. Are you saying if a company is listed
twice in your CardScanData table it does not append a single record for it?
Click to expand...
 
Top