Solved Form with varying Fields

[sethugec29]

Hi Everybody,

Im designing a Product Database. Attached is a processflow/ schematic of how I want want the data entered and stored. Can someone please point me in the right direction. I need to be able to add more fields in final tables and more product categories(i.e. tables).


Thanks everybody in advance !

 

[arnelgp]

you have entirely different approach.
you don't create it on the-fly.

you create the Category table first.

tblCategory(table)
CategoryID (autonumber)(PK)
Category (short text)

tblProducts(table)
CategoryID (long) FK
ProductID (autonumber) PK
ProductCode (short text)
ProductName (short text)

..
see the Northwind db for more example of those tables.

 

[sethugec29]

you have entirely different approach.
you don't create it on the-fly.

you create the Category table first.

tblCategory(table)
CategoryID (autonumber)(PK)
Category (short text)

tblProducts(table)
CategoryID (long) FK
ProductID (autonumber) PK
ProductCode (short text)
ProductName (short text)

..
see the Northwind db for more example of those tables.

Hi Arnelp, The category tables are meant to be predefined with respective fields, the only thing that changes is what fields are presented to the user. example: (c1 table has field a, b and c), but (c2 table only has fields b,c and d). The question was more do with the setuping the user inputs, data input sequence as oppose to the indivdual field crietria. Thanks for your input, i will take a look at your Northwind recommendation !

 

[arnelgp]

you need to create all those fields (a, b, c, c2, d) on Same table
use form for your data entry.
you may elect to hide/unhide controls, depending on who is using the form.
or to make it easier, create different form's for different users.
each user sees different Fields (but actually they are viewing same Table).

 

[sethugec29]

you need to create all those fields (a, b, c, c2, d) on Same table
use form for your data entry.
you may elect to hide/unhide controls, depending on who is using the form.
or to make it easier, create different form's for different users.
each user sees different Fields (but actually they are viewing same Table).

Yes i see your point, certain records in the "global table" would simply have no entries in the fields based on the category, any idea on how to set up the form ?

 

[Gasman]

and more product categories(i.e. tables).

Sounds like a spreadsheet layout

 

[Pat Hartman]

There are a couple of options. If you have a limited number of categories, you can create a parts table to hold all the common info as well as Category and then separate tables for each category assuming that there are numerous attributes. Then use a form/subform where the subform loaded will depend on the category of the part in the main form.

Another option is an entity/attribute model. This is less efficient but more flexible and will work no matter how many categories you have. Again the part table will contain all the common fields. Then you need a couple of different tables to define the attributes for the categories.
tblFields = the universe of attribute names.
tblCatFields = the fields for each category
tblFieldValues = The table where you enter the values, one row at a time.

I created an app for an insurance company that used this technique. I can't share it but I am willing to demo it if that's the route you want to go.

 

[sethugec29]

Hi Pat,

thanks for the input!

A demo would be great if you could spare the time, without releasing any sensitve info from your side .

Would it be possible to post a example of both options ? I would just need the rough concept, so that I could adapt it to my application.

Quick question, when you said less effcient (i.e. entity/attribute model)

--> are you refering to the qty of record that can be held/ look up speed or is it another aspect of effeciency ?

Thanks,

 

[mike60smart]

Hi Pat,

thanks for the input!

A demo would be great if you could spare the time, without releasing any sensitve info from your side .

Would it be possible to post a example of both options ? I would just need the rough concept, so that I could adapt it to my application.

Quick question, when you said less effcient (i.e. entity/attribute model)

--> are you refering to the qty of record that can be held/ look up speed or is it another aspect of effeciency ?

Thanks,

Sounds like a standard Cascade Combobox setup

Select a Category from 1st Combobox and then the 2nd Combobox only displays those Products associated with the Category selected.

 

[Pat Hartman]

A demo would be great if you could spare the time, without releasing any sensitve info from your side

Way too much work. Sorry.

are you refering to the qty of record that can be held/ look up speed or is it another aspect of effeciency ?

It increases the size of the database and makes certain things harder. It's a trade off. With the E/A model, each attribute ends up as a row in a table. So, if you have 100 attributes, 20 are common and 80 are variable/optional. You have a parent table with 20 columns and a child table with three columns but up to 80 rows. By the end of the first year we had tables with over a million rows. They were very narrow, but very long.
tblfieldValues:
FieldValueID (autonumber, PK)
AttributeID (long integer FK)
AttributeVal

The nice thing about the model is that once you establish the structure and some rules, the user can add new attributes and map them to a product without you having to do anything.

In the case of my client, before I developed the Access app, they were having to wait a minimum of four months for the ID department to create new tables and modify forms, etc. Once the Access app was developed, the user could create a new policy type in a couple of hours. In both cases, the time to create the word templates was separate. A policy might require dozens of documents, even hundreds if different riders were required for each state. So, that could take days depending on how many they had to create. I even built them a tool that would read the bookmarks in a word document and map them to defined fields. To do this, they had to use the field name as the bookmark name and use a numeric suffix if they needed to use the same field multiple times. For example, the Insured name might appear several times in the document so it would be Last01, Last02, First01, First02, etc. if they didn't use the naming rules, they had to do the mapping manually.

The task of creating the necessary fields and mapping them wasn't done by all the users. It was only done by three that I trained. The others just used the app to enter data. The app had users from San Francisco to London and was run using Citrix that was hosted in Farmington, CT.

 

[sethugec29]

Way too much work. Sorry.

It increases the size of the database and makes certain things harder. It's a trade off. With the E/A model, each attribute ends up as a row in a table. So, if you have 100 attributes, 20 are common and 80 are variable/optional. You have a parent table with 20 columns and a child table with three columns but up to 80 rows. By the end of the first year we had tables with over a million rows. They were very narrow, but very long.
tblfieldValues:
FieldValueID (autonumber, PK)
AttributeID (long integer FK)
AttributeVal

The nice thing about the model is that once you establish the structure and some rules, the user can add new attributes and map them to a product without you having to do anything.

In the case of my client, before I developed the Access app, they were having to wait a minimum of four months for the ID department to create new tables and modify forms, etc. Once the Access app was developed, the user could create a new policy type in a couple of hours. In both cases, the time to create the word templates was separate. A policy might require dozens of documents, even hundreds if different riders were required for each state. So, that could take days depending on how many they had to create. I even built them a tool that would read the bookmarks in a word document and map them to defined fields. To do this, they had to use the field name as the bookmark name and use a numeric suffix if they needed to use the same field multiple times. For example, the Insured name might appear several times in the document so it would be Last01, Last02, First01, First02, etc. if they didn't use the naming rules, they had to do the mapping manually.

The task of creating the necessary fields and mapping them wasn't done by all the users. It was only done by three that I trained. The others just used the app to enter data. The app had users from San Francisco to London and was run using Citrix that was hosted in Farmington, CT.

Completely understand Pat.... thank you for your detailed explanation ! I going to try to implement this strategy!

 

[Pat Hartman]

Here's pictures of three forms. The Data entry form, the Product Definition Form, and the all fields form. Notice on the product form that the Field name is a combo. The app starts with a universe of fields where their attributes are defined (description, field type, control type, query for combo if that is how the data should be entered. On the form shown, the users pick which of the common fields are used for this product. They specify a sort order which is used on the data entry form and also whether or not this field is required initially. Everything is ultimately required but future checking is document by document. The All fields form includes additional attributes that are needed but not shown on the Product form. There's a couple of special processing rules for this app. and it also has the ability to add tool tip text to help with data entry.

 

[sethugec29]

another simple solution in VBA.

"Me.Field1.Visible = True"

with an "if" statement.

not sure of the impact on the Database performance