473,321 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

How to fill different table`s field automatically from a single source field

132 100+
Hi @1!
I have a single DETAILS table from which I want to enter data including FirstNames and Surnames, But this surname field is also included in all ten other tables, I want all these tables filled automatically whenever data is entered in the DETAILS table. The Surnames should be valid only once in all tables to be filled. I have tried to ENFORCE INTEGRITY but it coudnt work. I then tried to creat LOOKUP column but it gives me very longlist to choose from, which donot simplify my job as the list elongates.
Please help me on this!
Thanks in advance
Sep 27 '10 #1
8 1787
MMcCarthy
14,534 Expert Mod 8TB
Hi Mr Key

Databases aren't meant to work like this. The surname field should only be in one table. Check out this insight for a better understanding of the way databases work.

Database Normalization and Table Structures
Sep 27 '10 #2
Mr Key
132 100+
Thanks sir for your imeadiate response,
But I have to have it like this, What you have just recomended is very good article thanks once again.
This database is intended for college use,
The teachers in a certain college wants their database of entering marks and other records to be like that,
Each Tabble/form represent a certain subject, each of which should display the respective Surnames for the EXAMINER to enter a correct score for correct student. Think of Ordinary user that donot know even how to copy data from one table to another, They just want a space to enter names once only and then uses those names to enter the respective scores for each subject separately.
Sep 27 '10 #3
MMcCarthy
14,534 Expert Mod 8TB
This would be an integrity nightmare. Can't you do the same thing with an updateable query.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblStudent.StudentID, tblStudent.Surname, tblMarks.Score FROM tblStudent LEFT JOIN tblMarks
  2. ON tblStudent.StudentID=tblMarks.StudentID
This would be based on the following table structure.

tblStudent
StudentID (PK)
FirstName
Surname

tblMarks
MarksID (PK autonumber)
StudentID
Score

Another way of doing it would be to have a form based on the student and a subform for marks.

There is a reason what you want to do is not recommended the integrity of trying to keep the names right would be an absolute nightmare.

The trick is to give the teachers what they want visually but structurally you don't.
Sep 27 '10 #4
Mr Key
132 100+
Thanks alot MMcarthy, it gives me the hint of what and how am I supposed to do. The problem is, the scores are different for each other and has to appear on its own table while Surnames has to be there as I have suggested before.
Sep 27 '10 #5
NeoPa
32,556 Expert Mod 16PB
Mr Key,

There is nothing in the setup that has been suggested which precludes your doing what you require. In fact, it is the only sensible way to attempt such a task. You are lucky that MMcCarthy has given this advice as the other approach would have been a disaster from start to finish.

I would suggest you have a little dig around on the web and find something that covers the basics of databases, as without this you are likely to struggle with this task - even with help.

Best of luck with your endeavours and welcome to Bytes!
Sep 27 '10 #6
Mr Key
132 100+
Thanks Neopa!
Any further help will be approciated
Sep 28 '10 #7
Mr Key
132 100+
Hi!
Due to your suggestions I have come with somehow new idea on this,
I have created a form(Modules) with Sub forms (Students&Score) in which I could at least enter data on a Module form and Students Names and Grades at the same time. I have to have the Subforms(ie LastName & Examination Number) Records to remain there so that I couldn't have to enter it again and again.
See the attachment below for clarifications.
In this attachment if I navigate to NEXT records, the whole form comes to New records, I want some to remain there constants Unless I decided purposely to do so.
I searched around the webs and come across with students records db that can be used in any college but it couldn't meet my design criteria. Most of them suggesting entering a data per Student and not per subject/Module.
Please help!
Attached Files
File Type: zip AotomatesFieldinputs.zip (94.0 KB, 85 views)
Sep 28 '10 #8
NeoPa
32,556 Expert Mod 16PB
I can see that you're not comfortable with the advice given so far Mr Key. My belief is that if you had followed my advice and learned more about the very basics, you would be in a better position to appreciate the wisdom of what has been suggested. The fact that you are still looking elsewhere for alternative answers tells me that I cannot help you further. Of course it is your choice whether or not to pay attention to what we suggest, but as you appear to have chosen not to, then I am not going to advise you on how to take an approach that I feel is wrong for you.

Good luck with your project.
Oct 1 '10 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Will | last post by:
Hi, I have a form which calculates a final cost (frmWorkCosts, data stored in tblWorkCosts) and once calculated adds this final cost (Which isn't saved in tblWorkCosts) into a different table...
2
by: MLH | last post by:
I feel pretty lucky on my last SQL question. I think I'll try one more... If there is a table (tblCorrespondence) with a field in it named and I am extracting a dynaset of records having an ...
2
by: Vipul Pathak | last post by:
Hi everyone, I have a .NET 1.1 application ready to be ported on .NET 2.0. With some of the features made obsolete in 2.0 and the new recomended methods are not available in .NET 1.1, It...
10
by: RoadRunner | last post by:
Hi, I have a employee vacation database that has a vacation table that has the employee name, pay week and date of vacation. I have another lookup table with pay week code and date range for the...
1
by: alexsg | last post by:
A little while ago Ron P kindly responded to my inquiry on how to copy the first line of memo field into a text field, using a query: left(,instr(1,,vbCrLf)-1) This is great, but I would like...
1
by: Paul Glavey | last post by:
Hello anyone any idea how to do this. The problem I am having has to do with Continuous Forms. In the header of the form I have 4 Combo boxes. For now lets just work on one combo box. The combo...
5
by: DaveLock | last post by:
Hi, I have 3 tables of data created from different sources, each with the same 2 fields & I wanted to fill the 2nd field of another table with the data on condition. The condition is because...
2
by: robin1983 | last post by:
HI, I have a problem with sql query.. Actually, i have three different table eg. table1, table2, and table3. All the three table have same field name. Actually, my problem is that, whenever I insert...
1
by: sillyr | last post by:
Hi I wanted to make a statement in a form to take a value from one field if the first field has no value. I have a form created from two tables. Each table has a field called Haul number. On the...
1
mb60
by: mb60 | last post by:
I have two columns in my access table -FIELD1 and FIELD2. Field1 has the names of some animals. If I enter a name BIRD in Field 1, automatically a prescribed number , for example '2' should be...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.