Multiple Tables

[Guest]

Am new to access but think I have hit the maximum # of fields allowed for a
table.

I currently have one table with more than 255 fields. I have decided to
break down the table down into multiple tables ... however there are two
forms for the data which means each form will have multiple tables as the
source. How do I add multiple tables to one form?

Thanks,
DBrown

 

[Duane Hookom]

Let me be the first to question why you need to have 255 or more fields.
Most of us feel that a table with more than about 30 fields is probably not
normalized.

Can you provide some information about why you think you need this many
fields?

 

[Guest]

I am updating a database that someone else created. Unfortunately the users
want it left how it was done .... I wouldn't have done it this way however
these are the constraints that I have to work with.

I have decided to break the table down into 6 different tables as the table
is of "parts" that "can" be ordered. The different tables are grouped by
types of parts.

The original database had "one" form for data entry. I was able to talk
them into letting me split it into 2 forms. By splitting the table one form
will add data to 3 tables and the other one adds to the other 3 tables.

I have seen this before where there was multiple tables listed as the source
for one form. I just cant remember the syntax. Hope this answers your
question.

 

[J. Goddard]

Hi -

You could use one main form, with as many subforms as you need - each
subform could have a different table as the source. A tab control, with
one sub-form per tab would be a possible solution; this would enable you
to use only one form, and you would be able to ensure all the current
records were related to the same "whatever it is they describe!"

John

 

[Guest]

Thanks but that would alter to many other things if I did it that way. I am
trying not to have to change the queries and reports. I know you can list
more than one table as the source ... I worked on a database once that had it
but I no longer have a copy of it. If I was going to totally redo the
database I would use the subform option.

 

[Duane Hookom]

There is no way that a query will return more than 255 fields. There is no
way that a form can have more than one record source. A record source can be
a query that is based on more than one table.

I agree with using subforms. This would be the easiest solution for
displaying so many fields. Actually I would probably begin working on a
normalized solution and not spend much time on the current table structure.

 

[John Vinson]

I am updating a database that someone else created. Unfortunately the users
want it left how it was done .... I wouldn't have done it this way however
these are the constraints that I have to work with.

I am just going to agree with John and Duane.

This design IS WRONG, and cannot be made to work. You're limited to
255 fields in the Form's recordsource, period; unless you use Subforms
there is NO WAY to do what you ask.

Would your users insist on misusing Excel, or Word? They're insisting
on misusing Access. I realize that "new is evil" - but using Access as
a spreadsheet, as the current design is doing, is like driving nails
with a crescent wrench. It can be done, sort of, but now the wrench is
broken!

Try creating a normalized database with a good form design (we'll be
glad to help). I think your users will find that typing the first
couple of letters of a tool name into a combo box is actually easier
than scrolling across 174 columns to find it, once they try it.

John W. Vinson[MVP]

 

[Guest]

First of all let me say that I agree that the database should be re-done.
However, in this particular case it can not be. The original logic is a
little different than normal relational databases. The customer does not want
the structure changed. If I have to do that then they will stay with what
they have and I will loose a weeks+ work of pay.

The purpose of the database is to keep track of when and if equipment parts
were ordered for a particular job.

The logic flows like this:

Originally there was one table of "equipment".
Fields included 6 different types of equipment: cable, wire, conduit,
hardware, signal, misc. Fields included the different sizes that "could" be
ordered.

One form is used for the input of the data - yes they put ALL fields on one
form - listed in six different columns. They want to be able to have an
overview of all equipment to be ordered on a particular job. They actually
print the form to use as a reference for each job. Then there are reports
that use querries for each part type.

They have agreed to let me split the form screen into two screens but that
is as far as they would go. I can split the table because it is behind the
scene and they won't know the difference.

Since there cant be multiple tables on a form, what about just splitting the
database in two parts. Almost like 2 independent databases in one.

Group A - Includes 3 diff part types (Cable, Wire, Conduit)
Table A - includes all fields for these part types
Form A - Lists the 3 diff part types in collumns - Form has link to Group B

Group B - Includes (Signal, Hardware, Misc)
Table B - includes all fields for these part types
Form B - Lists the 3 diff part types in collumns - form has link to Group A

Basically this is a scale down of what they already have.

This database was originally configured for the table on the backend and the
forms on the front end. I do not have experience with that so I am pulling
it all together ... their database is not that large nor will it ever get
that big so it shouldn't be a problem.

I know this goes against the cardinal rules of database design here ... but
I need some help thinking outside of the box. I have played with splitting
the table but when I create a new querrie I get the following:

Invalid braketing of name '[forms!frmreportsmenu!txtrptbegindate]'

I simply copied the sql from the other querrie and the braketing is correct.
If I can resolve that issue then I think I may have this licked.

My time is really short here and need to get this resolved. Is there anyone
who has a few minutes to chat about this issue. I could really use the help.

I know this isn't what you guys want me to do but sometimes we have to do
those things that we don't want to do.

Thanks for all your help.

 

[John Vinson]

Originally there was one table of "equipment".
Fields included 6 different types of equipment: cable, wire, conduit,
hardware, signal, misc. Fields included the different sizes that "could" be
ordered.

Ok, I see 6 fields here, or maybe a couple more if you have a primary
key. I don't see 256 fields. What is the actual STRUCTURE of your
table?

Since there cant be multiple tables on a form, what about just splitting the
database in two parts. Almost like 2 independent databases in one.

Well, you obviously don't need two DATABASES - two .mdb files; just
two forms. If that's acceptable to the users then by all means, go for
it.

One form is used for the input of the data - yes they put ALL fields on one
form - listed in six different columns. They want to be able to have an
overview of all equipment to be ordered on a particular job. They actually
print the form to use as a reference for each job. Then there are reports
that use querries for each part type.

This can of course be done very easily with a normalized database. But
I understand that the timecrunch you're in may forbid that.

Invalid braketing of name '[forms!frmreportsmenu!txtrptbegindate]'

I simply copied the sql from the other querrie and the braketing is correct.
If I can resolve that issue then I think I may have this licked.

The bracketing is in fact incorrect. Each part of the name needs its
own brackets:

[forms]![frmreportsmenu]![txtrptbegindate]


John W. Vinson[MVP]

 

[Guest]

Originally there was one table of "equipment".
Fields included 6 different types of equipment: cable, wire, conduit,
hardware, signal, misc. Fields included the different sizes that "could" be
ordered.

Ok, I see 6 fields here, or maybe a couple more if you have a primary
key. I don't see 256 fields. What is the actual STRUCTURE of your
table?

Since there cant be multiple tables on a form, what about just splitting the
database in two parts. Almost like 2 independent databases in one.

Well, you obviously don't need two DATABASES - two .mdb files; just
two forms. If that's acceptable to the users then by all means, go for
it.

One form is used for the input of the data - yes they put ALL fields on one
form - listed in six different columns. They want to be able to have an
overview of all equipment to be ordered on a particular job. They actually
print the form to use as a reference for each job. Then there are reports
that use querries for each part type.

This can of course be done very easily with a normalized database. But
I understand that the timecrunch you're in may forbid that.

Invalid braketing of name '[forms!frmreportsmenu!txtrptbegindate]'

I simply copied the sql from the other querrie and the braketing is correct.
If I can resolve that issue then I think I may have this licked.

The bracketing is in fact incorrect. Each part of the name needs its
own brackets:

[forms]![frmreportsmenu]![txtrptbegindate]


John W. Vinson[MVP]

The table Structure is as follows:

JOBNO (Job #)
JOBDESCRIPTION (Job description)
214 (part size - answer is #)
there are 50 items for cable parts
50 for wire parts
50 for signal equipment
50 for misc
50 for conduit parts
50 for hardware parts

the fields are numeric mostly but some are alph numeric or dates

The problem started because I added about 20 fields to each category.
Before it worked fine.

This is the actual syntax that I copied from and existing querrie that works
fine ...

Between ([Forms]![frmReportsMenu]![txtRptBeginDate]) And
([Forms]![frmReportsMenu]![txtRptEndDate])

but get the error message above.

Thanks for you help.

 

[John Vinson]

The table Structure is as follows:

JOBNO (Job #)
JOBDESCRIPTION (Job description)
214 (part size - answer is #)
there are 50 items for cable parts
50 for wire parts
50 for signal equipment
50 for misc
50 for conduit parts
50 for hardware parts

the fields are numeric mostly but some are alph numeric or dates

And it's IMPOSSIBLE to use a very simple continuous subform with two
tables?

Jobs
JobNo <Primary Key>
JobDescription
PartSize

JobParts
PartID <Autonumber Primary Key>
JobNo <link to Jobs>
PartType <wire, signal, misc, hardware>

The problem started because I added about 20 fields to each category.
Before it worked fine.

Exactly. You ran into the inflexible 255 field limit. You CANNOT get
around this limit with one table, or with one query based on multiple
tables.

This is the actual syntax that I copied from and existing querrie that works
fine ...

Between ([Forms]![frmReportsMenu]![txtRptBeginDate]) And
([Forms]![frmReportsMenu]![txtRptEndDate])

but get the error message above.

Try removing the parentheses, though I don't see that as being a real
problem. Perhaps the error is elsewhere in the query - could you open
the query in SQL view and post it here?

John W. Vinson[MVP]