VBA Function to check for duplicates & insert record

[Secret Squirrel]

I need to create a function that checks 3 tables to see if a record exists.
If it doesn't then I need to create a record in one of the tables. There is
an import that runs nightly to tblTable4 and that's the data I need to
cross-reference with the tables below. After the import I want to run this
function to look at these tables to see if a record exists.

Tables to check:

tblTable1
tblTable2
tblTable3

Fields to check:

EmpID (Number)
Date (Date/Time)

If the record exists in any of these tables then do nothing.
If the record does not exist in any of these tables then create a record in
tblTable3 using these fields from tblTable4:

EmpID
Date

This field below is in tblTable3 and needs to have a default value of "OE"
for any record that is created by this function:

Type - Default value of "OE" for any record that is created

I'm not good with writing this type of code so I was hoping someone would
show me the way.

Thanks
SS

 

[Lou]

I need to create a function that checks 3 tables to see if a record exists.
If it doesn't then I need to create a record in one of the tables. There is
an import that runs nightly to tblTable4 and that's the data I need to
cross-reference with the tables below. After the import I want to run this
function to look at these tables to see if a record exists.

Tables to check:

tblTable1
tblTable2
tblTable3

Fields to check:

EmpID (Number)
Date (Date/Time)

If the record exists in any of these tables then do nothing.
If the record does not exist in any of these tables then create a record in
tblTable3 using these fields from tblTable4:

EmpID
Date

This field below is in tblTable3 and needs to have a default value of "OE"
for any record that is created by this function:

Type - Default value of "OE" for any record that is created

I'm not good with writing this type of code so I was hoping someone would
show me the way.

Thanks
SS

Could you do the following?

INSERT INTO tblTable3 ( EmpID, Date, Type )
SELECT EmpID, Date, 'OE'
from tblTable4
where not exists
( SELECT 'true'
from tblTable1
where EmpID = tblTable4.EmpID
and Date = tblTable4.Date )
and not exists
( SELECT 'true'
from tblTable2
where EmpID = tblTable4.EmpID
and Date = tblTable4.Date )
and not exists
( SELECT 'true'
from tblTable3
where EmpID = tblTable4.EmpID
and Date = tblTable4.Date )

 

[Allen Browne]

Save this as (say) Query1:
SELECT EmpID, [Date] AS TheDate FROM tblTable1
UNION ALL
SELECT EmpID, [Date] AS TheDate FROM tblTable2
UNION ALL
SELECT EmpID, [Date] AS TheDate FROM tblTable3;

Now create a query like this:
SELECT EmpID, [Date] AS TheDate 'OE' AS TheType
FROM tblTable4
WHERE NOT EXISTS
(SELECT EmpID FROM Query1
WHERE Query1.EmpID = tblTable4
AND Query1.TheDate = tblTable4.[Date])

Verify that it gives the records you want.
Add DISTINCT if you need to remove dupes.
Then turn it into an Append query (Append you Query menu) to add the records
to tblTable3.

(Date is a reserved word, so the square brackets are needed.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Secret Squirrel]

Hello Allen,

I set it up as you had said but it's still writing the record even though it
exists in one of my tables. Here's how I wrote the two queries:

Query1:

SELECT EmpID, VDate AS TheDate FROM tblTable1
UNION ALL
SELECT EmpID, VDate AS TheDate FROM tblTable2
UNION ALL SELECT EmpID, VDate AS TheDate FROM tblTable3;

Query2:

INSERT INTO tblTable3 ( EmpID, VDate, TheType )
SELECT tblTable4.EmpID, tblTable4.VDate AS TheDate, "OE" AS TheType
FROM tblTable4
WHERE (((Exists (SELECT EmpID FROM Query1
WHERE Query1.EmpID = tblTable4
AND Query1.TheDate = tblTable4.VDate))=False));

Did I make a mistake somewhere?

Save this as (say) Query1:
SELECT EmpID, [Date] AS TheDate FROM tblTable1
UNION ALL
SELECT EmpID, [Date] AS TheDate FROM tblTable2
UNION ALL
SELECT EmpID, [Date] AS TheDate FROM tblTable3;

Now create a query like this:
SELECT EmpID, [Date] AS TheDate 'OE' AS TheType
FROM tblTable4
WHERE NOT EXISTS
(SELECT EmpID FROM Query1
WHERE Query1.EmpID = tblTable4
AND Query1.TheDate = tblTable4.[Date])

Verify that it gives the records you want.
Add DISTINCT if you need to remove dupes.
Then turn it into an Append query (Append you Query menu) to add the records
to tblTable3.

(Date is a reserved word, so the square brackets are needed.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I need to create a function that checks 3 tables to see if a record exists.
If it doesn't then I need to create a record in one of the tables. There
is
an import that runs nightly to tblTable4 and that's the data I need to
cross-reference with the tables below. After the import I want to run this
function to look at these tables to see if a record exists.

Tables to check:

tblTable1
tblTable2
tblTable3

Fields to check:

EmpID (Number)
Date (Date/Time)

If the record exists in any of these tables then do nothing.
If the record does not exist in any of these tables then create a record
in
tblTable3 using these fields from tblTable4:

EmpID
Date

This field below is in tblTable3 and needs to have a default value of "OE"
for any record that is created by this function:

Type - Default value of "OE" for any record that is created

I'm not good with writing this type of code so I was hoping someone would
show me the way.

Thanks
SS

 

[Secret Squirrel]

Nevermind. I think I found the problem in query2. I forgot to add EmpID to
the tblTable4 in the where statement.

Revised:

INSERT INTO tblTable3 ( EmpID, VDate, TheType )
SELECT tblTable4.EmpID, tblTable4.VDate AS TheDate, "OE" AS TheType
FROM tblTable4
WHERE (((Exists (SELECT EmpID FROM Query1 WHERE Query1.EmpID =
tblTable4.EmpID AND Query1.TheDate = tblTable4.VDate))=False));

Save this as (say) Query1:
SELECT EmpID, [Date] AS TheDate FROM tblTable1
UNION ALL
SELECT EmpID, [Date] AS TheDate FROM tblTable2
UNION ALL
SELECT EmpID, [Date] AS TheDate FROM tblTable3;

Now create a query like this:
SELECT EmpID, [Date] AS TheDate 'OE' AS TheType
FROM tblTable4
WHERE NOT EXISTS
(SELECT EmpID FROM Query1
WHERE Query1.EmpID = tblTable4
AND Query1.TheDate = tblTable4.[Date])

Verify that it gives the records you want.
Add DISTINCT if you need to remove dupes.
Then turn it into an Append query (Append you Query menu) to add the records
to tblTable3.

(Date is a reserved word, so the square brackets are needed.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I need to create a function that checks 3 tables to see if a record exists.
If it doesn't then I need to create a record in one of the tables. There
is
an import that runs nightly to tblTable4 and that's the data I need to
cross-reference with the tables below. After the import I want to run this
function to look at these tables to see if a record exists.

Tables to check:

tblTable1
tblTable2
tblTable3

Fields to check:

EmpID (Number)
Date (Date/Time)

If the record exists in any of these tables then do nothing.
If the record does not exist in any of these tables then create a record
in
tblTable3 using these fields from tblTable4:

EmpID
Date

This field below is in tblTable3 and needs to have a default value of "OE"
for any record that is created by this function:

Type - Default value of "OE" for any record that is created

I'm not good with writing this type of code so I was hoping someone would
show me the way.

Thanks
SS