Form with variable fields

[carole]

Hi,

I am very new to Access. I know the basics about tables, queries, forms, relationships, etc such that I can modify existing databases, but now I am trying to build one from the ground up.

The database will be for collecting inspection data. I am planning the table to have serial numbers as the records and inspection attributes as the fields. I want to create a form that allows for streamline data entry.

My challenge is this:
The table will have at least 50 fields of inspection attributes, and not every serial number requires data for every attribute. A set of serial numbers may require anything between 3 and 15 attributes, and the combinations of those attributes are infinite. For that reason I don't see how breaking the table down into smaller tables is possible. I don't want a form that has every field if only a few fields require entries, and I don't want an infinite number of forms.

Any suggestions?
Thanks so much!

 

[AccessMSSQL]

Carole:
I would set up a lookup table for your attributes, and a SerialNum/Attribute table for your serial numbers and attributes rather than creating 50 fields across the table for each attribute.

Your table structure would look something like this:

Lookup Table: tlkpAttributes (note:the tlkp prefix means Lookup table)
AttributeID autonumber primary key
AttributeName (make this indexed no duplicates)

Table: tblSerialNum
SerialNumID autonumber primary key
SerialNumber (make this indexed no duplicates)
other fields related to a serial number that are unique to serial number (no attribute fields in this table)

TABLE: tblSerialNumAttribute
SerialNumAttributeID autonumber
SerialNumID int
AttributeID int

make serialNumID and AttributeID a combination key in the above table

Your relationships would be
1. tblSerialNum.SerialNumID one to many tblSerailNumAttribute.AttributeID
2. tlkpAttributes.AttributeID one to many tblSerialNumAttribute.AttributeID

The tblSerialNumAttribute allows you to store only the attribute data that applies to that serial number. So a serial number with 3 attributes would only have 3 rows in the tblSerialNumAttributes table. A serial number with 50 attributes would have 50 rows in the tblserialnumattributes table.

I am not sure what data you are storing in the attributes fields but if you need to store values relating to an attribute you would need to add another column to tblSerialNumAttribute to store the value related to the attribute.

Hope thiis makes sense.

 

[carole]

That does make sense. I will try it. How can I then transfer the information in tblSerialNumAttributes to a form?

 

[AccessMSSQL]

You can have a main form bound to tblSerialNum, a subform on the main form bound to tblserialnumattributes with the parent and child fields property set to SerialNumID.
On the subform make your AttributeID column a combo box with the rowsource set to : Select AttributeID, AttributeName from tlkpAttributes. make sure the bound field property of this combo box is set to 1, the columns property set to 2, and the Columnwidths property set to 0;1. This way you can choose an attribute from the combo box list and the attributeID will automatically be stored in the tblSerialNumAttributes table. The serialNumID will automatically populate itself from the main form if parent/child field property is set up properly. Then if you have any additional fields to put the attribute value in, that would also be in the subform next to your combo box.

 

[carole]

That sounds great. I will try it and let you know if I have issues. Thank you so much!

 

[carole]

When you say

Your relationships would be
1. tblSerialNum.SerialNumID one to many tblSerailNumAttribute.AttributeID
2. tlkpAttributes.AttributeID one to many tblSerialNumAttribute.AttributeID

do you mean
tblSerialNum.SerialNumID one to many tblSerailNumAttribute.SerialNumID?

 

[nanscombe]

Find attached a little example of how I might tackle this given the layout of the tables that have already been discussed.

It may seem a bit complicated for what you want but it's just here for you to explore.

 

[carole]

Thanks! it helps having a visual

 

[AccessMSSQL]

When you say

do you mean
tblSerialNum.SerialNumID one to many tblSerailNumAttribute.SerialNumID?

oops. sorry carole, that is exactly what I meant. Sorry to confuse you!
I mis-typed that - was thinking too fast.
Hope it's going well.

 

[carole]

Ok,

I have a couple questions about the carole01 database nanscombe built.

I don't see where relationships are defined. Was that just left out or is there a different way to do them?

I am trying to flip-flop the frmItem so the attribute is in the main form and the sub form lists the serial numbers. If I make no changes to the subform, just change the control source of the main form and the record source of the text box, it pops up a box asking for the SerialNumID. Why?

Thanks!

 

[boblarson]

I am trying to flip-flop the frmItem so the attribute is in the main form and the sub form lists the serial numbers.

Then it would appear that you don't quite understand that the MAIN FORM is for the ONE side and the SUBFORM is for the MANY that relate to that item selected in the main form.

Isn't it that ONE Serial Number can have many attributes? Or does one attribute have many serial numbers?

 

[nanscombe]

... Isn't it that ONE Serial Number can have many attributes? Or does one attribute have many serial numbers?

However, it's possible that ONE attribute (type, as it's from a picklist) may apply to MANY Serial Numbers.

Hence the "ONE" form, in the example, effectively being built around a "MANY to MANY" link table.


Anyway, I've attached another database with the forms reversed, v2_frmAttribute & v2_sFrmItem.

I've also filled in the relationships this time.

The relationships for the Form & Subform, however, are defined by the Link Master Fields and Link Child Fields properties of the Subform object on the form design page.

If you click on the elipsis (...) button it brings up a dialog where you can choose which field is linked to which.

In this example both fields are called the same so you get two columns showing as Master Fields: SerialNumID and Child Fields: SerialNumID.


ETA: Scrub that, It is a ONE to ONE at the moment as I've based it on an instance of the attribute. Off to do a hasty version 3.

 

[nanscombe]

Ahem ... Version 3


Right. This time I have turned it around. I have the main form v3_frmAttribute based on the picklist table tlkpAttributes and the sub form based on a query qry_v3_sFrmSource which links the intermediate table tblSerialNumAttribute with the item table tblSerialNum.

The Subform is now linked by the fields; Link Master Fields: AttributeID, Link Child Fields: AttributeId.

Now a single attribute (tlkpAttributes) shows which items (tblSerialNum) it is linked to.

 

[carole]

Thanks again for the help... yes, one direction I was thinking is that one attribute applies to many serial numbers. In our work we measure one attribute of all the serial numbers before moving to the next attribute.

I will give rev 03 a shot.