250 fields

  • Thread starter Thread starter mark r
  • Start date Start date
M

mark r

a table maxes out at 250 fields or so.
The table is accessed through one main form and two
subforms.
the user wants either:
a "print screen" of his mainform/subform
or a report containing all three forms

a Wizard report is limited in the number of fields

what is the best way to "report" the 250 fields?
 
Well, A reports can have 754 controls, so you should be fine there. My
question is whay you would have one table with 250 fields? In a relational
database (which Accessis) you should create multiple tables and link them.
One table might conain basic customer information, one might have contact
information, one might have billing details, etc.

I can think of almost no case where you should have a single table with more
than twenty or thirty fields.

Rick B


a table maxes out at 250 fields or so.
The table is accessed through one main form and two
subforms.
the user wants either:
a "print screen" of his mainform/subform
or a report containing all three forms

a Wizard report is limited in the number of fields

what is the best way to "report" the 250 fields?
 
so when you have a report display fields from more than
one table, is each field displayed on the report have a
way to specify which table the field comes from........a
control source table and then specify field name?

Is the only way to create a print out of a form by
creating a report and laying out each control exactly as
it appears on the form, or is their an automated way
to "print screen" a form as it appears on the screen?
 
You can pull fields from multiple tables into the query upon which your form
is based. They will appear on your field picker list then.

If you want to create a report that looks similar to your existing form a nd
then modify it, there is an easy way to do so.

Open the form, select your File menu, then Save As, then save your form as a
report. You can then open it as a report and modify it to better meet your
needs.

Rick B


so when you have a report display fields from more than
one table, is each field displayed on the report have a
way to specify which table the field comes from........a
control source table and then specify field name?

Is the only way to create a print out of a form by
creating a report and laying out each control exactly as
it appears on the form, or is their an automated way
to "print screen" a form as it appears on the screen?
 
Thanks...........but a problem. I am getting an error that
my QUERY (that will feed my report) has too many fields
defined, it seems to be topping off around 250........so
how do you feed a report with 754 controls?
Well, A reports can have 754 controls, so you should be
Click to expand...
fine there.

My question is whay you would have one table with 250
Click to expand...
fields?

I have tons of details of information about each customer,
like 250 details.

I could split out that one main table into sub tables with
a one to one relationship just to keep the tables smaller,
but for two reasons I didn't:

1. Most importantly, for outside audit purposes - those
that could be incredulous, I wanted the details into one
printable file..........perhaps over cautious and from a
programmers world unnecessary and not understandable, but
lawyers aren't smart enough to be programmers and will
always find ways to question whether tables could be
mismatched or created at other times - it just eliminates
one other disputable topic.

2. it would still be a one to one relationship so not much
gained by splitting the fields out, just more matching
overhead. No one has told me yet that table processing is
significanlty degraded once the table is bigger than X
number of fields.
..
 
I have tons of details of information about each customer,
like 250 details.
Click to expand...

250 *non-repeating*, *independent* details, i.e. different KINDS of
attributes? I'd be very fascinated to see this. I've needed as many as
60 fields in a table, once or twice. Is it possible that you have
several one-to-many relationships embedded within this record - i.e.
fields like CreditCard1, CreditCard2, CreditCard3 or AppointmentDate1,
AppointmentDate2, AppointmentDate3?
I could split out that one main table into sub tables with
a one to one relationship just to keep the tables smaller,
but for two reasons I didn't:

1. Most importantly, for outside audit purposes - those
that could be incredulous, I wanted the details into one
printable file..........perhaps over cautious and from a
programmers world unnecessary and not understandable, but
lawyers aren't smart enough to be programmers and will
always find ways to question whether tables could be
mismatched or created at other times - it just eliminates
one other disputable topic.
Click to expand...

DO NOT confuse data *storage* with data *presentation*. A properly
normalized database, with referential integrity enforced, is *more*
secure and verifiable than a huge bedsheet.... ummm spreadsheet. A
table IS NOT INTENDED to be "a printable file".
2. it would still be a one to one relationship so not much
gained by splitting the fields out, just more matching
overhead. No one has told me yet that table processing is
significanlty degraded once the table is bigger than X
number of fields.
Click to expand...

Well, I'll tell you, then. Performance WILL suffer. Worse, there is a
hard limit of 2000 bytes *actually occupied* in any record in a table
(exclusive of memo fields, which each count for 16 bytes no matter how
large). You can easily create a table with 250 20-byte text fields -
but the moment you FILL all those fields with an average of 10 bytes
each, you'll get an error message and not be able to save the record.

Seriously - table normalization is not an arcane, academic pursuit.
It's good design and its rules are there for good, thoroughly
validated reasons. Designing a database without using the principles
of normalization (splitting your table into multiple *one to many* -
very rarely one to one - tables) would be like running a law practice
without knowing anything about the concept of precedent.
 
After your condescending lecture, you NEVER ANSWERED THE
QUESTION I POSED: How does one build a report with up to
750 controls if the query that supplies it, which pulls
from several tables, is unable to pull more than 250
fields?

It is a huge problem when you HOG my reply to get up on a
soap box and prevent others from answering my question.

As for your "advice", since I do not
have "textfield1" "textfield2" "textfield3" (no embedded
one to many relations) I actually appreciate the tip off
about the 2000 byte limit. That actually is the only
useful piece of information in your babble.

So I ask you, humbly, oh fabled one, what does a byte
equal? A textfield defined as alpha-character 20 = 20
bytes? one byte per alpha character; a check box is one
check = one byte? an autonumber two digits = 2 bytes.
I need to count up my bytes per record and set up some one
to one tables if I am too close to the limit.

As for you seeing the table, it is none of your business.
Answer the questions or stop answering mine, there are too
many other very nice spoken people that have basically
helped me learn this software package all along the way.
I never claimed to be a software engineer, from the start.

Thanks in advance if you can stay gracious.
 
After your condescending lecture, you NEVER ANSWERED THE
QUESTION I POSED: How does one build a report with up to
750 controls if the query that supplies it, which pulls
from several tables, is unable to pull more than 250
fields?
Click to expand...

The only way that I can think of is what I suggested in another thread
on this subject: a Report with Subreports.
It is a huge problem when you HOG my reply to get up on a
soap box and prevent others from answering my question.

As for your "advice", since I do not
have "textfield1" "textfield2" "textfield3" (no embedded
one to many relations) I actually appreciate the tip off
about the 2000 byte limit. That actually is the only
useful piece of information in your babble.

So I ask you, humbly, oh fabled one, what does a byte
equal? A textfield defined as alpha-character 20 = 20
bytes? one byte per alpha character; a check box is one
check = one byte? an autonumber two digits = 2 bytes.
I need to count up my bytes per record and set up some one
to one tables if I am too close to the limit.
Click to expand...

I am not completely certain how UNICODE text fields are handled, but
according to the Help for Specifications, it is 2000 *characters*. A
Long Integer (such as an Autonumber) is not stored as characters,
though, but as a 4-byte binary integer; Integers are two bytes, Single
Float numbers are 4, Doubles (and Dates, a type of Double) are 8,
Yes/No fields are either 1 or 2.
As for you seeing the table, it is none of your business.
Answer the questions or stop answering mine, there are too
many other very nice spoken people that have basically
helped me learn this software package all along the way.
I never claimed to be a software engineer, from the start.

Thanks in advance if you can stay gracious.
Click to expand...

My apologies. I was trying to give good advice as I saw it; I
understand that you may find my suggestions unpalatable, and they may
indeed have been erroneous. I'm quite willing to have my erroneous
assumptions corrected, but that option doesn't appear to be available
either, so I'll drop this thread. If you wish, I'll be glad to notify
the other MVP's offline to reopen this thread, or you may wish to
repost the question to start a new thread.
 
The answer is that a report can have controls that have nothing to do with
data. Therefore it is perfectly logical that a report can have a limit on
the number of controls that is larger than the limit on fields in a query.

I agree with John BTW that the odds of a properly designed table requiring
250 fields is so small that the premise can be discounted even without
seeing the table.
 
John,

I am impressed with your response. It takes a big man to
reverse course and be so humble. I am sure you are a
brilliant software engineer.

Again I am respecting your warning on byte size. I counted
roughly and came to 2200 bytes if the user fills all the
fields. I think in actual usage and by definition the
user would not ever need to fill in every field, example,
if user fills in fielddetail47 then by definition the user
would not want to fill in fielddetail48.

Never the less, I deleted fields totally 150 bytes and did
some redesign as a result of your insight.

As for a report with subreports, I guess I will have to go
that route since no one made other suggestions yet. I
guess each subreport can have its own query as a source.

Thanks again
 
if user fills in fielddetail47 then by definition the user
would not want to fill in fielddetail48.
Click to expand...

<sigh>

I'm sorry, Mark. You just conclusively PROVED MY POINT that your table
is not properly normalized. If you have fields with these names, and
if the allowed value of fielddetail48 depends on whether fielddetail47
has a value or not, that means that your table violates second normal
form AND third normal form - and is therefore not normalized.

You are using a spreadsheet. You are using relational database
software to work with this spreadsheet, but you are not using it
relationally. It's possible to drive nails with a crescent wrench, but
that doesn't make it a hammer!

Best of luck working with the database. My honest, unbiased advice is
that you should normalize the table; if you choose not to do so, you
may be able to get your report working, and I hope you do; but you
will have a MUCH harder time over the lifetime of this project due to
the incorrect table structure.
 
Intriguing string here, and informative.
My conclusion and advice Mark? After reading your driveling "It is a huge
problem when you HOG my reply to get up on a
soap box and prevent others from answering my question." I too admire the
patience and good grace of John Vinson, a long-time contributor to this
forum. You visit our house and then abuse us? You clearly do not understand
or appreciate how forums such as this work and are an obvious amateur at
what you are attempting to do. Show some respect to those who contribute
their time and knowledge freely. Their advice is free. Take it or leave it.
So why not pay the money and hire a programmer. Then you can abuse him or
her to your hearts content. That's if they stay on the job.
 
WSF said:
Intriguing string here, and informative.
My conclusion and advice Mark? After reading your driveling "It is
a huge problem when you HOG my reply to get up on a
soap box and prevent others from answering my question." I too admire
the patience and good grace of John Vinson, a long-time contributor
to this forum. You visit our house and then abuse us? You clearly do
not understand or appreciate how forums such as this work and are an
obvious amateur at what you are attempting to do. Show some respect
to those who contribute their time and knowledge freely. Their advice
is free. Take it or leave it. So why not pay the money and hire a
programmer. Then you can abuse him or her to your hearts content.
That's if they stay on the job.
Click to expand...
Absolutely. Mark has also shown that he has no interest in learning much. If
I read "control" in one place and "field" in another I would want to know
the difference between the terms and would find out. RYFM woiould be my
first choice but I would ask if the answer was not there.

Chances are he will blame the tool. The bad thing about this is that if he
works for somebody they will never see that he was using the cresent wrench.
 
Back
Top