CHICHI120976
asked on
do not append duplicate records
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
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
ASKER
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!
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!
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
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
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
Mike
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.
5 records were not added due to key violations means that it found five records where the PK was already in the other table.
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
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
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
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
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
John
ASKER
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
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
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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