Using If or IIF to find duplicate records in different tables

[Ndel40]

I have a table that is populated with data by importing a text file created
from a SAP download once per week. I have automated the process, however, I
see a potential problem with duplicating data due to overlapping SAP download
date ranges.

In order to prevent duplication, I would like to import the text file to a
temporary table, create a concatenated field for several of the imported
fields and compare the concatenation to the same concatenation in the master
table.

I would like use and “if†statement that does the evaluation and if no
duplicates are found the data is imported and appended to the master table.
If duplicates exist, find the max data and time from the master table and
display a message… something like this:

If no duplicated records exist then

Import the text file and append it to the master data file
Else
Display a message that says “Data already exists – The last record imported
was 10/11/04 12:31:00 PM. Please re-run the SAP downloadâ€

Endif

I’m sure there are other ways to accomplish what I need… any ideas are
welcome.

FYI… I am using Access 97.

Thanks,

Nick

 

[Jeff Boyce]

Nick

Another approach would be to set a unique index on the combination of fields
that needs to be unique. That way, when you try to append records, any that
match existing records are discarded.

 

[Ndel40]

Jeff,

Good idea... however, I need to notify the user that they need to re-run the
SAP report using a differnt date range.

Any other ideas?

Thanks,

Nick

 

[John Spencer (MVP)]

Well, some simple vba code might do what you want

IF DMAX("DateTimeField","TableToGetRecords") >=
DMIN("DateTimeField","TempTable") Then
MsgBox "DateRange Error"
END IF

IF you have other ways to tell about duplicates you could do a join on the two
tables on all the relevant fields and then if the count of the records was more
than zero, you could generate your message.

SQL would be something like

SELECT A.FieldOne
FROM Target As A INNER JOIN Source As S
ON A.FieldOne = B.FieldOne AND
A.FieldThree = B.FieldThree AND
...

Then you could use
IF DCount("*","TheSavedQuery") > 0 THEN
MSGBox "Whatever"
Else
'Execute your import
End if

 

[Ndel40]

What is the format for referencing a specific field in a table? I tried it
as written below (substituting my field and table names), but it does not
work (with or with out the quotes).

Do I need to somehow declare the table and fields?

Thanks,

Nick