Update Query does duplicates (1 Viewer)

Gismo

Registered User.
Local time
Today, 20:39
Joined
Jun 12, 2017
Messages
1,298
Hi All, please could you assist

I have 2 tables , a master and a history tbl
I want to update a few fields in the history tbl from the master tbl
lets say i have 2 of the same records in the master tbl but the frequency is different. I have the same 2 records in the history file.
when I do a normal select query i get 4 record, when I change to update query, it updates to the same frequency and the other frequency has not been updated.
what am i doing wrong here? am i missing something?

Select query shows the correct records when i use the total (summary) button but on update query the total button is not available.
 

plog

Banishment Pending
Local time
Today, 13:39
Joined
May 11, 2011
Messages
11,611
Can you provide code and/or data examples? Posting a database would be best. My guess is you are improperly linking the two.
 

isladogs

MVP / VIP
Local time
Today, 18:39
Joined
Jan 14, 2017
Messages
18,186
Have you tried setting Unique Records =Yes. In SQL this is DISTINCTROW
 

Gismo

Registered User.
Local time
Today, 20:39
Joined
Jun 12, 2017
Messages
1,298
Hi,

I changed the unique record to yes.
When I look at one specific record, the result gives me 2 records which is correct.
In my master file I have 2 entries, exactly the same data in all fields except for the frequency field which is different.

the problem now is that the update query updates to one of the frequencies for both records.

Capture.PNG
Capture1.PNG Frequency should be 100 and 800

Code:
UPDATE DISTINCTROW [SB/AD Master] INNER JOIN [SB-AD History] ON ([SB/AD Master].Category = [SB-AD History].Category) AND ([SB/AD Master].Series = [SB-AD History].Series) AND ([SB/AD Master].[Aircraft Type] = [SB-AD History].Type) AND ([SB/AD Master].[SB/AD No] = [SB-AD History].[SB/AD No]) SET [SB-AD History].Status = [SB/AD Master]![Code / Status], [SB-AD History].[Release Date] = [SB/AD Master]![Release Date], [SB-AD History].[Revision Status] = [SB/AD Master]![Revision Status], [SB-AD History].Description = [SB/AD Master]![Description], [SB-AD History].Frequency = [SB/AD Master]![Repetition Hours], [SB-AD History].[SB Attachment] = [SB/AD Master]![SB Attachment]
WHERE ((([SB/AD Master].[SB/AD No])="292 72 2861") AND (([SB-AD History].[Aircraft Registration])="zs-hxb") AND (([SB-AD History].Category)="Engines"));
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 18:39
Joined
Jan 14, 2017
Messages
18,186
Both tables need a primary key field for the update key to work reliably.
Ideally join the tables using the PK

In fact ALL tables should have a PK
 

Gismo

Registered User.
Local time
Today, 20:39
Joined
Jun 12, 2017
Messages
1,298
I initially used the SB.AD No as the PK, but later on noticed that I have one SB/AD No with different variants in other fields. so at this stage I do not have any PK's in any of the 2 tables. So One SB/AD no in the master file could apply to anything from 1 to 6 different variables.
The other problem now is that I have over 5000 records in my master file and over 40 000 records in my history file.
Not sure how to correct this now.
 

isladogs

MVP / VIP
Local time
Today, 18:39
Joined
Jan 14, 2017
Messages
18,186
Is that field unique in the master table. If so reinstate it as PK.

Could you make a composite PK in the History using that field and frequency?
Or add an autonumber PK field and use a combined index using AdNo and frequency?

Also it is a bad idea to use special characters like '/', '-' or spaces in table and field names
 

Gismo

Registered User.
Local time
Today, 20:39
Joined
Jun 12, 2017
Messages
1,298
yeh, I realized the special character i bit too late, can not change it now as I have just to many queries and tables relating to the SB/AD No field.

The SB/AD No is not unique in the Master file, SB/AD No 001 could relate to different types, different frequency and different codes and different revisions.

I will have to take the long route here, I will recalculate the index field in my master file, then update it to the history file and then I will manually have to edit the fields with multiple records in the history file. I dont think there will be an easier method.
 

isladogs

MVP / VIP
Local time
Today, 18:39
Joined
Jan 14, 2017
Messages
18,186
I suspect doing it the hard way is the only solution for you now.

Whilst it would take time to fix table and field names, Access does have tools to assist with doing so.
First make a backup. This is ESSENTIAL
Then use object dependencies to check all items that depend on the master table.
Switch on Name Autocorrect in Access Options. Change the name of your master table. Now look at each query in turn. All should have updated. Do the same with each field in turn. Note all changes.
Now go through your code making the same changes
Backup again.
Repeat with the other table(s) in turn.
Backup again.
You may wish to disable Name Autocorrect at the end as some people say it leads to corruption. However I have never had an problems of that sort.

A slightly easier approach is to use a free add in called V-Tools which includes a deep search and replace feature. That works on everything except macros.
I have my own code that also works on macros. I intend to release that in the near future though there will be a small cost.
 

Users who are viewing this thread

Top Bottom