472,968 Members | 1,507 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Table design question

This may be an easy question, but for some reason the multiple table design
idea is throwing me.

I'm trying to avoid using one large, wide table - so I've got multiple
tables that hold different categories of information about each record -
address information in one table, contact information in another table,
order-related information in a third, and so on.

The "Customer" table has an AutoNumber "Cust_ID" as a PK, which is the main
ID used throughout the database. Each ancillary table has it's own
AutoNumber ID field (e.g. Address_ID) as a PK, with the main Cust_ID as a
foreign key. I believe this is pretty standard normalization. I have
one-to-many relationships with referential integrity and cascading deletes
enabled on all these tables (the "One" side being Cust_ID in the Customer
form).

The problem I'm having is getting data into the tables. I have a
"NewRecord" popup form that is used for data entry. The user types in all
the different information in this one popup form and clicks OK - and the
data is supposed to find it's way to the different tables - address info to
the address table, name and company to the Contact table, etc.

I assume I need to use a query for the recordsource NewRecord Form. I tried
using subforms in the NewRecord form with each subform bound to it's
respective table, but that seemed like a kludge. But can I join 3 or 4
tables as a recordsource for a data entry form? The only purpose of the
NewRecord form is data entry - it opens on a new record, and closes with the
OK button. I got this to work with 2 tables, but not with more that that.
The problem is populating the other tables with the Cust_ID foreign key.
For example, when I enter a new record, the Customer table AutoNumbers that
new record as, say, 981 - how do I get that into the Cust_ID (foreign key)
field of the other tables? Can I write the foreign key to multiple tables
when using a query as a recordsource?

Thanks in advance.
Nov 12 '05 #1
2 2131
"deko" <de**@hotmail.com> wrote in message
news:fP*****************@newssvr27.news.prodigy.co m...
This may be an easy question, but for some reason the multiple table design idea is throwing me.

I'm trying to avoid using one large, wide table - so I've got multiple
tables that hold different categories of information about each record -
address information in one table, contact information in another table,
order-related information in a third, and so on.

The "Customer" table has an AutoNumber "Cust_ID" as a PK, which is the main ID used throughout the database. Each ancillary table has it's own
AutoNumber ID field (e.g. Address_ID) as a PK, with the main Cust_ID as a
foreign key. I believe this is pretty standard normalization. I have
one-to-many relationships with referential integrity and cascading deletes
enabled on all these tables (the "One" side being Cust_ID in the Customer
form).

The problem I'm having is getting data into the tables. I have a
"NewRecord" popup form that is used for data entry. The user types in all
the different information in this one popup form and clicks OK - and the
data is supposed to find it's way to the different tables - address info to the address table, name and company to the Contact table, etc.

I assume I need to use a query for the recordsource NewRecord Form. I tried using subforms in the NewRecord form with each subform bound to it's
respective table, but that seemed like a kludge. But can I join 3 or 4
tables as a recordsource for a data entry form? The only purpose of the
NewRecord form is data entry - it opens on a new record, and closes with the OK button. I got this to work with 2 tables, but not with more that that.
The problem is populating the other tables with the Cust_ID foreign key.
For example, when I enter a new record, the Customer table AutoNumbers that new record as, say, 981 - how do I get that into the Cust_ID (foreign key)
field of the other tables? Can I write the foreign key to multiple tables
when using a query as a recordsource?


Getting multi-table queries to be updateable is tough with a couple tables,
almost impossible with three or more (as you have found), The subforms idea
is not a kludge. It is the standard and recommended way to do this.

Nov 12 '05 #2
> Getting multi-table queries to be updateable is tough with a couple
tables,
almost impossible with three or more (as you have found), The subforms idea is not a kludge. It is the standard and recommended way to do this.


Thanks for the tip.

I suppose the only other alternative would be to use something like:

DoCmd.RunSql ("INSERT INTO tblOrders ( [Cust_ID] ) VALUES (" & Me!Cust_ID &
")")

In any case, I've reworked the form in question so that it uses subforms to
populate the different underlying tables. I've tried to make it look like
it's only one form by aligning the input fields using a transparent border
on the subform. The problem is Tab Order - how do I get the cursor to go to
and from the subform according to the intended design of the form?

[subForm_frmAddress]
Private Sub Country_KeyDown(KeyCode As Integer, Shift As Integer)
Forms!frmNewRecord.cmdOK.SetFocus
End Sub

This doesn't seem to work correctly... is there a better way to control Tab
Order between forms?
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: mr_burns | last post by:
hi, is the table percent value for height used for displaying in browsers. i have a table i want to run to the bottom of the screen so it seemed best to set the height value to 100%. when i...
0
by: 2trax | last post by:
Hi all, I am trying to mimic the unix security model (users and groups) in a web app. I have thought of two ways of implementing this, but am unsure which would be better. The first design...
15
by: kimi | last post by:
I have just started working on a project that is partially complete. It is an application that is using access to store test results. The test results are being stored in two Access 2000 databases....
5
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
18
by: TORQUE | last post by:
Hi, Im wondering if anyone can help me with a problem. I have a form with more than 50 unbound fields. Some of the fields will be blank from time to time. This seems to be where im having...
7
by: david | last post by:
I have asked this question before. But it does not work for me. Given radion buttons in the web form design page. What I did is described as follows. A panel control is dragged into the design...
1
by: Mark | last post by:
Hi there gurus, can you please add your 2 cents on this design? We're having trouble relating these tables in a diagram because of the keys. Is it necesary to have the references setup? I would...
4
by: yanjie.ma | last post by:
Hi, I've got a two part question on table and form design (sorry for the length but it takes a bit to explain). Our sales department uses a look-up table to help the them select the best...
54
by: csolomon | last post by:
Hello: I was wondering if I could get some input on how to address a design issue, involving my composite table. I have one portion of my project complete. The following forms and reports I...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.