Update multiple table single column from main table column (1 Viewer)

akk303

New member
Joined
May 26, 2024
Messages
3
Hello
I am new in the Access world. I am trying to learn about database workouts. I have a database. The database contains 7 tables (e.g. Q1-Q7). Each table has a common column which is "Roll number". So, I make a relationship between Q1 table to the other 6 tables (e.g. Q1 and Q2, Q1 and Q3, etc.). Now I want, when I enter the roll number in the Q1 table, the other table roll number should be updated. How do I complete this task? I am looking forward to a solution/suggestion/advice/direction to accomplish this task. Thanks and best regards to all.
 
Sounds like you have an unnormalised database? :(
But I will load the bullets for you.
Run an append query when you save the roll number in Q1.
 
Last edited:
You should have just one table for whatever you store with a date field from which you can deduce the quarter.
 
Data shouldn't be updated in a relational database. Data should be related. Then thru those relationships the data you need can be obtained without further action.

Also, it sounds like you haven't set up your tables correctly. The process of setting up fields and tables in a database is called normalization:


I suggest you give that a read, find a few tutorials and practice, apply what you have learned to your database and then post your database here so we can help you get the tables correct before you move on
 
First let's discuss the 2 minor issues that are really bugging me:

1. Poor names/notes. Someone should be able to open a table, look at its names and its field names and have some sort of idea what it is for. 'Q1', '1(a), '1(b)' and even 'Roll number' does nothing for me. I have no idea what I am working with so can only apply the generalist of database normalization rules to help you. I can't offer any topic specific ideas because I have no idea what the topic is.

2. Bad characters in names. You should only use alphanumeric characters and underscores in names. When you use other characters (parenthesis, spaces) it makes, coding and querying later on a little more difficult. Do yourself a favor and don't use them.

Now, the 2 big mistakes which are essentially the same--storing data in names. One at the table level and one at the field level. :

3. Tables with the exact same structures shouldn't exist. You essentially copied table Q1, pasted it and replaced all the 1's with 2's. That's wrong If you feel the need to copy a table and just rename everything--don't, just add an additional field. Instead of 8 Q tables, you only need one. Let's make a copy of Q1 and call it tableQ, add a field, this would be its structure:

tableQ
RollNumber - existing field
Q_Number - will hold the number that suffixed the table name and prefixed each field
A - existing field renamed without number
B - existing field renamed without number
C - existing field renamed without number
D - existing field renamed without number
E - existing field renamed without number

That 1 table can now hold all the data from all your existing Q tables. Instead suffixing all those Q tables with a number, you would put that number in the Q_Number field and that would let you know what that record is for. However...

4. Don't prefix/suffix fields with numbers/letters. All those lettered fields need to go away as well. This is what tableQ should actually look like:

tableQ
RollNumber - existing field
QNumber - will hold that number formerly suffixed to the table name
QLetter - Will hold A, B, C, D, E
QValue - will hold the actual value that was once in the fields like 1(a), 1(b), 2(d), etc.

That's it. Instead of 8 tables with 6 fields each, that 1 table with just 4 fields now can hold all your data. Instead of 1 record in table Q1 you now have 5 records in tableQ. That's normalization. Better yet, reduces your queries and forms and most likely your yet to be built reports.
 
I think it would be helpful to you and readers if you could step back and provide a 30,000 ft overview of the 'business process(es)' you are trying to automate/support with a database. Use simple plain English.
 
I think it would be helpful to you and readers if you could step back and provide a 30,000 ft overview of the 'business process(es)' you are trying to automate/support with a database. Use simple plain English.
I am trying to learn access database. One more thing I do not run a business. I am a University Professor.
 
Last edited:
akk303,
My point on 'business process(es)' was intended for you to provide an overview description of WHAT you are trying to automate/support with a database. I can remove the term business from the post and emphasize the what is the subject matter and what are the relevant steps involved?

For example, your situation may be something along these lines:

- I am doing an analysis archaeological sites from X, Y, Z locations,
- I am revising the curriculum for my post graduate program(s),
- I am studying the smoking habits ( hobbies, job expectations...) of undergraduate students,

Bottom line is you are dealing with some subject matter and you are gathering data on some aspect(s) that is of some interest. For readers to assist in a focused manner, you'll have to provide some context.

What exactly have you done to "learn Access database"?
Here is a link to a tutorial by Roger Carlson on database --identifying tables and relationships.

There are several articles on database planning and design in the link in my signature.
 
EDITED by The_Doc_Man: I see after reviewing the thread that you posted a database showing more details than appeared in the original question. Then you retracted the post. This makes it difficult to stay on target.

The biggest problem I see here from a technical viewpoint is ambiguity, which is one of the side effects of your particular form of denormalization. Here is what I understand from what you told us:

You have 7 tables Q1-Q7. They have one field in common, Roll Number. Relationships exist using that common field found in Q1 and each of the six other (Q2-Q7) tables. Your goal is that if you update some field in some record in Q1, you want a corresponding field in a particular record (as identified by Roll Number) in one of the other Qx tables to be updated.

The implication is that only one of the six "side" tables should be updated. It is important to know if it can ever occur that more than one of the Q2-Q7 tables match up against the record in Q1 at the same time? I.e. multi-table matches potentially leading to multiple updates among Q2-Q7 for a single update of Q1? Stated another way, is this Q1-Qx match unique across the six other tables?

To achieve your intended goal, you need to know which of the six other tables has a matching record to the Q1 table from which you selected the given record to update. You would have to identify the correct table first.

Your problem, as I stated earlier, is ambiguity. The reason is that your original description says or implies that you link records in Q2-Q7 via only one column. For a properly normalized table relationship, there can be no ambiguity as to which table is being matched. Access would require that if your match-up is unique (i.e. only one possible matching record from only one of the six tables) then you would need a second field to identify which table holds that match because Access will not find that for you. Actually, though, your situation is worse than that.

A central part of designing a database is to identify the entities you are tracking, which can include occurrences or non-physical events as well as more physical objects. Identifying the entities must include knowing their similarities and differences, and must include identifying their properties or attributes. And here is where your design comes to grief.

Ignoring the choice in names for just a moment, LOGICALLY your description says that Q2-Q7 all represent the same thing but with a difference of one attribute, because all six of those tables are related to a single thing in another table and that match is identified as a Roll Number. That common relation means that Q2-Q7 have something in common.

In Access, things that represent the same thing go in the same table, and their difference is recorded as an attribute number. So you should not have seven tables. At most, you should have two tables and that differentiating attribute (that was implied by choosing Q2 or Q3 or ... or Q7) as a field. In the simplest case, you would need a "matching table index" (ranging from 2 to 7) in Q1. And until I know more about what you are actually trying to do, I'm not even going to swear you needed Q1.

You can best help us to help you by giving us an overview of what you are doing. I know we are going to need to give you structural help, but without knowing the base problem, it will be difficult or impossible to give you more directed help.
 
I am trying to learn access database. One more thing I do not run a business. I am a University Professor.
Welcome to AWF. We'd love to help but as the others have mentioned, we have no context. It also helps us if we have some understanding of your knowledge of Access. If you have no experience with relational tables, your first project can be a challenge. It seems that you have gotten off on the wrong foot with your initial schema design. As plog mentioned in detail, you probably only want a single table rather than 7. But since we don't know what you are trying to model, we cannot make any more definitive suggestions.

Additional potentially helpful information - Is this a project for your personal use? Are you trying to teach yourself Access so you can use it in one of your classes and teach your students? Even a personal use first project should start you on the path to a basic understanding of best practices so you can expand your knowledge base and that always starts with the schema (tables). This is your foundation. If your foundation is sound, you will be able to build a sound application that hopefully you will be able to change over time as your needs change.

The more you know about your data and the more you know about the reports, statistics, etc that you hope to develop, the easier it is to design the tables. Although the starting point of the build will always be the tables, you really need a good idea of your end goal before you even start. If you are looking for reports, then mock some up with Excel or Word and we will help you to work backward from there.
 

Users who are viewing this thread

Top Bottom