Help - More Tables/Fields Than Space

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need some creative help. I am building a form for tracking job
information. This form will track the items that are needed for each phase,
the cost of each item, the quantity of that item, and the total amount spent
on that item (cost * qty = total). I have 3 phases. Each phase is broken
down into the type of material used to create the items for that phase. For
example, phase 1 has the categories, PVC, Copper, and Misc, thus I have
tables Phase1_PVC, Phase1_Copper, Phase1_Misc.

Normally I would just create one large query and create a form with tabs for
each phase to handle the data entry. However, my problem is that there are
too many fields on to combine even 2 tables into one query. Why? Well, all
of the tables are built the same, and each one has room for 30 items and each
item requires 4 fields for the necessary data, thus I have 120 fields per
table.

So, what I want to do is to create a large form that has tabs and sub tabs.
That way I would have a tab for Phase1 and on that tab I would have subtabs
for each category. All of the tabs should tie in to the job code that is on
the main form above the main tabs. I have tried using subforms to handle the
sub tabs, but it dosen't seem to work. Anyone have any creative ideas for me?

Thanks in advance.
 
digger27,

Stop What You Are Doing!
The problem is not the number of fields, but your design. For example, you
mention tables Phase1_PVC, Phase1_Copper, Phase1_Misc.

Why is that necessary? It will really only make things harder for you. Why
not one table called Phase1_Material? Better Still, if the other Phases also
have material, then one table called Material. Then you could use some
fields to do filtering on.
Fields:
[Phase_No], [Material_Type]

I would recommend you reevaluate your database design before you waste any
more time on forms.
 
Klatuu said:
digger27,

Stop What You Are Doing!
The problem is not the number of fields, but your design. For example, you
mention tables Phase1_PVC, Phase1_Copper, Phase1_Misc.

Why is that necessary? It will really only make things harder for you. Why
not one table called Phase1_Material? Better Still, if the other Phases also
have material, then one table called Material. Then you could use some
fields to do filtering on.
Fields:
[Phase_No], [Material_Type]

I would recommend you reevaluate your database design before you waste any
more time on forms.

digger27 said:
I need some creative help. I am building a form for tracking job
information. This form will track the items that are needed for each phase,
the cost of each item, the quantity of that item, and the total amount spent
on that item (cost * qty = total). I have 3 phases. Each phase is broken
down into the type of material used to create the items for that phase. For
example, phase 1 has the categories, PVC, Copper, and Misc, thus I have
tables Phase1_PVC, Phase1_Copper, Phase1_Misc.

Normally I would just create one large query and create a form with tabs for
each phase to handle the data entry. However, my problem is that there are
too many fields on to combine even 2 tables into one query. Why? Well, all
of the tables are built the same, and each one has room for 30 items and each
item requires 4 fields for the necessary data, thus I have 120 fields per
table.

So, what I want to do is to create a large form that has tabs and sub tabs.
That way I would have a tab for Phase1 and on that tab I would have subtabs
for each category. All of the tabs should tie in to the job code that is on
the main form above the main tabs. I have tried using subforms to handle the
sub tabs, but it dosen't seem to work. Anyone have any creative ideas for me?

Thanks in advance.
Click to expand...
Click to expand...
 
Klatuu,
Thanks for your reply. However, I don't think your desing will suit my
needs. If I use your design I don't see how I can build the form I need for
data entry. I need to have a form that shows all of the items that I am
entering at the same time, preferrably by using tabs to separate phases. If
I use your design, it appears that I will have to create a form that will
only allow me to enter the Job, Phase, Material, etc. for one line, create a
new record and enter another line. This won't work for me. Each
phase-material combination will contain 20-30 items. This means that there
could be 280-420 items for a job. I need to be able to see then entire job
at once in order to be sure that I am getting all of the data in correctly.
Doing this one line at a time would be impossible for the people doing this
data entry. Also, this design would make my analysis of items and costs
harder. In my design I had each phase/material section on one line. This
allows for easier queries and totaling of each section.

Please let me know if you think that I am off on this.

Thanks again
Klatuu said:
digger27,

Stop What You Are Doing!
The problem is not the number of fields, but your design. For example, you
mention tables Phase1_PVC, Phase1_Copper, Phase1_Misc.

Why is that necessary? It will really only make things harder for you. Why
not one table called Phase1_Material? Better Still, if the other Phases also
have material, then one table called Material. Then you could use some
fields to do filtering on.
Fields:
[Phase_No], [Material_Type]

I would recommend you reevaluate your database design before you waste any
more time on forms.

digger27 said:
I need some creative help. I am building a form for tracking job
information. This form will track the items that are needed for each phase,
the cost of each item, the quantity of that item, and the total amount spent
on that item (cost * qty = total). I have 3 phases. Each phase is broken
down into the type of material used to create the items for that phase. For
example, phase 1 has the categories, PVC, Copper, and Misc, thus I have
tables Phase1_PVC, Phase1_Copper, Phase1_Misc.

Normally I would just create one large query and create a form with tabs for
each phase to handle the data entry. However, my problem is that there are
too many fields on to combine even 2 tables into one query. Why? Well, all
of the tables are built the same, and each one has room for 30 items and each
item requires 4 fields for the necessary data, thus I have 120 fields per
table.

So, what I want to do is to create a large form that has tabs and sub tabs.
That way I would have a tab for Phase1 and on that tab I would have subtabs
for each category. All of the tabs should tie in to the job code that is on
the main form above the main tabs. I have tried using subforms to handle the
sub tabs, but it dosen't seem to work. Anyone have any creative ideas for me?

Thanks in advance.
Click to expand...
Click to expand...
 
Back
Top