By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,081 Members | 2,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,081 IT Pros & Developers. It's quick & easy.

Table Relationship and Form Problem

P: 83
I have 4 tables that are linked by a unique indexed case number. I want to have a form that allows you to create a new case and enter information to one of the tables, but still create a new case number in the other 3 tables.
I have tried creating a relationship-one to -one between the one table and the rest (Case Number to Case Number. )
I then created a query that brings them all together, but there are 4 different case numbers but I used one of them. but it doesn't create a new case in all the tables.?
Jun 8 '07 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
I have 4 tables that are linked by a unique indexed case number. I want to have a form that allows you to create a new case and enter information to one of the tables, but still create a new case number in the other 3 tables.
I have tried creating a relationship-one to -one between the one table and the rest (Case Number to Case Number. )
I then created a query that brings them all together, but there are 4 different case numbers but I used one of them. but it doesn't create a new case in all the tables.?
I believe that In order for the case number to be generated in each of the related tables, you need to do the following:
:
1. set the table relationships for all tables such that referential integrity is enforced (checked) and cascade updates and deletes are checked.
2. in the relationship window, the case number field in one of the tables has to be designated as the Primary Table to each of the other 3 tables (the so called foreign key tables). This also implies that the structure and makeup of the case number in the primary and foreign tables has to be the same.
Jun 10 '07 #2

FishVal
Expert 2.5K+
P: 2,653
I have 4 tables that are linked by a unique indexed case number. I want to have a form that allows you to create a new case and enter information to one of the tables, but still create a new case number in the other 3 tables.
I have tried creating a relationship-one to -one between the one table and the rest (Case Number to Case Number. )
I then created a query that brings them all together, but there are 4 different case numbers but I used one of them. but it doesn't create a new case in all the tables.?

???

"Relationship one-to-one". What does it means? Case Number is unique in each table? If so, why do you need 4 tables?

Please clarify.
Jun 10 '07 #3

P: 83
I need 4 tables because there are too many fields for one table

???

"Relationship one-to-one". What does it means? Case Number is unique in each table? If so, why do you need 4 tables?

Please clarify.
Jun 11 '07 #4

FishVal
Expert 2.5K+
P: 2,653
I need 4 tables because there are too many fields for one table
Sorry. It seems to me your db is not normalized.
Is it so?
By the way how do you want to represent >255 fields (1000 ?? in 4 tables to keep within access limits :)) on a form?
Jun 11 '07 #5

Post your reply

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