424,303 Members | 1,366 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,303 IT Pros & Developers. It's quick & easy.

Help Too many Fields

P: n/a
I am needing some advice from some of you more advanced programmers.
I am trying to write a database for my employer. This database has
like 303 fields and I ran into a error that I had too many fields in
my table. I tried to split the data into two tables. Now I cannot put
all the fields on one form like he wants. It won't let me. I need to
keep the fiels in both tables working together because they will
contain one record on my form.

How do I usee all 300+ fields at one time on a field? Any help would
really be appreciated. HELP!!!

.... Thanks Tom
Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
TC
You need to start by reading this:

http://support.microsoft.com/support...es/Q100139.ASP

HTH,
TC
"Tom Conley" <tc**@hotmail.com> wrote in message
news:83********************************@4ax.com...
I am needing some advice from some of you more advanced programmers.
I am trying to write a database for my employer. This database has
like 303 fields and I ran into a error that I had too many fields in
my table. I tried to split the data into two tables. Now I cannot put
all the fields on one form like he wants. It won't let me. I need to
keep the fiels in both tables working together because they will
contain one record on my form.

How do I usee all 300+ fields at one time on a field? Any help would
really be appreciated. HELP!!!

... Thanks Tom

Nov 12 '05 #2

P: n/a
rkc

"Tom Conley" <tc**@hotmail.com> wrote in message
news:83********************************@4ax.com...
I am trying to write a database for my employer. This database has
like 303 fields and I ran into a error that I had too many fields in
my table. I tried to split the data into two tables. Now I cannot put
all the fields on one form like he wants. It won't let me. I need to
keep the fiels in both tables working together because they will
contain one record on my form.

How do I usee all 300+ fields at one time on a field? Any help would
really be appreciated. HELP!!!


Does your 'employer' also want this all on one screen?
Nov 12 '05 #3

P: n/a
"TC" <a@b.c.d> wrote in message news:<1065686340.166766@teuthos>...
You need to start by reading this:

http://support.microsoft.com/support...es/Q100139.ASP

HTH,
TC
"Tom Conley" <tc**@hotmail.com> wrote in message
news:83********************************@4ax.com...
I am needing some advice from some of you more advanced programmers.
I am trying to write a database for my employer. This database has
like 303 fields and I ran into a error that I had too many fields in
my table. I tried to split the data into two tables. Now I cannot put
all the fields on one form like he wants. It won't let me. I need to
keep the fiels in both tables working together because they will
contain one record on my form.

How do I usee all 300+ fields at one time on a field? Any help would
really be appreciated. HELP!!!

... Thanks Tom


I think you may have a problem here. The maximum number of fields in a
table is 255. I think the same hold true for forms.
Nov 12 '05 #4

P: n/a
And if your database is somehow already normalized and you *still*
need 300+ fields on your form (God help your poor users!), you would
have to base the form on a query...

SELECT A.*, B.*
FROM A INNER JOIN B ON A.PK=B.FK;

and then maybe you can build your form... but normalize first. As I
read somewhere once... turn the computer off, get out a stack of paper
or notecards or whatever and start writing. Figure out what goes
where... then try again.
Nov 12 '05 #5

P: n/a
On Wed, 08 Oct 2003 18:41:43 -0500, Tom Conley <tc**@hotmail.com>
wrote:
I am needing some advice from some of you more advanced programmers.
I am trying to write a database for my employer. This database has
like 303 fields and I ran into a error that I had too many fields in
my table.


Generally, this is a clue that there is a problem in the design of the
database. I'd start there first. Really examine the fields you're
capturing and think about why they're there and whether or not they'll
result in any duplication. If so, then there should be another table.

Displaying the information in a way that your employer can use isn't
that hard using forms and subforms. You can pretty much control how
they appear so no one needs to *know* the data's being stored in
different tables if they're offended by that idea. But seriously,
check your design first before crowbaring this many fields into a
form.

--
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employers)
Nov 12 '05 #6

P: n/a
rkc

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
As I
read somewhere once... turn the computer off, get out a stack of paper
or notecards or whatever and start writing. Figure out what goes
where... then try again.


This probably sounds goofy, but I almost always design my data
structure and toss ideas around using Window's plain old Paint Brush.
You can cut and paste text from other sources, move things around, use
colors, draw arrows, etc. All for the affordable price of $0 dollars.

When I am convinced I have things right, I then do something many
in this ng say is a waste of time, I write DDL and run a script to create
the tables. Once you are familiar with the Jet dialect it's much faster
than point and click.

Nov 12 '05 #7

P: n/a
"rkc" <rk*@yabba.dabba.do.rochester.rr.com> wrote in message news:<WN*******************@twister.nyroc.rr.com>. ..
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
As I
read somewhere once... turn the computer off, get out a stack of paper
or notecards or whatever and start writing. Figure out what goes
where... then try again.


This probably sounds goofy, but I almost always design my data
structure and toss ideas around using Window's plain old Paint Brush.
You can cut and paste text from other sources, move things around, use
colors, draw arrows, etc. All for the affordable price of $0 dollars.

When I am convinced I have things right, I then do something many
in this ng say is a waste of time, I write DDL and run a script to create
the tables. Once you are familiar with the Jet dialect it's much faster
than point and click.


How do you do it? I have used the usual Oracle-like syntax, but not
so much Access... ie

CREATE TABLE MyTable(
Field1 dbText(20),
Field2 dbLong,
.....)
Nov 12 '05 #8

P: n/a
TC

"rkc" <rk*@yabba.dabba.do.rochester.rr.com> wrote in message
news:WN*******************@twister.nyroc.rr.com...

(snip)
When I am convinced I have things right, I then do something many
in this ng say is a waste of time, I write DDL and run a script to create
the tables. Once you are familiar with the Jet dialect it's much faster
than point and click.

I do it via the UI initially. But then I write scripts or VBA for the
updates. That way I can upgrade a customer from v'x' to v'y' by running the
appropriate script(s), without having to remember what changes I made to the
db structure from version to version.

TC

Nov 12 '05 #9

P: n/a
rkc

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"rkc" <rk*@yabba.dabba.do.rochester.rr.com> wrote in message

news:<WN*******************@twister.nyroc.rr.com>. ..
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
When I am convinced I have things right, I then do something many
in this ng say is a waste of time, I write DDL and run a script to create the tables. Once you are familiar with the Jet dialect it's much faster
than point and click.


How do you do it? I have used the usual Oracle-like syntax, but not
so much Access... ie

CREATE TABLE MyTable(
Field1 dbText(20),
Field2 dbLong,
....)


I don't think the basics are much different.

A place to start is JetSQL40.chm or JetSQL35.hlp, which ever you
can find on your system.

Here's an example that creates the Order Details table found in the
NorthWind database (Access 2002 version, I think).
This was generated from a script so many of the Create Index
statements are redundant.

CREATE TABLE OrderDetails (
OrderID Long,
ProductID Long NOT NULL ,
UnitPrice Currency NOT NULL ,
Quantity Integer NOT NULL ,
Discount Single NOT NULL ,
CONSTRAINT pkOrderDetails PRIMARY KEY (OrderID,ProductID),
CONSTRAINT OrdersOrderDetails FOREIGN KEY (OrderID)
REFERENCES Orders (OrderID),
CONSTRAINT ProductsOrderDetails FOREIGN KEY (ProductID)
REFERENCES Products (ProductID)
)

Create Index OrderID
ON Order Details (OrderID);

Create Index OrdersOrder Details
ON Order Details (OrderID);

Create Unique Index PrimaryKey
ON Order Details (OrderID,ProductID)
WITH DISALLOW NULL;

Create Index ProductID
ON Order Details (ProductID);

Create Index ProductsOrder Details
ON Order Details (ProductID);



Nov 12 '05 #10

P: n/a
I suspect that some of the fields depend upon others and that there is some
duplication of data in your current design.
I suggest you look up the 'Normalization' rules. Normal forms 1, 2, and 3
are very inportant and can usually be followed without too much trouble.
Hugh

"Siobhan Perricone" <si***************@notspam.state.vt.us> wrote in message
news:o9********************************@4ax.com...
On Wed, 08 Oct 2003 18:41:43 -0500, Tom Conley <tc**@hotmail.com>
wrote:
I am needing some advice from some of you more advanced programmers.
I am trying to write a database for my employer. This database has
like 303 fields and I ran into a error that I had too many fields in
my table.


Generally, this is a clue that there is a problem in the design of the
database. I'd start there first. Really examine the fields you're
capturing and think about why they're there and whether or not they'll
result in any duplication. If so, then there should be another table.

Displaying the information in a way that your employer can use isn't
that hard using forms and subforms. You can pretty much control how
they appear so no one needs to *know* the data's being stored in
different tables if they're offended by that idea. But seriously,
check your design first before crowbaring this many fields into a
form.

--
Siobhan Perricone
Systems Developer
Vermont Agency of Natural Resources
(my comments are my own, not my employers)

Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.