Append query

[Ernst Guckel]

Hello,

I have two questions... the first is that I am having a bit of trouble
writting the append query. It works great as a select query but when I
change it to an append query I get errors...

There is a ContactID field as autonumber in tblContacts but that should work
fine... ContactCOmpany is a number but it looks up the data fine in the
select query...
2. How do I prevent duplicated over three criteria fields in the query as
well...

Thanks,
Ernst.

Here is the SQL:

INSERT INTO tblContacts ( ContactCompany, ContactLastName, ContactFirstName,
[ContactE-mail], ContactJobTitle, ContactBusinessPhone, ContactMobilePhone,
ContactFaxNumber, Notes )
SELECT DLookUp("CompanyID","tblCompanies","[CompanyBusinessName] = " &
Chr(34) & [Company] & Chr(34)) AS Expr1, CardScanData.LastName,
CardScanData.FirstName, CardScanData.Email, CardScanData.Title,
CardScanData.MainPhone, CardScanData.PhoneMobile, CardScanData.Fax,
CardScanData.Notes
FROM CardScanData;

 

[Lou]

Hello,

  I have two questions...  the first is that I am having a bit of trouble
writting the append query.  It works great as a select query but when I
change it to an append query I get errors...  

There is a ContactID field as autonumber in tblContacts but that should work
fine...  ContactCOmpany is a number but it looks up the data fine in the
select query...
2.  How do I prevent duplicated over three criteria fields in the queryas
well...

Thanks,
Ernst.

Here is the SQL:

INSERT INTO tblContacts ( ContactCompany, ContactLastName, ContactFirstName,
[ContactE-mail], ContactJobTitle, ContactBusinessPhone, ContactMobilePhone,
ContactFaxNumber, Notes )
SELECT DLookUp("CompanyID","tblCompanies","[CompanyBusinessName] = " &
Chr(34) & [Company] & Chr(34)) AS Expr1, CardScanData.LastName,
CardScanData.FirstName, CardScanData.Email, CardScanData.Title,
CardScanData.MainPhone, CardScanData.PhoneMobile, CardScanData.Fax,
CardScanData.Notes
FROM CardScanData;

It would have been helpful to know the error that was reported.

I easily see two possibilities:
--the DLookup function returned a long integer that already existed
in tblContacts, thus causing a duplicate key error;
--the Dlookup function returned a NULL because there was no "Company"
in tblCompanies, thus causing a type-mismatch error.

Why not writing a without the DLookup?

SELECT DISTINCT B.CompanyID, A.LastName, A.FirstName, A.Email,
A.Title,
A.MainPhone, A.PhoneMobile, A.Fax, A.Notes
from CardScanData as A inner join tblCompanies as B
on A.Company = B.CompanyBusinessName ;

 

[Ernst Guckel]

That worked great... So there are two concerns.. One: if there is no company
then it sould use 1 for a value and Second: how do I prevent duplicates from
being added to the append table...

Ernst.

Hello,

I have two questions... the first is that I am having a bit of trouble
writting the append query. It works great as a select query but when I
change it to an append query I get errors...

There is a ContactID field as autonumber in tblContacts but that should work
fine... ContactCOmpany is a number but it looks up the data fine in the
select query...
2. How do I prevent duplicated over three criteria fields in the query as
well...

Thanks,
Ernst.

Here is the SQL:

INSERT INTO tblContacts ( ContactCompany, ContactLastName, ContactFirstName,
[ContactE-mail], ContactJobTitle, ContactBusinessPhone, ContactMobilePhone,
ContactFaxNumber, Notes )
SELECT DLookUp("CompanyID","tblCompanies","[CompanyBusinessName] = " &
Chr(34) & [Company] & Chr(34)) AS Expr1, CardScanData.LastName,
CardScanData.FirstName, CardScanData.Email, CardScanData.Title,
CardScanData.MainPhone, CardScanData.PhoneMobile, CardScanData.Fax,
CardScanData.Notes
FROM CardScanData;

It would have been helpful to know the error that was reported.

I easily see two possibilities:
--the DLookup function returned a long integer that already existed
in tblContacts, thus causing a duplicate key error;
--the Dlookup function returned a NULL because there was no "Company"
in tblCompanies, thus causing a type-mismatch error.

Why not writing a without the DLookup?

SELECT DISTINCT B.CompanyID, A.LastName, A.FirstName, A.Email,
A.Title,
A.MainPhone, A.PhoneMobile, A.Fax, A.Notes
from CardScanData as A inner join tblCompanies as B
on A.Company = B.CompanyBusinessName ;

 

[Lou]

That worked great...  So there are two concerns.. One: if there is no company
then it sould use 1 for a value and Second: how do I prevent duplicates from
being added to the append table...

Ernst.

Hello,
  I have two questions...  the first is that I am having a bit oftrouble
writting the append query.  It works great as a select query but when I
change it to an append query I get errors...  
There is a ContactID field as autonumber in tblContacts but that should work
fine...  ContactCOmpany is a number but it looks up the data fine in the
select query...
2.  How do I prevent duplicated over three criteria fields in the query as
well...
Thanks,
Ernst.
Here is the SQL:
INSERT INTO tblContacts ( ContactCompany, ContactLastName, ContactFirstName,
[ContactE-mail], ContactJobTitle, ContactBusinessPhone, ContactMobilePhone,
ContactFaxNumber, Notes )
SELECT DLookUp("CompanyID","tblCompanies","[CompanyBusinessName] = " &
Chr(34) & [Company] & Chr(34)) AS Expr1, CardScanData.LastName,
CardScanData.FirstName, CardScanData.Email, CardScanData.Title,
CardScanData.MainPhone, CardScanData.PhoneMobile, CardScanData.Fax,
CardScanData.Notes
FROM CardScanData;

It would have been helpful to know the error that was reported.

I easily see two possibilities:
   --the DLookup function returned a long integer that already existed
in tblContacts, thus causing a duplicate key error;
   --the Dlookup function returned a NULL because there was no "Company"
in tblCompanies, thus causing a type-mismatch error.

Why not writing a without the DLookup?

SELECT  DISTINCT B.CompanyID, A.LastName, A.FirstName, A.Email,
A.Title,
   A.MainPhone, A.PhoneMobile, A.Fax, A.Notes
from CardScanData as A inner join tblCompanies as B
   on A.Company = B.CompanyBusinessName ;- Hide quoted text -

- Show quoted text -

Try these steps:

Step 1.

create unique index tblContacts_PK
on tblContacts( ContactCompany, ContactLastName, ContactFirstName ) ;

This will ensure that there are no duplicates on these three fields.

Step 2 may be repeated as necessary.

INSERT INTO tblContacts ( ContactCompany, ContactLastName,
ContactFirstName,
[ContactE-mail], ContactJobTitle, ContactBusinessPhone,
ContactMobilePhone,
ContactFaxNumber, Notes )
SELECT 1, A.LastName, A.FirstName, A.Email, A.Title,
A.MainPhone, A.PhoneMobile, A.Fax, A.Notes
from CardScanData as A left join tblCompanies as B
on A.Company = B.CompanyBusinessName
where B.CompanyBusinessName is null
and 1 = ( SELECT count(*)
FROM CardScanData
where LastName = A.LastName
and FirstName = A.FirstName )
and not exists
( SELECT 'true'
FROM tblContacts
where LastName = A.LastName
and FirstName = A.FirstName ) ;

This will insert rows for those CardScanData LastName-FirstName pairs
that have no match in tblCompanies. Repeat this query, incrementing
the 1 in "and 1 = SELECT Count(*)", until the query stops inserting
rows,

Step 3 may be repeated as necessary.

INSERT INTO tblContacts ( ContactCompany, ContactLastName,
ContactFirstName,
[ContactE-mail], ContactJobTitle, ContactBusinessPhone,
ContactMobilePhone,
ContactFaxNumber, Notes )
SELECT B.CompanyID, A.LastName, A.FirstName, A.Email, A.Title,
A.MainPhone, A.PhoneMobile, A.Fax, A.Notes
from CardScanData as A left join tblCompanies as B
on A.Company = B.CompanyBusinessName
where 1 = ( SELECT count(*)
FROM CardScanData
WHERE Company = B.CompanyBusinessName
where LastName = A.LastName
and FirstName = A.FirstName )
and not exists
( SELECT 'true'
FROM tblContacts
where CompanyContact = B.CompanyID
LastName = A.LastName
and FirstName = A.FirstName ) ;

Repeat this query, incrementing the 1 in "and 1 = SELECT Count(*)",
until the query stops inserting rows,