Originally Posted by
ssanfu
If you want to use "cust_code", let me know....
Here I meant selecting unique source records from table "tblSchChg" using field "Cust" OR "cust_code". After thinking about it, you could use both fields if you thought it was necessary/required/better.
Originally Posted by
slimjen
I did find the source of the duplicated records is because they use a union query to create the final tblschchg. So I will try to eliminate the duplicates before the table is create.
If the record are determined to be duplicates based on the fields in the query "r", you could try adding the predicate "DISTINCT" to see if the duplicates are eliminated. It all depends on how the records are determined to be duplicate and if you want to spend the time looking for/eliminating the duplicates.
Originally Posted by
slimjen
I can't imagine what grouping on the cust_code as well would look like whether it would make a huge difference. What do you think? would it be a big inconvenience to add this to see?
I can make the changes of you can.
You probably know how to do this, but these are the steps to modify the queries:
1) Make a COPY of the database for a backup. <<-- VERY important!!
2) Open the IDE
3) Find the following code segment. Modify the code:
(Comment out the lines with the RED, add the line with the BLUE)
Code:
'get unique "cust" records
' sSQL = "SELECT DISTINCT tblSchChg.Cust"
sSQL = "SELECT DISTINCT tblSchChg.cust_code"
sSQL = sSQL & " FROM tblSchChg;"
Set s = d.OpenRecordset(sSQL)
' Debug.Print sSQL
4) Then modify this code segment:
Code:
sSQL = "SELECT tblSchChg.RenDate, tblSchChg.Cust, tblSchChg.cust_id, tblSchChg.bus_name,"
sSQL = sSQL & " tblSchChg.address1, tblSchChg.bus_name2, tblSchChg.address2,"
sSQL = sSQL & " tblSchChg.address3, tblSchChg.city, tblSchChg.ST, tblSchChg.contact, tblSchChg.zip,"
sSQL = sSQL & " tblSchChg.fax, tblSchChg.ID, tblSchChg.phone, tblSchChg.st_id,"
sSQL = sSQL & " tblSchChg.st_id, tblSchChg.cust_code, tblSchChg.Current_Boss, tblSchChg.RealRATE,"
sSQL = sSQL & " tblSchChg.Rate2, tblSchChg.Rate3, tblSchChg.Rate4, tblSchChg.Rate5, tblSchChg.Rate6,"
sSQL = sSQL & " tblSchChg.Rate7, tblSchChg.Rate8, tblSchChg.Rate9, tblSchChg.Rate10, tblSchChg.Rate11,"
sSQL = sSQL & " tblSchChg.Current_RealRATE, tblSchChg.CurrentRate2, tblSchChg.CurrentRate3, tblSchChg.CurrentRate4,"
sSQL = sSQL & " tblSchChg.CurrentRate5, tblSchChg.CurrentRate6, tblSchChg.CurrentRate7,"
sSQL = sSQL & " tblSchChg.CurrentRate8, tblSchChg.CurrentRate9, tblSchChg.CurrentRate10, tblSchChg.CurrentRate11"
sSQL = sSQL & " FROM tblSchChg"
' sSQL = sSQL & " WHERE tblSchChg.Cust = '" & s!Cust & "';"
sSQL = sSQL & " WHERE tblSchChg.Cust = '" & s!cust_code & "';"
To try using code to eliminate the "duplicate records", try changing this line. Add the word in BLUE:
Code:
sSQL = "SELECT DISTINCT tblSchChg.RenDate, tblSchChg.Cust, tblSchChg.cust_id, tblSchChg.bus_name,"
sSQL = sSQL & " tblSchChg.address1, tblSchChg.bus_name2, tblSchChg.address2,"
sSQL = sSQL & " tblSchChg.address3, tblSchChg.city, tblSchChg.ST, tblSchChg.contact, tblSchChg.zip,"
sSQL = sSQL & " tblSchChg.fax, tblSchChg.ID, tblSchChg.phone, tblSchChg.st_id,"
sSQL = sSQL & " tblSchChg.st_id, tblSchChg.cust_code, tblSchChg.Current_Boss, tblSchChg.RealRATE,"
sSQL = sSQL & " tblSchChg.Rate2, tblSchChg.Rate3, tblSchChg.Rate4, tblSchChg.Rate5, tblSchChg.Rate6,"
sSQL = sSQL & " tblSchChg.Rate7, tblSchChg.Rate8, tblSchChg.Rate9, tblSchChg.Rate10, tblSchChg.Rate11,"
sSQL = sSQL & " tblSchChg.Current_RealRATE, tblSchChg.CurrentRate2, tblSchChg.CurrentRate3, tblSchChg.CurrentRate4,"
sSQL = sSQL & " tblSchChg.CurrentRate5, tblSchChg.CurrentRate6, tblSchChg.CurrentRate7,"
sSQL = sSQL & " tblSchChg.CurrentRate8, tblSchChg.CurrentRate9, tblSchChg.CurrentRate10, tblSchChg.CurrentRate11"
sSQL = sSQL & " FROM tblSchChg"
sSQL = sSQL & " WHERE tblSchChg.Cust = '" & s!Cust & "';"
I would create a query without the DISTINCT keyword to get a record count of all records, then add the DISTINCT keyword. Compare the record counts of the two queries. Then look at the 2nd query to see if the correct (duplicate) records are/were not included....
(The Criteria, "Cust" or "cust_code", MUST be the same)
And if you would rather that I make the changes, let me know..........