Large Tables

  • Thread starter Thread starter J. Hurd
  • Start date Start date
J

J. Hurd

I am attempting to creat a database program that will
print out a standard form. Unfortunately, the form has
about 300 different fields that require info. Because the
max table size is 255 fields, I was unable to create ONE
table... SOOOO, I split the table and then tried to link
it with a query... Unsuccessful... I guess the bottom line
is... HOW DO I CREATE THIS TABLE THAT HAS OVER 255
FIELDS.. AND THEN CREATE A FORM SINCE EVERYTHING IS TOO
BIG FOR ACCESS... ANY SUGGESTIONS ARE GREATLY
APPRECIATED.. I AM ON YAHOO S/N NPPD1217.. OR EMAIL ME AT
(e-mail address removed) IF YOU ARE ABLE TO GIVE ME SOME
POINTERS... THE WORST PART??? ABOUT 35 OF THE TABLE FIELDS
REQUIRED THEIR OWN TABLES FOR PULL DOWN MENU PURPOSES... I
CREATED ALL THIS CRAP AND NOW I CANT CREATE THE FORM OR
THE REPORT TO PRINT IT!!!! THANKS FOR THE HELP
 
Never run up against Access' limitations, but have you
tried creating subreports, and then inserting them on a
main report?

HTH
Kevin Sprinkel
 
J. Hurd said:
I am attempting to creat a database program that will
print out a standard form. Unfortunately, the form has
about 300 different fields that require info. Because the
max table size is 255 fields, I was unable to create ONE
table... SOOOO, I split the table and then tried to link
it with a query... Unsuccessful... I guess the bottom line
is... HOW DO I CREATE THIS TABLE THAT HAS OVER 255
FIELDS.. AND THEN CREATE A FORM SINCE EVERYTHING IS TOO
BIG FOR ACCESS... ANY SUGGESTIONS ARE GREATLY
APPRECIATED.. I AM ON YAHOO S/N NPPD1217.. OR EMAIL ME AT
(e-mail address removed) IF YOU ARE ABLE TO GIVE ME SOME
POINTERS... THE WORST PART??? ABOUT 35 OF THE TABLE FIELDS
REQUIRED THEIR OWN TABLES FOR PULL DOWN MENU PURPOSES... I
CREATED ALL THIS CRAP AND NOW I CANT CREATE THE FORM OR
THE REPORT TO PRINT IT!!!! THANKS FOR THE HELP
Click to expand...

I understand your frustration, but please don't yell.

I don't really know your situations, but a table with 255 fields is
probably not "normalized" -- that is, it probably has repeating fields,
like Phone1, Phone, Phone3 ... and the like. If that's the case, you
definitely want to take the repeating fields and put that data in a
separate table as rows, not columns, with the second table related
many-to-one to the first table. Then you would use subforms to present
this secondary information on the main form.
 
Ill give you the jist of the idea here... there is an
excisting form (paper version)... that form contains 122
fields that MUST be filled in... of the 122, most are
divided into sections.. ie. 1 field BOX for first middle
and last name... that box is, although one field on the
paper, split into 3 sections.. one for each part of the
name... therefore, 1 just became 3...
 
therein lies the problem... i am by far no computer dummy,
but i am not a wiz at access either... as i said in the
reply to the above poster, there are about 122 fields on
the paper form that i am trying to convert.. of the 122
some of those are split.. ie. first last middle name...
all in one field on paper, however split into three on
access... also, of the 122 fields there are no
duplicates... on the form there are no repeat fields, but
just for instance, box 40 on the form is actually box 40a
40b 40c and 40d.. so i again turned 1 field into 4... and
sorry about the yelling
 
J. Hurd said:
Ill give you the jist of the idea here... there is an
excisting form (paper version)... that form contains 122
fields that MUST be filled in... of the 122, most are
divided into sections.. ie. 1 field BOX for first middle
and last name... that box is, although one field on the
paper, split into 3 sections.. one for each part of the
name... therefore, 1 just became 3...
Click to expand...

Even if you don't have a lot of repeating fields, maybe you can group
logically related sets of fields together and put into tables that are
related one-to-one with the main table. Then you could still use
subforms to present these tables to the user.
 
As Dirk stated you should still be able to group some
information from your paper form and make separate tables
for this. For example, you can group the name and address
(assuming there's an address on your form) into one table
with a Primary key using the autonumber. If nothing else
occurs to you then group the fields by question number,
each group of 30 or 40 questions is one table. Then the
only thing that you have to do is use the same key for all
of the tables.

What you will wind up with is 3 or 4 tables, the first one
with autonumber primary key and the rest related to this
one using the same number as its primary key, all with a
one-to-one relationship.

Then your main form will be bound to the first table and a
bunch of subforms bound to each of the others with either
a combo box or a listbox to show the key from the first
table.

Something like this for your table structure:

tblNameAddress
keynumber (autonumber PK)
salutation
firstname
middleinit
lastname
suffix
street number
street name
suite or box
additional address
city
state
zip/postcode

tblQuestiongroup1
keynumber (PK here but FK to first table)
qone
qtwo
qthree
qfour
qfive
....

tblQuestiongroup2
keynumber (PK and FK as above)
qthirtyone
qthirtytwo
....

etc


The forms fall into place easily then.
(I also recommend using more descriptive titles for your
fields, for example if questionfifteen relates to whether
the respondant is a smoker or not, use 'smoker' for the
fieldname instead of qfifteen.)

Hope that helps
 
OK... i have seperated the data into like groups.. that
part is ok... i have linked the tables with index fields
that are autonumbered... now the problem is creating the
report to print everything at one time... there is still
the same amount of fileds to print...i created 2
querys..each contains half of the info.. when i attempt to
create a report using the queried info it says unable to
create report... any ideas on how to get allll that info
into the report ?
its not a matter of setting the page up.. once i can
create a report i will be able to arrange the report like
the actual paper report... thanks for all the help so far
 
-----Original Message-----


Even if you don't have a lot of repeating fields, maybe you can group
logically related sets of fields together and put into tables that are
related one-to-one with the main table. Then you could still use
subforms to present these tables to the user.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Yes, Ernie; that's a good solution. But I think J. Hurd's
question is about reproducing this in a printer version,
and in that case, subreports would seem the way to go.

Kevin Sprinkel
 
OK... i have seperated the data into like groups.. that
part is ok... i have linked the tables with index fields
that are autonumbered... now the problem is creating the
report to print everything at one time... there is still
the same amount of fileds to print...i created 2
querys..each contains half of the info.. when i attempt to
create a report using the queried info it says unable to
create report... any ideas on how to get allll that info
into the report ?
its not a matter of setting the page up.. once i can
create a report i will be able to arrange the report like
the actual paper report... thanks for all the help so far
Click to expand...

Since the maximum number of fields in any one query is 255, and you say
you have more than 300 overall, Kevin Sprinkel's suggestion to use
subreports would seem to be the way to go. This is exactly analogous to
build a form with subforms for the related tables. Build a separate
report for each of the related tables, then build a report for the main
table and insert the other reports as subreports on that report.
 
Back
Top