Help resolving a big issue

A

Ayo

I am try to create a database with multiple tables. In Excel, I had to break
down the main table into 9 tables. Now in the database 1 of the tables is the
record source for the main form, and there are 4 other forms. Each of this
form needs to have 2 tables as is record source.
My questions are: how do I design the database? and How do I join the tables
such that when I enter a new record on the main form, which has its own
table, this will automatically trigger a new record instance in the other
forms?
 
D

default105

Have you ever dealt with RDMS design before? What you ask, there is no
simple answer for. In reading your post, it sounds as if you have one table
that is in lamens terms the primary table and four tables that relate to it.
If this is the case you first need to set up your tables and relationships to
conform to at least 3NF (nf is normal form). I can not see why you would
want to have one form create a record and then create a record on another
form. This would create duplicate data which you should strive avoid in any
database. If you already know all of this, I did not mean to waste your
time. As far as how to combine two tables as one record source for a form,
use the sql creator by clicking the three dots ... at the right of record
source in the form design view properties dialog. Don't get discouraged by
this post if it sounds overwhelming, there is a lot of good reference
material out there to explain all you need to know to achieve what you are
asking.
 
T

Tom Wickerath

Hi Ayo,

You haven't provided enough information for anyone to help you with the
design of your database. You'll need to provide a list of the attributes that
you need to track in your database as a minimum. My suggestion is to post DB
design-related questions to this newsgroup:

http://www.microsoft.com/office/com...=microsoft.public.access.tablesdbdesign&exp=1

I also recommend that you first pay a visit to this site and read as much as
you can on DB design:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Read the two articles by Mike Hernandez as a minimum.
and How do I join the tables
such that when I enter a new record on the main form, which has its own
table, this will automatically trigger a new record instance in the other
forms?
Click to expand...

Generally, it is not advisable to automatically enter new records in related
tables. Records should only be added to related tables when you specifically
add a new record in a form or subform.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

Hi Default,

Good answer. I gave you a "greenie" <smile>

I read your About me information:
"Self-taught database developer, not to the extent of the MVPs (not even
close), but brute-forcing my way to the top."

Well, I'm pretty much self-taught as well. Good Luck with your continuing
journey!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
A

Ayo

So how do I "combine two tables as one record source for a form" by use the
sql creator by clicking the three dots ... at the right of record source in
the form design view properties dialog?
Do I have to put both tables in there seperated by commas or somthing else?
 
T

Tom Wickerath

The three dots (Build Button) that "default105" mentions should open the
Query Builder when you click on it. Here, you would create a query by adding
the two tables, just as you would create any other query.

Based on what I've read so far, I think you might be putting the
cart-before-the-horse, so to speak. In my opinion, you would be better off to
start with a much simplier database versus the 9 tables you have described.
Here is an Office Online article that can guide you in creating a simple
SELECT query:

http://office.microsoft.com/en-us/access/HP051879651033.aspx

You might want to try out Access MVP Crystal's "Access Basics" tutorial,
available here:

http://www.accessmvp.com/Strive4Peace/Index.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

Try copying the SQL statement for your query and posting it into a reply.
Hopefully it will not be too hairy (complex). When in query design view,
click on View | SQL View. Copy the SQL statement from this window, and paste
into a reply. I'll try my best to help you, but I may need to have you post a
zipped and compacted copy of your database for further investigation.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
A

Ayo

I would love to post the database because I think it would make it easier to
explain what I am trying to do. And I think it would also make it easier for
you to understand what I am trying to accomplish and be better able to direct
me towards an appropriate approach.
 
T

Tom Wickerath

The Microsoft.Public.Access.X newsgroups do not allow binary file
attachments, but you can post your database to a personal web space, assuming
that your ISP makes this available to you (most do). I recommend compacting
it first and then zipping it.

As an alternative, consider signing up for Utter Access
(www.utteraccess.com). This web-based forum allows one to post file
attachments, although I think they have a limit, like 500 KB or so. If you
post to either a personal site, or UA, reply back to this thread with a link
that I can use to download your database.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
J

John W. Vinson

I am try to create a database with multiple tables. In Excel, I had to break
down the main table into 9 tables. Now in the database 1 of the tables is the
record source for the main form, and there are 4 other forms. Each of this
form needs to have 2 tables as is record source.
My questions are: how do I design the database? and How do I join the tables
such that when I enter a new record on the main form, which has its own
table, this will automatically trigger a new record instance in the other
forms?
Click to expand...

You don't.

Creating empty "placeholder" records in your related tables is essentially
NEVER either necessary nor a good idea.

I strongly suspect that these are not normalized tables, but are unchanged
Excel spreadsheets. The logic used in Excel and in Access is VERY different -
a good spreadsheet design will probably be a badly flawed database design (and
vice versa!!).

Could you describe the nature of the information stored in these tables,
perhaps some fieldnames, how the tables are logically related, and why you are
spreading the information across nine of them?
 
A

Ayo

I have placed the database on the site. My tread is titled "Relationships
between 3 tables."
 
A

Ayo

Let me try and see if I can explain the whole thing. We have a form in excel
and we need to transfer and the data on the form into a database. There are
over 500 of these forms and they are grouped by projects. Three projects and
each with at least 250 forms. The forms are what we call RFDS RF data Sheets
and they contain information about a site and all the configuration at the
site.

Each form has 4 technology, GSM850, GSM1900, UMTS850 and UMTS1900 and each
also has an existing configuartion and a planned configuration. So what I did
was I create a table with the site information and 8 other tables. For
instance I create 2 tables for the GSM850 configurations, an existing
configuartion and a planned configuration. The reason I did this was because
each configuration has at least 175 columns, so for the GSM850 configurations
for I would end up with at least 300 controls from the configuartion and a
planned configuration tables.
 
J

John W. Vinson

Let me try and see if I can explain the whole thing. We have a form in excel
and we need to transfer and the data on the form into a database. There are
over 500 of these forms and they are grouped by projects. Three projects and
each with at least 250 forms. The forms are what we call RFDS RF data Sheets
and they contain information about a site and all the configuration at the
site.
Click to expand...

The existing paper forms are needed to identify the Entities and their
Attributes. They are NOT good designs for Access tables. Just because there's
a printed box on a paper form does not mean that the paper form corresponds to
a table, nor that each box on the form corresponds to a table field! Paper
forms are DREADFUL as guides to table structure.
Each form has 4 technology, GSM850, GSM1900, UMTS850 and UMTS1900 and each
also has an existing configuartion and a planned configuration.
Click to expand...

Ok; I can see *one* table, with a field for Technology and a yes/no field
where Yes is existing and No is planned.
So what I did
was I create a table with the site information and 8 other tables. For
instance I create 2 tables for the GSM850 configurations, an existing
configuartion and a planned configuration. The reason I did this was because
each configuration has at least 175 columns, so for the GSM850 configurations
for I would end up with at least 300 controls from the configuartion and a
planned configuration tables.
Click to expand...

I'll need to look at the tables, but I'm very certain that just as Tom has
said, that you have many repeating fields; and that one or a small number of
one to many relationships will correctly model this data.
 
Top