Advise Needed

[Bob]

I have about 100 fields in my table, I have extended the form to fit all
these in. I could move about 50 off them to another form and use another
table, would that be a better idea. And if I did how would I keep the same
CustomerID and Auto Number....Thanks for any help...Bob

 

[Arvin Meyer [MVP]]

I've never needed more than 30 fields in a table. You probably have a design
problem. Why don't give us an idea of what you are trying to acomplish?

 

[Tom Wickerath]

Hi Bob,

It is very rare to see a table that needs to store 100 attributes (fields)
for a given record. I won't say that such databases do not exist, but it is
the rare exception, not the rule. I suspect that you have what is
euphamistically known as an "Access spreadsheet". In other words, perhaps one
table to store all of your data. If this is true, you will have nothing but
problems now, and into the future, with such a design.

You should spend some time gaining an understanding of database design and
normalization before attempting to build something in Access (or any RDBMS
software for that matter). Here are some links to get you started. Don't
underestimate the importance of gaining a good understanding of database
design. Brew a good pot of tea or coffee and enjoy reading!

http://www.seattleaccess.org/
(See the last download titled "Understanding Normalization"
in the Meeting Downloads page)

<Begin Quote (from page 23 of document)>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>


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

Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the relationships between the various
tables (Tools > Relationships...)



Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Bob]

Thanks Tom, But I do have 25 Tables, A lot of the fields in this table is
used for printing Reports Only,...Thanks Bob

 

[Bob]

These Are the fields I have listed below
Thanx.......Bob
HorseID
HorseName
MotherName
FatherName
Status
Sex
DateOfBirth
FoalDate Race
HorsePrice
StableReturnDate
MicroChipNumber
NearBrand
OffBrand
Remark
Worksheet
Cb1
Cb2
Cb3
Cb4
Cb5
Cb6
Cb7
Cb8
Cb9
Cb10
Cb11
HorseNotes
RacingGear
RacingReturn
Spelling
SpellingDate
Box12Date
Box1Date
Box2Date
Box3Date
Box4Date
Box5Date
Box6Date
Box9Date
Box10Date
Box11Date
****************************Printing Reports Only Below
RaceOwner1
RaceOwner2
RaceOwner3
RaceOwner4
RaceOwner5
RaceOwner6
RaceOwner7
RaceOwner8
RaceOwner9
RaceOwner10
RaceOwnerPer1
RaceOwnerPer2
RaceOwnerPer3
RaceOwnerPer4
RaceOwnerPer5
RaceOwnerPer6
RaceOwnerPer7
RaceOwnerPer8
RaceOwnerPer9
RaceOwnerPer10
Colour Printdate
BreederName
Covstallion1
Covstallion2
Covstallion3
Covstallion4
CovstallionRes1
CovstallionRes2
CovstallionRes3
CovstallionRes4
CovStallionDate1
CovStallionDate2
CovStallionDate3
CovStallionDate4
BreederName1
BreederName2
BreederName3
BreederName4
BreederName5
BreederName6
BreederPer1
BreederPer2
BreederPer3
BreederPer4
BreederPer5
BreederPer6
CurrentSeasonSire
LastServiceDate
TransferDate
TrainingS

 

[Rick Brandt]

These Are the fields I have listed below

All of the field groups that end in 1,2,3 etc. should be separate tables.

 

[Arvin Meyer [MVP]]

Here is a list of fields which need to be in many side tables:
The HorseID, being the Key, must be in both set of tables.

HorseID
Cb1
Cb2
Cb3
Cb4
Cb5
Cb6
Cb7
Cb8
Cb9
Cb10
Cb11
Box12Date
Box1Date
Box2Date
Box3Date
Box4Date
Box5Date
Box6Date
Box9Date
Box10Date
Box11Date
****************************Printing Reports Only Below
RaceOwner1
RaceOwner2
RaceOwner3
RaceOwner4
RaceOwner5
RaceOwner6
RaceOwner7
RaceOwner8
RaceOwner9
RaceOwner10
RaceOwnerPer1
RaceOwnerPer2
RaceOwnerPer3
RaceOwnerPer4
RaceOwnerPer5
RaceOwnerPer6
RaceOwnerPer7
RaceOwnerPer8
RaceOwnerPer9
RaceOwnerPer10
Covstallion1
Covstallion2
Covstallion3
Covstallion4
CovstallionRes1
CovstallionRes2
CovstallionRes3
CovstallionRes4
CovStallionDate1
CovStallionDate2
CovStallionDate3
CovStallionDate4
BreederName1
BreederName2
BreederName3
BreederName4
BreederName5
BreederName6
BreederPer1
BreederPer2
BreederPer3
BreederPer4
BreederPer5
BreederPer6

The actual many side tables may only really need the following:

HorseID
Cb1
Box12Date
RaceOwner1
RaceOwnerPer1
Covstallion1
CovstallionRes1
CovStallionDate1
BreederName1
BreederPer1

If all these fields are in each record, then you'll put them in a single
many-side table. If the fields are not in each record, they need their own
table. If you need an explanation as to why, look at:

http://datamodel.org/NormalizationRules.html

 

[John W. Vinson]

Thanks Tom, But I do have 25 Tables, A lot of the fields in this table is
used for printing Reports Only,...Thanks Bob

If you're assuming that all fields must be in the same table in order to print
them on a report - you're making an erroneous (but very common) assumption.

Reports will USUALLY be based on multitable queries. It is *NOT* necessary to
put all data into one table in order to report it!

John W. Vinson [MVP]

 

[John W. Vinson]

These Are the fields I have listed below

Comments inline

Thanx.......Bob
HorseID
HorseName
MotherName
FatherName

Will the sire and dam be in the same table? If so, their ID's (instead of
their names) will be preferable.

Status
Sex
DateOfBirth
FoalDate Race
HorsePrice

This is static? Surely the price will change over time!

StableReturnDate
MicroChipNumber
NearBrand
OffBrand

A separate Brands table might be worth considering.

Remark
Worksheet

What's a "worksheet"?

Cb1
Cb2
Cb3
Cb4
Cb5
Cb6
Cb7
Cb8
Cb9
Cb10
Cb11

Here's 11 incorrect fields. If each Horse has eleven CB's (whatever those
might be), you need ANOTHER TABLE, with fields HorseID, CBNo (values 1 to
11... or more!), and the value of CB.

HorseNotes
RacingGear
RacingReturn
Spelling
SpellingDate

Not sure what "spelling" might be...

Box12Date
Box1Date
Box2Date
Box3Date
Box4Date
Box5Date
Box6Date
Box9Date
Box10Date
Box11Date

Again... if one horse has 12 Box Dates, use a second table with HorseID,
BoxNo, and BoxDate.

****************************Printing Reports Only Below

RaceOwner1
RaceOwner2
RaceOwner3
RaceOwner4
RaceOwner5
RaceOwner6
RaceOwner7
RaceOwner8
RaceOwner9
RaceOwner10

A many to many relationship here: you need ONE table of Owners (all the
owners, no matter how many horses they own), and ONE table of RaceOwners. I
presume this means that a given horse might have several owners over time and
different owners for different races? If so you need a table of Races, and a
table RacesRun with fields HorseID, RaceID, RaceOwner, and maybe the outcome
of the race for this horse (position in the race, scratch, whatever).

RaceOwnerPer1
RaceOwnerPer2
RaceOwnerPer3
RaceOwnerPer4
RaceOwnerPer5
RaceOwnerPer6
RaceOwnerPer7
RaceOwnerPer8
RaceOwnerPer9
RaceOwnerPer10
Ditto

Colour Printdate
BreederName
Covstallion1
Covstallion2
Covstallion3
Covstallion4
CovstallionRes1
CovstallionRes2
CovstallionRes3
CovstallionRes4
CovStallionDate1
CovStallionDate2
CovStallionDate3
CovStallionDate4

One to many relationship between Horses and Breedings.

BreederName1
BreederName2
BreederName3
BreederName4
BreederName5
BreederName6
BreederPer1
BreederPer2
BreederPer3
BreederPer4
BreederPer5
BreederPer6
CurrentSeasonSire
LastServiceDate

Calculate this dynamically from the table of breedings.

TransferDate
TrainingS

You're using a relational database. Use it relationally, NOT as a
spreadsheet!! Read the references Tom suggested; you'll find that Access is
much more usable if you work with it rather than struggling against it!

John W. Vinson [MVP]

 

[Bob]

Thanks John,
HorsePrice was actually the Purchase Price, but I have no longer a text box
for it as HorseNotes is used for information about the horse
Horse goes under it breeding and age and sex untill raced named i.e.:
Danehill---Diamond Lover--2yo---Colt
Worksheet Yes/No is on another form lists Active(Status) horses and you tick
which horses will be printed on a worksheet
There is actually 13 What I can Management Boxes cb1-11, Spelling, Racing, I
couldn't rename those to later when I altered it to 13 boxes
There is a another table that you name them in and cb boxes are a viable of
comments, numbers , and yes/no with a date next to them, that means you can
change the name of these boxes for what ever purpose you need i.e.:
Spelling(When a horse goes for a rest) Shoeing,worming,racecolours,came with
Cover, etc etc
The extra Breeder is the Breeder of this horse and the other breeders are
the people that are going to breed from this filly/mare
RaceOwners most times are different to who this horse is linked to as the
person or persons who pay this account which I have called Clients
I am quite new to this so I just thought I would load up tblHorseinfo, And
extend the form to 3 times screen the size
A friend of mine has about 400 horses on it and it seems to cope, One little
problem I have is that I have to import frmHorseInfo a blank db to make any
changes and export back because it crashes my computer every time I change
it my original db which is 24,000kb
Thanks for listening to my garbage....Bob

 

[John W. Vinson]

Thanks John,
HorsePrice was actually the Purchase Price, but I have no longer a text box
for it as HorseNotes is used for information about the horse

Well... I'm afraid that's not a very good idea.

Notes fields (Memo type I presume?) cannot be searched efficiently because
they cannot be edited. If you have the word "price" twice or three times in a
memo field it's almost impossible to search for an associated dollar figure.
If, for example, you wanted to search for all horses which cost over $10,000
it would be easy if you used a HorsePrice field; and all but impossible with a
memo field.

Horse goes under it breeding and age and sex untill raced named i.e.:
Danehill---Diamond Lover--2yo---Colt

You could store this in a Name field until the horse was named. And if a horse
is a Colt this year, he won't be next year... and you'll have data in your
table WHICH IS WRONG.

Worksheet Yes/No is on another form lists Active(Status) horses and you tick
which horses will be printed on a worksheet
ok

There is actually 13 What I can Management Boxes cb1-11, Spelling, Racing, I
couldn't rename those to later when I altered it to 13 boxes

I have NO idea what this sentence means. "rename those to later"? Are you
assuming that your **TABLE** contains textboxes or combo boxes? It doesn't; a
Table *contains data*. Combo Boxes, Checkboxes, etc. are *tools* on a Form
which allow you to edit the data in the Table. Don't confuse data *storage* -
tables - with data *presentation* - forms and controls!

There is a another table that you name them in and cb boxes are a viable of
comments, numbers , and yes/no with a date next to them, that means you can
change the name of these boxes for what ever purpose you need i.e.:
Spelling(When a horse goes for a rest) Shoeing,worming,racecolours,came with
Cover, etc etc

Again, I'm not understanding you clearly here. Are these 13 fields which
contain the same sort of data, and you're just changing the LABEL on a Form?
What if cb7 means one thing for one horse, and something else for another
horse?

The extra Breeder is the Breeder of this horse and the other breeders are
the people that are going to breed from this filly/mare

Again: *a one to many relationship* requires another table. "Fields are
expensive, records are cheap" - if a given horse is going to be bred by
several stallions, you should NOT store this information in repeated fields in
the Horse table, but as *multiple records* in a DIFFERENT table.

RaceOwners most times are different to who this horse is linked to as the
person or persons who pay this account which I have called Clients
I am quite new to this so I just thought I would load up tblHorseinfo, And
extend the form to 3 times screen the size

Sorry. THAT IS SIMPLY WRONG. It's a severe misuse of Access and it will get
you in deep trouble with your database design.

A friend of mine has about 400 horses on it and it seems to cope, One little
problem I have is that I have to import frmHorseInfo a blank db to make any
changes and export back because it crashes my computer every time I change
it my original db which is 24,000kb

And that's just the start of the troubles you'll have.

I'd really suggest you read up on database design, from Tom's links or from
the "Database Design 101" links on Jeff's webpage (below):

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]

 

[Bob]

Thanks John , just a quick Question, Can I leave my form the same and just
create other tables so spreading my 100 fields be ok!
Being able to name the 13 Management boxes was if I gave the program to one
of my friends he could name them to what ever he wanted?
This is how horse breeding shows before being race named, by keeping the
same ID number
=IIf(tbName.Value="" Or IsNull(tbName.Value),tbFatherName.Value & "--" &
tbMotherName.Value & "--" & tbAge.Value & "--" & cbSex.Value,tbName.Value)
Thanks I will go to that web page and have a read

 

[John W. Vinson]

Thanks John , just a quick Question, Can I leave my form the same and just
create other tables so spreading my 100 fields be ok!

No. You'll be able to pertty quickly copy blocks of controls from one form to
another - but if you have 13 textboxes or combo boxes for the non-normalized
13 fields, you WILL need to change the design (to a continuous Subform with
*one* textbox or combo box, which you could use to enter 3, or 13, or 33
records as needed.

Being able to name the 13 Management boxes was if I gave the program to one
of my friends he could name them to what ever he wanted?

<frustration> You're thinking about the FORM. The Form *is not your data*. The
Form is a TOOL to enter data.

If your table structure is wrong - and, in my opinion, it is - then changing
labels on a form is like rearranging desk chairs on the Titanic. Having 13
unnamed combo boxes and changing the labels on the form *is simply bad design*
and NOT somthing I'd give anyone.

This is how horse breeding shows before being race named, by keeping the
same ID number
=IIf(tbName.Value="" Or IsNull(tbName.Value),tbFatherName.Value & "--" &
tbMotherName.Value & "--" & tbAge.Value & "--" & cbSex.Value,tbName.Value)

ok... you didn't *say* that, you showed the result. Again... data *storage*
and data *presentation* are two different things.

Thanks I will go to that web page and have a read

You may want to get a good book as well; quite a few are listed on those
websites.

John W. Vinson [MVP]

 

[Bob]

Thanks John I was only thinking about showing the latest record of the 13
Combo Boxes, like the last time the horse was wormed, the last time he went
for a spell,
The 13 Combo Information and 13 combo date boxes are queried to 13 forms
with the name I have labeled it, that shows all (Active) horses with their
information and date and then reported from there for printing...but looks
like I should look at subform them...Thanks for the help....Bob

 

[John W. Vinson]

Thanks John I was only thinking about showing the latest record of the 13
Combo Boxes, like the last time the horse was wormed, the last time he went
for a spell,

Well... wait.

Combo boxes don't display RECORDS. They display FIELDS.

It sounds like you need either 13 *subforms* (showing all the wormings, or
spells, or whatever) in reverse chronological order, most recent first,
keeping the history; or - probably much better - one Subform with all the
different actions, with a filter allowing you to choose wormings, or spells,
or breeding, or whatever. You could have a table with fields like

HorseID
ActionType
ActionDate
Outcome <or Comments, or Result, or whatever you want to call it>

For a Report you could choose several ways to display: use a subquery to
display only the most recent of each type of action; or, use a Crosstab query
to show a grid, actions across the top, dates running down; or use a Report
with sorting and grouping to show all the actions over a given time span.

John W. Vinson [MVP]

 

[Bob]

Thanks John, that's a great idea just have one subform and you can choose
any action i.e. Spelling, Worming, Shoeing. Might have to add one more field
say ActionInfomation. I will start learning how to subform then how to
create reports with queries, Thanks ,,,,Bob

 

[Pat Hartman \(MVP\)]

Please step away from the computer. Take a deep breath. Pick up a piece of
paper and a pencil. Use it. You have a lot of attributes and a lot of the
attributes have multiple occurrences which will require separate tables. It
is imperative that you get the table schema correct or reporting from the
mess will be next to impossible. Spend some time reading about
normalization and relationships. time you take to do this now will be
repaid 20-fold by eliminating rework and reporting problems. Take a stab at
creating a proper schema. Make lists of related fields and pay attention to
the number of instances you need to keep. If you need to keep more than
one, you need a new table. Each field that in your current design is
suffixed with a number belongs in a separate table. I didn't count the
repeating groups but it looked like around 5. That means 6 tables. One for
the horse, and one for each of the repeating groups. Having more tables
doesn't make the database more complex, it makes the data extraction easier.
In the world of spreadsheets, you just add another suffixed column if you
find you didn't originally define enough. If you do that in the world of
relational databases, you will be changing queries/forms/reports/procedures
until your fingers fall off when with a proper design all you would need to
do is to add a row - no structure or logic changes of any kind would be
required.

Once you have taken a stab at restructuring the tables, post back and we'll
help you refine the structure.

 

[Bob]

Pat Thanks for the advise, I suppose the 39 tables is too much for just
storing some information
On my frmHorseInfo I had a subform that I used for keeping remarks for
horses,
It has 3 Boxes
1-Dropdown cb that has shortcuts to enter data in to [Remarks]
2-Date tb that enters today's date in when data is entered into [Remarks]
3-[Remarks]- tb that holds remarks written in
It is linked to a table that has,
SrNo-Number-Serial Number to HorseID
dtDate- Date/Time-Date of remark
HorseID- Number-Horse ID Number
Remark- Text-Data entered
added=Category-Text-

I added the field Category to this tblRemarks, then added it to frmRemarks,
made a query qryCategory ,Is Not Null ,Unique Values Yes
Added a cb for qryCategory with control source Category
Added Category to my report that is filtered to only that horse , and
showed category fine
Looking at the table it all looks good category is being entered next to the
HorseID

So now I have to think of a way to show all horses last record in that
Category I select, and one horse with all data from That category
Am I on the right track now!!
Thanks for any In put.........Box

 

[Arvin Meyer [MVP]]

So Bob,

Let's say you are looking to display the last record in each category for
each horse. The base query (i.e. the query that shows the base information
that you want to display) is all in 1 table. The query would look something
like:

Select HorseID, CategoryID, Max([DateField]) As MaxofDateField
From MyTable
Group By HorseID, CategoryID
Order By Max(DateField) Desc;

In addition to the above, you'd have other tables to join on HorseID, and
adding the HorseName, and join on CategoryID and adding the Category.
Obviously, you'd need to use your table and field names, but I think you get
the idea now.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Pat Thanks for the advise, I suppose the 39 tables is too much for just
storing some information
On my frmHorseInfo I had a subform that I used for keeping remarks for
horses,
It has 3 Boxes
1-Dropdown cb that has shortcuts to enter data in to [Remarks]
2-Date tb that enters today's date in when data is entered into [Remarks]
3-[Remarks]- tb that holds remarks written in
It is linked to a table that has,
SrNo-Number-Serial Number to HorseID
dtDate- Date/Time-Date of remark
HorseID- Number-Horse ID Number
Remark- Text-Data entered
added=Category-Text-

I added the field Category to this tblRemarks, then added it to
frmRemarks, made a query qryCategory ,Is Not Null ,Unique Values Yes
Added a cb for qryCategory with control source Category
Added Category to my report that is filtered to only that horse , and
showed category fine
Looking at the table it all looks good category is being entered next to
the HorseID

So now I have to think of a way to show all horses last record in that
Category I select, and one horse with all data from That category
Am I on the right track now!!
Thanks for any In put.........Box

Please step away from the computer. Take a deep breath. Pick up a piece
of paper and a pencil. Use it. You have a lot of attributes and a lot
of the attributes have multiple occurrences which will require separate
tables. It is imperative that you get the table schema correct or
reporting from the mess will be next to impossible. Spend some time
reading about normalization and relationships. time you take to do this
now will be repaid 20-fold by eliminating rework and reporting problems.
Take a stab at creating a proper schema. Make lists of related fields
and pay attention to the number of instances you need to keep. If you
need to keep more than one, you need a new table. Each field that in
your current design is suffixed with a number belongs in a separate
table. I didn't count the repeating groups but it looked like around 5.
That means 6 tables. One for the horse, and one for each of the
repeating groups. Having more tables doesn't make the database more
complex, it makes the data extraction easier. In the world of
spreadsheets, you just add another suffixed column if you find you didn't
originally define enough. If you do that in the world of relational
databases, you will be changing queries/forms/reports/procedures until
your fingers fall off when with a proper design all you would need to do
is to add a row - no structure or logic changes of any kind would be
required.

Once you have taken a stab at restructuring the tables, post back and
we'll help you refine the structure.

 

[Bob]

Thanks Arvin this is the query I set up, had to have aName function in it so
as un-named horses appear under their breeding and age:
SELECT tblRemarks.Category, tblRemarks.dtDate, tblRemarks.HorseID,
tblRemarks.Remark, tblHorseInfo.HorseName,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name, tblHorseInfo.FatherName,
tblHorseInfo.MotherName, tblHorseInfo.DateOfBirth, tblHorseInfo.Sex
FROM tblRemarks INNER JOIN tblHorseInfo ON tblRemarks.HorseID =
tblHorseInfo.HorseID
ORDER BY tblRemarks.Category DESC , tblHorseInfo.HorseName,
funGetHorse(0,tblHorseInfo.HorseID,False);
This look OK to you looks good in datasheet View......Thanx.....Bob

So Bob,

Let's say you are looking to display the last record in each category for
each horse. The base query (i.e. the query that shows the base information
that you want to display) is all in 1 table. The query would look
something like:

Select HorseID, CategoryID, Max([DateField]) As MaxofDateField
From MyTable
Group By HorseID, CategoryID
Order By Max(DateField) Desc;

In addition to the above, you'd have other tables to join on HorseID, and
adding the HorseName, and join on CategoryID and adding the Category.
Obviously, you'd need to use your table and field names, but I think you
get the idea now.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Pat Thanks for the advise, I suppose the 39 tables is too much for just
storing some information
On my frmHorseInfo I had a subform that I used for keeping remarks for
horses,
It has 3 Boxes
1-Dropdown cb that has shortcuts to enter data in to [Remarks]
2-Date tb that enters today's date in when data is entered into [Remarks]
3-[Remarks]- tb that holds remarks written in
It is linked to a table that has,
SrNo-Number-Serial Number to HorseID
dtDate- Date/Time-Date of remark
HorseID- Number-Horse ID Number
Remark- Text-Data entered
added=Category-Text-

I added the field Category to this tblRemarks, then added it to
frmRemarks, made a query qryCategory ,Is Not Null ,Unique Values Yes
Added a cb for qryCategory with control source Category
Added Category to my report that is filtered to only that horse , and
showed category fine
Looking at the table it all looks good category is being entered next to
the HorseID

So now I have to think of a way to show all horses last record in that
Category I select, and one horse with all data from That category
Am I on the right track now!!
Thanks for any In put.........Box

Please step away from the computer. Take a deep breath. Pick up a
piece of paper and a pencil. Use it. You have a lot of attributes and
a lot of the attributes have multiple occurrences which will require
separate tables. It is imperative that you get the table schema correct
or reporting from the mess will be next to impossible. Spend some time
reading about normalization and relationships. time you take to do this
now will be repaid 20-fold by eliminating rework and reporting problems.
Take a stab at creating a proper schema. Make lists of related fields
and pay attention to the number of instances you need to keep. If you
need to keep more than one, you need a new table. Each field that in
your current design is suffixed with a number belongs in a separate
table. I didn't count the repeating groups but it looked like around 5.
That means 6 tables. One for the horse, and one for each of the
repeating groups. Having more tables doesn't make the database more
complex, it makes the data extraction easier. In the world of
spreadsheets, you just add another suffixed column if you find you
didn't originally define enough. If you do that in the world of
relational databases, you will be changing
queries/forms/reports/procedures until your fingers fall off when with a
proper design all you would need to do is to add a row - no structure or
logic changes of any kind would be required.

Once you have taken a stab at restructuring the tables, post back and
we'll help you refine the structure.



I have about 100 fields in my table, I have extended the form to fit
all these in. I could move about 50 off them to another form and use
another table, would that be a better idea. And if I did how would I
keep the same CustomerID and Auto Number....Thanks for any help...Bob