creating a form for costings with a large number of fields

[Guest]

Hi

I need to create a form to perform costings on. Currently the work is done
in an excel spreadsheet with the 3 columns, qty, price and then (qty*price).
There are roughly fifty fields for each costing. To break down a building a
different tab is used in the spreadsheet for different sections of the
building, currently the maximum is 15 tabs used.
I want to try and set this up in one form but have the problem of a table
only allowing 255 fields.
So I am considering the following options and would like feedback as to the
best way forward.

1 Link the form to more than one table so that each section of the building
would have a table with the costing information stored in it. (Max 15 tables
and I am not sure there would be the space on the form for all the fields to
be displayed to do this)
2 Use subforms on the mainform linked again to different tables
3 Use Tab Control Pages with fields linked to different tables or even if
possible with each Tab Control page linked to different tables.
4 Any other options not discussed above.

Thanks

Richard

 

[George Nicholson]

2 & 3 are essentially the same idea. TabControl pages and Controls on those
pages are part of the form they are sitting on, so they have the same
recordsource as that form *unless* you use subforms.

There are roughly fifty fields for each costing.

A bit more detail? Not sure why you'd need as many fields as you say. Be
careful and don't commit spreadsheet-itis on a database.

Maybe you could use a structure like:
CostType (1 of 15)
CostDescription
Qty
Price


HTH,

 

[Guest]

The reason for 50 fields per costing is there are 38 different materials, 5
labour types, 7 plant and equipment types against which the user could have
to enter values.
Could you explain further what you meant by spreadsheet-itis

 

[John W. Vinson]

The reason for 50 fields per costing is there are 38 different materials, 5
labour types, 7 plant and equipment types against which the user could have
to enter values.
Could you explain further what you meant by spreadsheet-itis

Then you need a Materials table with 38 rows, a LaborTypes table with five
rows, and a EquipmentTypes table with 7 rows; and queries to combine the
selections.

You do NOT need a table with 38 + 5 + 7 fields, and in fact such a table would
simply be *wrong* from a relational design view.

John W. Vinson [MVP]

 

[Guest]

Forgive me for my ignorance.
I can understand the different tables pulling the data into the form using
comboboxes and queries, if I understand you right.
However I am still left with materials at 38 fields x 15 potential costings
= 570 fields which would mean I would need to put material information into 3
tables (at 255 fields per table) just for materials alone. Is this the
correct way to do this.

 

[John W. Vinson]

Forgive me for my ignorance.
I can understand the different tables pulling the data into the form using
comboboxes and queries, if I understand you right.
However I am still left with materials at 38 fields x 15 potential costings
= 570 fields which would mean I would need to put material information into 3
tables (at 255 fields per table) just for materials alone. Is this the
correct way to do this.

No.

You're still thinking spreadsheet, and thinking "wide-flat".

You need a costings table with three fields: Material, CostingType, Cost.

This table would have 570 ROWS, not 570 fields.

If you're assuming that every cost needs a separate field in a wide-flat
table... rethink. It doesn't. You can use a Query joining to this costings
table by two fields, or three, or even four to isolate the record containing a
specific cost.

John W. Vinson [MVP]

 

[Guest]

Ok here goes, I really have got hung up on replicating the current costings
spreadsheet in Access and have lost sight of principles

Thanks

 

[John W. Vinson]

Ok here goes, I really have got hung up on replicating the current costings
spreadsheet in Access and have lost sight of principles

Spreadsheets are wonderful and powerful tools... but as your excellent example
shows, are not good guides for constructing relational databases. As I say
occasionally, "you can drive nails with a crescent wrench - but that doesn't
make it a hammer!"

Hope you're back on track. Post back if you run into roadblocks.

John W. Vinson [MVP]