How to enter information into similar fields in different tables

[abmo72]

Hello friends
In Access, I designed several tables, some of which have similar fields in some tables, and I designed a form for each of them separately.
The question is
How to enter information into similar fields in different tables
For example, in several tables I have a field called project title
I want this field to be automatically replaced in the other five tables when it enters form number one

 

[welshgasman]

Simple answer, you could use an update query.
Possibly correct answer, is that you should just hold a link to the project. Then when you change the title, no change needed.?

You would do the same for a person. If they change their surname, you change it in one place only. By using the surrogate keys, nothing else needs to be done.
Really sounds like you need to research normalization

 

[JonXL]

As welshgasman says, look up database normalization. The structure of your DB needs to change. Instead of storing the project title in every table, store it in just one. In the other tables, create a field to reference the autonumber ID field from the projects table (where the project title properly belongs).

Whenever you want to see your other info alongside the related projects' titles, you run a query selecting the project title field from the projects table and joining the project ID to the related field in your other table. In this way, the project title is only ever changed in one place and then its updated value appears automatically anywhere else you want it... on the fly.

 

[welshgasman]

See the links posted by @Micron in post 5 of this thread.
Converting existing Excel Project Database over to Access

 

[abmo72]

Simple answer, you could use an update query.
Possibly correct answer, is that you should just hold a link to the project. Then when you change the title, no change needed.?

You would do the same for a person. If they change their surname, you change it in one place only. By using the surrogate keys, nothing else needs to be done.
Really sounds like you need to research normalization

Thank you for your suggestion. I solved the problem with append query, but I had another problem


INSERT INTO table2 ( [first name], [last name], [book number], [book title] )
SELECT DISTINCT [first name], [last name]], [book number], [book title]
FROM table1
WHERE NOT EXISTS (SELECT * FROM table2
WHERE table2.[first name] = table1.[first name]
AND table2.[last name]] = table1.[last name]]
AND table2.[book number] = table1.[book number]
AND table2.[book title] = table1.[book title] );


The problem is solved when I enter the SQL code and a new record is added to the table, but when I want to add a field, the code works fine.

INSERT INTO table4 ( [address user] )
SELECT DISTINCT table2.[address user]
FROM table2 INNER JOIN table4 ON table2.[address user] = table4.[address user]
WHERE (((Exists (SELECT * FROM table4
WHERE table4.[address user] = table2.[address user]))=False));


And I do not know what the problem is?

 

[abmo72]

Simple answer, you could use an update query.
Possibly correct answer, is that you should just hold a link to the project. Then when you change the title, no change needed.?

You would do the same for a person. If they change their surname, you change it in one place only. By using the surrogate keys, nothing else needs to be done.
Really sounds like you need to research normalization

There is a way to prevent this window from being displayed
This image is related to the first code string
That append works well, but I do not want this warning to be shown by the query implementation

 

[abmo72]

As welshgasman says, look up database normalization. The structure of your DB needs to change. Instead of storing the project title in every table, store it in just one. In the other tables, create a field to reference the autonumber ID field from the projects table (where the project title properly belongs).

Whenever you want to see your other info alongside the related projects' titles, you run a query selecting the project title field from the projects table and joining the project ID to the related field in your other table. In this way, the project title is only ever changed in one place and then its updated value appears automatically anywhere else you want it... on the fly.

thank you The link was very useful

 

[abmo72]

Thank you for your suggestion. I solved the problem with append query, but I had another problem


INSERT INTO table2 ( [first name], [last name], [book number], [book title] )
SELECT DISTINCT [first name], [last name]], [book number], [book title]
FROM table1
WHERE NOT EXISTS (SELECT * FROM table2
WHERE table2.[first name] = table1.[first name]
AND table2.[last name]] = table1.[last name]]
AND table2.[book number] = table1.[book number]
AND table2.[book title] = table1.[book title] );


The problem is solved when I enter the SQL code and a new record is added to the table, but when I want to add a field, the code works fine.

INSERT INTO table4 ( [address user] )
SELECT DISTINCT table2.[address user]
FROM table2 INNER JOIN table4 ON table2.[address user] = table4.[address user]
WHERE (((Exists (SELECT * FROM table4
WHERE table4.[address user] = table2.[address user]))=False));


And I do not know what the problem is?

Sorry I wrote it wrong
To add a field only the code does not work well
means this cods
INSERT INTO table4 ( [address user] )
SELECT DISTINCT table2.[address user]
FROM table2 INNER JOIN table4 ON table2.[address user] = table4.[address user]
WHERE (((Exists (SELECT * FROM table4
WHERE table4.[address user] = table2.[address user]))=False));

 

[Micron]

Sorry that I can't make sense of that sql but I do understand the message. An update can fail for one or multiple reasons, and they're listed there along with the number of records that can't be updated for each reason. Key violations means that you are trying to add the same value in a field where all the records must be unique. Often that is the result of trying to include a field such as an autonumber primary key field in an update, and one of those numbers is already in the target table. In your case, probably not hard to find the culprit as there's only one. Might also make eac select portions a select query on its own, run it, and see if you can id the problem record/field. Or perhaps it is this portion
WHERE (((Exists (SELECT * FROM table4

as it may be that if it's already in table4, you're not able to add it again because the field is indexed as no dupes allowed.