Merging duplicate value

[calvinle]

Hi,

I have data in a tblTemp imported from Excel, and I will need to merge them base on 2 field. If they match, then merge some other field together.

Right now, I am adding the first record from tblTemp to tblData, then compare the 2nd record from tblTemp to tblData, then if the 2 field are same, then update that record in tblData, otherwise, add that as new record to tblData. Then it will loop the next data in tblTemp.

That way works, but its taking a while becuz it compares every single record from tblTemp to tblData. I have about 10k record which takes about 5mins.

I Wonder if there is any other easier way? I guess concatenate?

Thanks

 

[plog]

High level concepts of what you want to do with your data are a good beginning point, but to really understand your issue examples are gold.

Can you provide 2 sets of data?

A - starting sample data from your tables. Include table and field names and enough sample data to cover all cases.

B - expected results. What you expect to end with when you perform your process on the data in A.

 

[James Deckert]

Sounds like you can make an append/update query.
you'll create a query with tblTemp and tblData with a left join (all records from tblTemp), then make it an update query. This will add records if not existing, and update fields for records already existing. If this won't work because you want different fields updated if it's new than if already existing, then you could make two queries, and update and an append query and run through the data twice. This will be much faster than what you are doing.

 

[calvinle]

High level concepts of what you want to do with your data are a good beginning point, but to really understand your issue examples are gold.

Can you provide 2 sets of data?

A - starting sample data from your tables. Include table and field names and enough sample data to cover all cases.

B - expected results. What you expect to end with when you perform your process on the data in A.

Every month, I have a new reports coming out, then I will need to compare those record on the new report to the old one and if there are new, then add, if old, then just replace existing data. So it's a monthly process to compare new record to old record.
rs = tblTemp
rs1 = tblData
A/ When an excel file is imported to Access, it will be paste in tblTemp without PK.
There are many field, so I will just cut to the one I need to comparison:
If [CLIENT] & [MEMBER_ID] from new record is same as old record, then
update new record to old record.

B/The result will be in tblData, same order but with a PK:
[PROD][CLIENT][MEMBER_ID]

The comparison is between the field [CLIENT][MEMBER_ID].
When adding, it will add to tblData:[PROD][CLIENT][MEMBER_ID]
When updating, it will update to tblData: [CLIENT][MEMBER_ID] but for the field [PROD], it will do:

 If Instr(rs1![PROD],rs![PROD]) = 0 Then rs1![PROD] = rs1![PROD] & "," & rs![PROD]

Sample of tblTemp data
TSHIRT - C2D - 012345
SHORT - C2D - 012344
TSHIRT - C2E - 012887
SHORT- C2D - 012345
SHORT- C2D - 012345

Sample of tblData result:
TSHIRT, SHORT - C2D - 012345
SHORT - C2D - 012344
TSHIRT - C2E - 012887

Thanks

 

[plog]

C-. You only provided one starting table and neither contained field names and I see lots of cases unexplained.

What happens if tblData already contains a SHORT - CSD - 012344 record? Does another get added? Do you increment a count field instead?

Suppose tblTemp has the data you posted and tblData has a TSHIRT - C2D - 012345 record? Do you add your TSHIRT, SHORT - C2D -012345 record as well? Or do you delete the existing record and add the TSHIRT, SHORT field?

Suppose tblTemp has the data you posted and tblData has a PANTS, TSHIRT, SHORT, HAT - C2D - 012345 record? Do you add your TSHIRT, SHORT - C2D -012345 record as well? Or do you not add it because its already included in the existing record?

 

[calvinle]

What happens if tblData already contains a SHORT - CSD - 012344 record? Does another get added? Do you increment a count field instead?

If the tblData already have
SHORT - CSD - 012344, and the tblTemp has :
SHORT - CSD - 012344
Then it will update the tblData from the tblTemp, and while updating, if the SHORT is already there in tblData, then it will not do anything to that SHORT field which is [PROD]

Suppose tblTemp has the data you posted and tblData has a TSHIRT - C2D - 012345 record? Do you add your TSHIRT, SHORT - C2D -012345 record as well? Or do you delete the existing record and add the TSHIRT, SHORT field?

I don't want to delete anything. It's either, Update or Add New. (The work comes from tblTemp (imported from excel) to tblData.

Suppose tblTemp has the data you posted and tblData has a PANTS, TSHIRT, SHORT, HAT - C2D - 012345 record? Do you add your TSHIRT, SHORT - C2D -012345 record as well? Or do you not add it because its already included in the existing record?

The data from tblData is cumulated for [PROD] field. and the data from tblTemp is always 1 items only in [PROD] field.

Here is my code that I am using right now, so you can have a better idea:

removed

 

[plog]

If the tblData already have
SHORT - CSD - 012344, and the tblTemp has :
SHORT - CSD - 012344
Then it will update the tblData from the tblTemp, and while updating, if the SHORT is already there in tblData, then it will not do anything to that SHORT field which is [PROD]

That makes no sense. What's there to update? Its the same data. And you didn't address my 2 other cases.


I don't want any explanations or code. I want data you to demonstrate what you are starting with and what you want to end with. So, just follow my instructions in my first post.

 

[calvinle]

Okai,I just want to make sure that you understand the data come from tblTemp to tblData. Then:

What happens if tblData already contains a SHORT - CSD - 012344 record? Does another get added? Do you increment a count field instead?

If the data already exist in tblData, then it will update to the same value as tblTemp. Reason for so, because the tblTemp will have new monthly data which has a field [BALANCE] in it, however, I just want to keep this concept simple, so I did not include this field in discussion. So, just let's say, if the record exist after comparing CLIENT and MEMBER_ID, it will override the new to existing data. That way, it will keep the PK and not create a new PK.

Suppose tblTemp has the data you posted and tblData has a TSHIRT - C2D - 012345 record? Do you add your TSHIRT, SHORT - C2D -012345 record as well? Or do you delete the existing record and add the TSHIRT, SHORT field?

If in the tblData already has:
TSHIRT, SHORT - C2D -012345

and the new data from tblTemp is:
TSHIRT - C2D - 012345

then the data in tblData will stay the same because the first comparison was CLIENT & MEMBER ID, which C2D & 012345 from tblTemp match with tblData, now do we update or add? The next comparison will be the [PROD]. If in tblData is TSHIRT, SHORT, then I will not add SHORT again to the data.

Suppose tblTemp has the data you posted and tblData has a PANTS, TSHIRT, SHORT, HAT - C2D - 012345 record? Do you add your TSHIRT, SHORT - C2D -012345 record as well? Or do you not add it because its already included in the existing record?

First, where do you get the TSHIRT, SHORT - C2D -012345? The data coming from tblTemp is always:
1 item - CLIENT - PROD.
There is no way that the comparison be:
MULTIPLE ITEM - CLIENT - MEMBERID
vs
MULTIPLE ITEM - CLIENT - MEMBERID.

The comparison is always:
SINGLE ITEM - CLIENT - MEMBERID from tblTemp
to
RECORD IN tblData.

Hope I answered your question.

 

[plog]

I was really close to yelling at you for yet again not providing me with data, when it occured to me I get to yell for something even more enjoyable--an improperly structured table.

You need to get away from the structure you have for tblData and have it be the same structure as tblTemp. You shouldn't keep jamming items into one field if you need to check that field for specific items.

tblData should not have multiple items listed in one field, it should have multiple records for those multiple items. Then when you need to see if a new item is in tblTemp its a simple LEFT JOIN between the two to find new items. Then when those are identified, its a simple APPEND. No updating, no parsing a field, just a subquery (LEFT JOIN) and an APPEND.

Your structure is working against you, you need to properly store your data. I know what you are thinking and let me cut you off--I don't care how you report on your data or the format necessary for that reporting. That has nothing to do with how you should store your data. That format can be accomplished with a properly structured database--you can still have all similar items appear comma delmited when you output your data. I believe there's some Allen Brown floating around on this forum that does just that.

 

[calvinle]

I get what you mean, however, in the tblTemp which are data coming from an excel, they have 1 rows for each items regardless the members. If the member has 10 items, then it will create 10 rows. In Access, I will need to manage per user regardless their items. So I will need 1 user per record, thats it!
If I just import from tblTemp to tblData of 5 record for 1 member because they have 5 différents items, then when I add a subform and search for that member, it will end up showing 5 different record.
Later on that project, I will need to generate a letter for each member including all their product. That is why concetenate the product in a single record will help me from generating the letter for each member too.

Thanks.

 

[calvinle]

I guess nevermind. I just went through concatenate function from Allen Browne, and the speed of the process is not much more faster than the comparing code that I am doing.

Thanks.