updating related tables

G'day all. I have a master data table that links with a bunch of other tables. If I add another record to the master data table, how do I force the primary key of the new record to slot into my other tables?? For example. If I enter and ID number in a master table, I'd like the new ID to appear in all related tables as well. I need to do this so that my queries will work and return matching records. Enforcing referential integrity only updates existing records. Is there some way I can do this without having to run an update query everytime I enter a new record?? I appreciate any advice.

There is a cleaner way to do this then actually addin the Blank Child Records.

In you query, you can change the porperties of the relationships themselves between the different tables (Double click on the relationship itself). There is the options that states:

Show me all records in Table1 and all records in Table2 Where they fields are equal

This is called and Right (or Left) Join in SQL code. This makes it so that all you Master table records will show up, regardless if the existing in another record.

If you still need to know how to added Blank records you will have to use ADO or DAO VBA Code to do this. Let me know and I will send you a sample DB

S-

My data base keeps records of student test scores over a number of years. Each student has an ID number which became my primary key. I kept scores in different tables for different years. Doing it this way, I found serching a little clumsy. So I threw all my data into one master table. I then had to use an auto number as a primary key because I had duplicate ID numbers. Relationships seem to only work with primary keys and the auto numbers don't really mean anything. Is there a better way to organise my data??

With Relationships, only one of the field being related back in forth between two tables needs to be unique (not neccesarily the primary)



Here is one alternative to set up your tables without using the autonumber (that you seem to not like)

Student Table
StudentID (PK)
StudentNAme

Student grades Table
StudentID (PK/FK)
StudentTestYear (PK)
TestScore


Here is an alternative with the autonumber

Student Table
StudentID (PK)
StudentNAme

Student grades Table
AutoNumber (PK)
StudentID (FK)
StudentTestYear
TestScore

Based on what you are saying here is two alrernatives. Now if I didn't undersatand you, post a copy of your DB design and I will look at it.

S-

sbaxter

Thanks for your ongoing help. Attached is a sample of a few tables. Any advice on how I can better organise my data so that I can get better performance from my data base is much appreciated. Thanks again.

I need more information on what your DB is suppose to do. What are you storing? Do you include a new record in "data GO assessments" for each year? Etc.

S-

I'd love to zip the whole thing up and send it but I'm restricted to a size limit.

Basically the database tracks student progress. If I have an interview with a parent I want to call up a student history to better manage what's the best course of action.

The data master table contains student data EQID, year, Name, class etc... In the other tables I simply append EQID and year ONLY and the queries fill the other data. Because students are at primary school for seven years, the data master table will have the same student 7 times with the same EQID but the year will be different. The same will happen in the other tables (a replica EQID and year date) I then just make up a query and match up the EQID and year. periodically I update the tables with new students. Is this the best way to do this?? Thanks for your help.

Goto Yahoo Breifcase and get your self a Free FTP site. IT will let you FTP up to 5Meg files.

The send me a Private message telling me the username and password and I will doenload your DB

S-

you may need the security file as well. here's the data base.
http://www.schools.ash.org.au/macgregorps/kid tracking new version.mdb

The page cannot be found

when I use your link

S-

http://www.schools.ash.org.au/macgregorps/kid tracking new version.mdb

copy the whole line into your web address bar