472,374 Members | 1,212 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,374 software developers and data experts.

Creating a form for 4 tables linked with one-to-one relationships?

Forgive me if this is a foolish question. I "play" in Access and have only created a few rather simple relational databases. My knowledge and experience is limited. I have no formal training, just picked up a few books and have gone from there... I'm not sure how to relate my question using the accepted shorthand, so I'm afraid this will be a narrative description.

I am using Access 2003. The database is for property management. I have about 12 tables. I know one-to-one relationsips are not normally recommended. In my case I "thought" it made sense to break down the information for organization purposes. And because not every record in what I consider the parent table will have a matching record in the child table(s).

In layman's language, my first table is general property info (called Property Info). It contains about 40 fields of information about the property such as address, city, state, zip, date available, rental term, pets allowed?, smoking allowed?, and many more similar fields. The actual fields relevant to my question are: PropertyID (pk), a lookup to an Owners table, and street address.

My second table contains information about any utilities and services included in the rent. There are about 70 fields in this table. Basically, each field is a different utility/service (yes/no .. there are about 35 fields each with different possible utilities and services that may be associated with any given property), service/utility provider (if yes), Service Contract? (y/n), Contract Expires When? (date), and other relevant information. The actual fields relevant to my question are: ID (PK - autonumber), PropertyID ( FK - used to creat the one-to-one relationship with my first table), all other fields pertain to the 35 service possibilities.

My third table is similar to the second but contains information about the included furniture, appliances and systems, and related warranty and service contract information. This table has close to 70 fields. Again, the possible list of included items are Y/N fields, then if set to yes the warranty and service contract information is entered. The fields relevant to my questions are: ID (PK -autonumber), PropertyId (FK- used to create the one-to-one relationship with the first table), and all other fields pertain to the various inclusions.

My fourth table is similar to the second and third, but contains special clause information such as legal info, deed restrictions, condo restictions, assoc. restrictions, and homeowner imposed restrictions. There are about 80 fields in this table. The relevant fields are: ID (PK-autonumber), Property ID (FK- used to create the one-to-one relationship), and all other fields identify each special clause and related information.

Ultimately the 4 tables together contain all relevant property information we need. Some properties may include several utilites, appliances, etc while others may include nothing extra. Soem may have several restictions, others may only have one or two. That is why I set up the tables this way.

What I am tring to do: I want to create a data entry form that combines several fields from all these table to mirror the format of paper forms in use. I tried to create a main form with 3 sub forms, but could not get it to tab properly through the main form (about half the fields), then jump to and through the first sub form, then to and through the second subform, etc ending with the remaining fields in the main form. I could set tab orders in each individual form, but the user would have to stop and click on each subform to activate it so they could enter the appropriate information. Additionally, this layout created a long entry form where scrolling could be necessary.

So I thought a tabbed form would work better. I could organize relevant information on each tab. But I can only get the fields from my first table to be available in the field list when in design view. I have not tried creating a form from a query, because one of the books discouraged that (can't remember why now). However, is that what I should be doing??? Or is it possible to do a subform using the tabbed pages format? Or am I going about this whole thing the wrong way?? I've spent days working on this in my spare time (not!) trying to figure out how to do this one form. I just can't figure out the best way to do it, so I am turning to the Experts here!

This newbie really appreciates any help or suggestions you can offer and thanks you in advance for your patience with my questions and posting style!
Jun 24 '08 #1
2 2201
14,534 Expert Mod 8TB
My advice would be to use the tab Pages object. Bind the main form to the first property table an put the PropertyID Control on the form (You can hide it). Then create a subform for all four tables and put each subform on one page of the tab object.
Jun 25 '08 #2
My advice would be to use the tab Pages object. Bind the main form to the first property table an put the PropertyID Control on the form (You can hide it). Then create a subform for all four tables and put each subform on one page of the tab object.
I'll give this a try. Thank you!
Jun 26 '08 #3

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

Similar topics

by: Jeffrey Silverman | last post by:
Hi, all. I have a linked list. I need an algorithm to create a tree structure from that list. Basically, I want to turn this: $list = array( array( 'id' => 'A', 'parent_id' => null, 'value'...
by: David Bradbury | last post by:
I currently have an iframe on a webpage into which users can insert content. They can further customise the text as I've included buttons such as Bold, Italic, Bullet point etc. This is done along...
by: tlaker10 | last post by:
Access 2000- my form has fields for “name”, “arrival”, “departure” and “site nbr”. Can I make a subform under “name” that keeps a history of all previous entries to arrival/departure/site nbr so...
by: Joe | last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource (using Oracle ODBC drivers). After linking the tables in Access, I inspect the data contained in the linked tables. For...
by: ZRexRider | last post by:
Hi, I am working on an Access application where the author used SQL back end via linked tables. I wrote some general functions that would execute pass through queries and was going to call...
by: sunrisewinesalon | last post by:
New here--I posted this to another group called 'ms access problems,' but it only had one member... Access says you can choose fields from more than one table or query to create a form--but can...
by: smd | last post by:
Hello and thanks for taking the time to read this. I've looked all over the web and newsgroups and can't find a solution to my problem. I've posted this question to the Access 2000 group as well -...
by: David Reynolds | last post by:
What is the best way of creating new SQL tables using VB.Net? I took a look at inserting new records but it was really time consuming on large number of records. Does anyone have any examples of...
by: jilppe | last post by:
I am having trouble setting up a filter on my form. I have two tables that are linked through a text field (contact_id). This field is a combination of two other text fields (first_name and...
by: EManning | last post by:
I have an A2003 database linked to SQL Server 2005. My problem is with a popup form with a filtered table as a recordsource. I set the filter with an SQL statement like this: SELECT * FROM...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.
by: F22F35 | last post by:
I am a newbie to Access (most programming for that matter). I need help in creating an Access database that keeps the history of each user in a database. For example, a user might have lesson 1 sent...

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.