Create tables from large report

[Greg2582]

I'm trying to create a database to hold information from a large form. The
form has five sections and 48 different fields. I decided the best way to do
this was to create a table fro each section of the form. There is no common
field, so I'm using the ID #(Primary Key) of the 1st section as a foriegn key
in the other 4 sections. The form I created has each of the 5 sections in a
Tab controlled page. When I run a query to control the form and submit the
data, the form comes up blank, with all the controls, label, etc. missing.
What am I'm missing?

 

[Rick B]

Your post is very confusing. Are you trying to create a table or a form?
Are you doing this from data in a report (as your subject indicates) and if
so, where is the report getting the data from?

If you are doing as the first line of your post suggest (trying to create a
database to hold information from a large form), then where is the form
getting the data from?

I'm confused.

Typically, you would have to create your tables and get your data structure
correct before you can work on a form or a report. Are you creating an
unbound form before you even think about your tables? YIKES! It would be
much easier to create the tables, then make a bound form.

Please post back with some details and clarify what you are trying to do.

 

[Roger Carlson]

Greg, I'm guessing you are trying to create a database to hold information
from a PAPER form, correct? If so, it is very unlikely that 5 tables, one
for each section, is the proper database design. There is little to gain by
splitting it into multiple tables. I can't really comment on specifics,
because you haven't given us any.

However, here are some general comments. Quite often on these paper forms,
there are multiple choices, usually in the form of check boxes. The
temptation is to create a Yes/No field for these, but that is usually
incorrect. It makes it very hard to query the data in any meaningful way.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ReallyBadDatabase.mdb" which was created from a paper form by
one of my customers. Aside from the horrendous colors, you will see how
difficult it is to query the table. There is another sample called
"ReallyBadDatabaseReborn.mdb" which shows how I corrected the problem, both
the user interface AND the table structure.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Greg2582]

Sorry about the confusion. Actually I created one large table and then broke
it down into five smaller tables. I've designed a form to submit data to the
tables. The query I'm using to submit the data to the tables, is causing the
form to appear blank, with no information on it.

 

[Greg2582]

Yes, I was giving a PAPER form, that has 48 different fields on it. Only 3 of
them are check boxes. The other 45 require you to a value that don't come
from drop-down list.
The PAPER form, has 5 distinct section, which is why I'm using 5 tables. So
the from won't be lengthy, I'm using a Tab Control, placing each section on
it's on separate tab. The only reason I split the table was because that
seemed like a really LARGE table.

 

[Roger Carlson]

Without seeing the paper form, I can't say whether any of these fields could
be grouped as values of a single field. I'll take your word that they
cannot.

So with that disclaimer, I don't see any value in breaking the single table
of 48 fields into separate tables. These tables would all have a one-to-one
relationship with the "main" table. All this does is complicate the issue
without adding any value. I'd combine them all back into one large table.
That should take care of your problem of not being able to see the data.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Duane Hookom]

I would have to agree with Roger on this. The only exception might be is if
groups of the fields might be updated by different users at the same time.
For instance, if this was for a loan application, there might be some fields
updated by a loan processor while other might be updated by a loan officer.
If there is a chance that these might be edited simultaneously by more than
one user, split tables might help.

However, that said, 48 fields seems like a bit many. Normalization has an
added benefit of reducing the opportunity for record locking.