Solved

do not append duplicate records

Posted on 2003-11-25
13
668 Views
Last Modified: 2008-02-07
Hi,

Hope you could help me out.

Inventory records (which have material document number, material code and description, quantity) are uploaded in a table three times a day because there are 3 inventory clerks working in shifts.  The second shift inventory clerk may may capture the same inventory records but what I want is that the same inventory records should not be appended anymore.
For example,  the following inventory records were uploaded by inventory clerk shift 1 at 8 am to table1.  records in table 1 will be automatically appended to table2 throught the ff. codes:

DoCmd.OpenQuery "Append material movements", acViewNormal, acEdit

INVENTORY RECORDS, SHIFT 1 (8AM)
material document number      material code      material description      quantity
1                                              aa                            product aa       400
2                                              bb                            product bb       200
3                                              cc                            product cc       400

At 2pm, inventory clerk 2 will generate the material movements again and upload the ff. inventory records to table1 (previous records are automatically deleted) and append records in table1 to table 2.
INVENTORY RECORDS, SHIFT 2 (5PM)
material document number      material code      material description      quantity
1                                              aa                            product aa       400
4                                              dd                            product dd       100
5                                              dd                            product dd       450

Notice that No. 1 above have already been taken up in shift 1(8am).  Therefore, I would like a code that would prompt an error message (ok button) that this record has already been appended.  Upon clicking on OK, next unique records will be appended.

Hope you can help me asap.  Thanks very much.


Regards,
chi-chi
0
Comment
Question by:CHICHI120976
  • 5
  • 2
  • 2
  • +2
13 Comments
 
LVL 10

Expert Comment

by:jobrienct
ID: 9816407
you could simply wait until the end of day, after all 3 shifts have completed their updates, and run a select distinct * INTO tblFinal FROM tblDaily;

or, alternatively, you could set the material document number as the primary key in your update tables, which would allow only distinct document numbers.

or if need be you could set all fields as the primary key together, and then you could have multiple document numbers as long as at least one of the other fields was different.

regards,

John
0
 

Author Comment

by:CHICHI120976
ID: 9816451
i could not wait until the end of the shift because this involves inventory counts per shift.


i have set the material document number as primary but it just stops when duplicate records exist.  what i hopefully want to achieve is when a duplicate record is detected, a message box will appear that duplicate records are detected but it will proceed to append the unique records.

pls. advise.  thx!
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9816475
Try:

INSERT INTO TblTarget(material, [document number], [material code],[material description], quantity) SELECT tblSource.material, tblSource.[document number], tblSource.[material code],tblSource.[material description], tblSource.quantity
FROM tblSource GROUP BY tblSource.material, tblSource.[document number], tblSource.[material code],tblSource.[material description], tblSource.quantity

Mike


0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9816485
A second solution is possible (if it applies in your case) is to install an index in your target table to include fields not to be repeated.  Set unique property of this index to yes.  This way, no matter how many times you append it, it will be ignored is such combination already exists.

Mike
0
 
LVL 15

Expert Comment

by:dbase118
ID: 9817237
I know its not user friendly but if you are running an append and it finds duplicates it should bring up a message/results screen where it would tell you how many records were not added and why, Something like

5 records were not added due to key violations means that it found five records where the PK was already in the other table.
0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9817316
ChiChi,

basically what you want to do demands VB programming. I will suggest an alternative solution which will:

1. Only append new records, and will be able to run during the day
2. Show you a report of 'invalid records' from the input table

1. When you make an append-query, you have the opportunity to include the target database for setting some filter rules.

a. Open your append query in the Query designer and add the target-table to the design area.
b. Create joins between the source and target table to ensure uniqueness
    (e.g. make joins for all the fields you've mentioned in your query)
c. In the design window, make sure ypu have your source-table on the right and target table on the left
d. Right-click all joins and select "Include all from (source table) and only ...... match"
e. Verify that all joins point FROM the source table to the target table
d. Drag the Document Number from the target table to a free column
e. Clear the "Append to"-field in that column
f. In the condition field in that column, put "Is Null"

Now the query will only append records that doesn't already exist

2.When you've verified that this query works, make a copy of it and call it something like "Invalid import records report".

a. Open the new query in the Query editor
b. Change the query from being an append query to become a select query
c. Find the column with the "Is Null" condition and change it to "Is Not Null"

Now you can view or print a report on already existing records.

Kind regards,
Sven
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9817337
Oops!

correction:

> c. In the design window, make sure you have your source-table on the LEFT and target table on the RIGHT


 - but it actually doesn't matter. I specified that, because in writing it I wanted e) to be:

> e. Verify that all joins point from left to right: FROM the source table to the target table
                                            --^--------^--------^--

;-)

Sven
0
 
LVL 10

Expert Comment

by:jobrienct
ID: 9817595
frankly from my reading we don't really know how the data is being "uploaded" or necessarily, how many tables are involved, I see 2 for 2 shifts with a description of a 3rd shift, does the 3rd shift alos update from table1 to table2? Once we know the methodology giving you a msgbox on error is simple. does the shift operator use a form? why does he "capture" previously updated inventory items?

John
0
 

Author Comment

by:CHICHI120976
ID: 9817776
thanks for the overwhelming responses.

scenario is as follows:

1.  i created a form with a textbox, a browse button and an upload button.  when i click on browse, i can select an excel file from my directories.  this file will then be entered on the textbox.  when i click on 'upload', any data in the table 1 will be deleted (through a delete query), the file indicated in the textbox will be imported to table 1 and then appended to table 2.  
upon appending to table 2, there must be a checking on the number of duplicate records and only the unique records should be appended.  I did some testing and found out that what dbase118 is saying is correct.  the message box was not user friendly so i ignored it the first time.  i read it again today and found out that message says that x number of records violated the rules (which means these are deemed as duplicate records).  but only the unique records were uploaded.  my problem now is, how to change this message box so that it would be more user friendly.  How i can change it to something like, "x number of records already exist, duplicate records are not allowed!  Only y number of records will be uploaded"

would appreciate your help.  thanks a lot!


regards,
chi-chi
0
 
LVL 10

Expert Comment

by:jobrienct
ID: 9818164
well, you might want to consider whether or not your users even need to know this. I guess thats dependent on whether or not they are involved with the data after it has been uploaded. If they do not need to worry about it you can disable these warnings before upload and enable them afterward to make sure other warnings are heeded (the warnings settings are system wide).  IF this works for you then your job is done since only unique records will be appended. To do this :

DoCmd.SetWarnings False

   'run your append query

DoCmd.SetWarnings True

If this does not suit you then you need to use the On error trap to present your custom message to the user. I'll post an example in a minute.

John



0
 
LVL 10

Expert Comment

by:jobrienct
ID: 9818210
If your situation requires that you inform the user of whats going on then in your command buttuns click event...

Sub Command5_Click
On Error GoTo Command5_Err

   DoCmd.SetWarnings False
   DoCmd.OpenQuery "qryAppendMytable"
   DoCmd.SetWarnings True

Exit Sub

Command5_Err:
   Msgbox Err.Number & vbcrlf & Err.Description
   Err.Clear
   DoCmd.SetWarnings TRUE
End Sub

Of course you can customize this message there.

regards,

JOhn
0
 
LVL 10

Accepted Solution

by:
jobrienct earned 500 total points
ID: 9818224
Sorry using Command5 as the button name was apparently because someone elses question included it. change the button name and goto point to match your needs.

John
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now