record value in table when one of many buttons are clicked

[theDBguy]

That's exactly what I did with my tblParts, tblFinalProducts, and tblAssemblyComponents. Exactly that.....and it works great.

I get that. Thank you for the effort of showing me a "map." Works well for me.

If that is what we're all talking about then it may be conceivable to incorporate that model into some portion of my inspections - not all - but some.

Thanks.

Hi. That’s good to hear. Without seeing your db, I had your inspections as the assets in my example above. Just like each asset could have different attributes, each inspection can have different criteria. So, do you think you could apply this to your inspection tables too?

 

[Zydeceltico]

Hi. That’s good to hear. Without seeing your db, I had your inspections as the assets in my example above. Just like each asset could have different attributes, each inspection can have different criteria. So, do you think you could apply this to your inspection tables too?

I don't think I can do it with the inspection details across inspections. There just really isn't that much overlap - if any.

However, I'm thinking that I may be able to restructure/index the tables branching off of tblInspectionevent in this manner which would likely greatly promote reporting and lessen the current necessity of passing as many values via OpenArgs and DLookups as I am presently requiring.

Is there some sort of indexing structure where there is a table of tables? Like being able to assign an ID to a Table itself? I'm wondering if I can ID a table as an "entity" where all of it's fields are attributes -maybe I'm just doing semantic gymnastics.

Later - as yet undeveloped - there is a feature that I have to add where an entity-attribute design will definitely be helpful - - - - when I begin adding single part inspections for all of the parts and assemblies. The approach that you described and that I am using in my FinalProducts, Parts, Assemblies will greatly benefit and be much more streamlined using that approach.

 

[Zydeceltico]

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html

Without "writing another book" - - - (maybe we should write a book - I can be your frustrating protege) - - - - -

Those links you sent above are some of the clearest, most useful I have yet to see and don't know how I have missed them for so long.

And a note to any other reader of this post with my same questions that boil down to table structure design: this link: http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html for me - is one of the best - if not THE best (meaning clear and informative) discussion of a tactical approach to DB table structure design I have ever read. All the info you need and written very clearly. Thanks for that Micron!

Then try designing something on paper (I use sheets about 24x30 inches) and see how it goes.

I have many notebooks worth of these - And yes - it is super helpful.

If it helps, I do have a background in CMMS and plant stores, equipment reliability,ISO (quality and environmental), contracting and equipment maintenance - not so much manufacturing. The other disciplines tended to cross over into the manufacturing realm a bit, but I'm no "manufacturing process" expert. Passing that along in case it means anything to you.

It does help. Thanks.

And just so you know - I'm reviewing all of your other comments above as well. I see some more fine-tuning I can do.

Thank You

Tim

 

[MajP]

I am not suggesting that you go an entity attribute model, but I will show it for academic purposes. There is some learning and overhead. Also understand that an entity attribute model is not "the correct" data base design. In fact it is not a normalized database design, but an potentially efficient non-normalized design.

In your case you have a lot of measures and assessments. So you need to know for each attribute the data type and units of measures. Ex
Double PSI
Double Inches
Integer Count 'Number of failed parts
Text Pass Fail
Text Flow Rating ' Bad, Good, Excellent

The unfortunate part is that you are going to store numbers, booleans, and text all in the same field so the field has to be text. The good news is you know the datatype of the attribute and you can convert as necessary.

So for example you attribute table would look like this. I imported the fields from weld tests and weld assemble. I made up units

inspectionAttributeID	AttributeName	AttributeDataType	AttributeUnits	InspectionType
3	HatCoil	10	Count	8
4	PlateCoil	10	Count	8
5	WeldNumberFromNewTip	4	Count	8
6	LineEfficiency	10	Rating	8
7	Gun1PSI	10	PSI	8
8	1NuggetASize	10	inches	8
9	1NuggetAPF	10	Liters	8
10	1NuggetATorn	1	Yes/No	8
11	2NuggetASize	10	inches	8
12	2NuggetAPF	10	Liters	8
13	2NuggetATorn	1	Yes/No	8
14	3NuggetASize	10	inches	8
15	3NuggetAPF	10	Liters	8
16	3NuggetATorn	1	Yes/No	8
17	4NuggetASize	10	inches	8
18	4NuggetAPF	10	Liters	8
19	4NuggetATorn	1	Yes/No	8
20	5NuggetASIze	10	inches	8
21	5NuggetAPF	10	Liters	8
22	5NuggetATorn	1	Yes/No	8
23	1NuggetBSize	10	inches	8
24	1NuggetBPF	10	Liters	8
25	1NuggetBTorn	1	Yes/No	8
26	2NuggetBSize	10	inches	8
27	2NuggetBPF	10	Liters	8
28	2NuggetBTorn	1	Yes/No	8
29	3NuggetBSize	10	inches	8
30	3NuggetBPF	10	Liters	8
31	3NuggetBTorn	1	Yes/No	8
32	4NuggetBSize	10	inches	8
33	4NuggetBPF	10	Liters	8
34	4NuggetBTorn	1	Yes/No	8
35	5NuggetBSize	10	inches	8
36	5NuggetBPF	10	Liters	8
37	5NuggetBTorn	1	Yes/No	8
41	WeldSpacingReq	4	inches	2
42	WeldSpacingAct	4	Inches	2
43	ChiselTest	10	Pass Fail	2
44	BarTest	10	Pass Fail	2
45	ACBattsInstalled	10	Yes/No	2
46	WindguardUsed	10	Yes/No	2
47	AirdamPlatesCorrect	10	Yes/No	2
48	AirDamFoamCorrect	10	Yes/No	2
49	LongitudinalAirDam	10	Yes/No	2
50	InterlockGap1	6	mm	2
51	InterlockGap2	6	mm	2
52	LabelsApplied	10	Choice	2
53	BundlingTags	10	Choice	2
54	LayoutPerDWG	10	Choice	2

Now to create a new inspection. Such as a weld test I do an insert query into tblInspections. It inputs all the attributes for a weld test. The form would allow be to assign values to the attributes. That is where is gets a little tricky. I need another table the holds choices. Example, Attribute is lineEfficiency. On the form I might need choices of Poor, Fair, Good, Excellent. Yes/No fields would need choices of Yes No. Etc. Numeric fields likely need no choices. The form can get code heavy, but it is a single form for all inspections.

TblInspections
  InspectionAttributeID 'PK
  AttributeID_FK
  AttributeValue

Then you simply fill in the values for each attribute. But remember all values are in a text field since you have numbers and strings. If you need to do things like sum up all weld lengths this is where the model kind of fails. If you just need to look at the values you do not care that it is ".25" Inches instead of .25 Inches.

InspectionID_FK	AttributeName	AttributeUnits	AttributeValue
2	HatCoil	Count	10
2	PlateCoil	Count	10
2	WeldNumberFromNewTip	Count	.25
2	LineEfficiency	Rating	Average
2	Gun1PSI	PSI	12
2	1NuggetASize	inches	16.75
2	1NuggetAPF	Liters	78
2	1NuggetATorn	Yes/No	Yes
2	2NuggetASize	inches	.1
2	2NuggetAPF	Liters	5
2	2NuggetATorn	Yes/No	No
2	3NuggetASize	inches	.3
2	3NuggetAPF	Liters	4
2	3NuggetATorn	Yes/No	Yes
2	4NuggetASize	inches	1
2	4NuggetAPF	Liters	,8
2	4NuggetATorn	Yes/No	No
2	5NuggetASIze	inches	.67
2	5NuggetAPF	Liters	4
2	5NuggetATorn	Yes/No	Yes
2	1NuggetBSize	inches	12
2	1NuggetBPF	Liters	.8
2	1NuggetBTorn	Yes/No	Yes
2	2NuggetBSize	inches	.9
2	2NuggetBPF	Liters	11
2	2NuggetBTorn	Yes/No	Yes
2	3NuggetBSize	inches	7
2	3NuggetBPF	Liters	.5
2	3NuggetBTorn	Yes/No	No
2	4NuggetBSize	inches	.5
2	4NuggetBPF	Liters	7
2	4NuggetBTorn	Yes/No	Yes
2	5NuggetBSize	inches	6
2	5NuggetBPF	Liters	7
2	5NuggetBTorn	Yes/No	Yes

 

[Zydeceltico]

First of all - thank you! for taking the time for mocking this up. That is a great explanation. I get this. And I will be able to utilize this approach for a portion of as yet undeveloped branch of inspections dealing with specific parts.

The unfortunate part is that you are going to store numbers, booleans, and text all in the same field so the field has to be text. The good news is you know the datatype of the attribute and you can convert as necessary.

Fortunately - Very few mathematical calculations are required. I'll primarily be looking at frequencies of events or Date/Time comparisons. I would assume that I could still use standard Date/Time fields in the tblInspections you describe. Actually - on second thought - two of my primary measures TimeStart and TimeStop related to Line Stops and Coil Changes are Date/Time fields. Does access record Date/Time the way Excel does as a literal number? I wonder how that would work?

inspectionAttributeID	AttributeName	AttributeDataType	AttributeUnits	InspectionType
3	HatCoil	10	Count	8
4	PlateCoil	10	Count	8
5	WeldNumberFromNewTip	4	Count	8
6	LineEfficiency	10	Rating	8
7	Gun1PSI	10	PSI	8
8	1NuggetASize	10	inches	8
9	1NuggetAPF	10	Liters	8
10	1NuggetATorn	1	Yes/No	8
11	2NuggetASize	10	inches	8
...

In the above code, I see "AttributeDataType." Within the values I see numbers representing the value of that field. Is the idea that those numbers are referencing a look up table?

Now to create a new inspection. Such as a weld test I do an insert query into tblInspections. It inputs all the attributes for a weld test. The form would allow be to assign values to the attributes. That is where is gets a little tricky. I need another table the holds choices. Example, Attribute is lineEfficiency. On the form I might need choices of Poor, Fair, Good, Excellent. Yes/No fields would need choices of Yes No. Etc. Numeric fields likely need no choices. The form can get code heavy, but it is a single form for all inspections.

TblInspections
  InspectionAttributeID 'PK
  AttributeID_FK
  AttributeValue

Believe it or not - this part I get.

Then you simply fill in the values for each attribute. But remember all values are in a text field since you have numbers and strings. If you need to do things like sum up all weld lengths this is where the model kind of fails. If you just need to look at the values you do not care that it is ".25" Inches instead of .25 Inches.

InspectionID_FK	AttributeName	AttributeUnits	AttributeValue
2	HatCoil	Count	10
2	PlateCoil	Count	10
2	WeldNumberFromNewTip	Count	.25
2	LineEfficiency	Rating	Average
2	Gun1PSI	PSI	12
2	1NuggetASize	inches	16.75
2	1NuggetAPF	Liters	78
2	1NuggetATorn	Yes/No	Yes
.......

Again - very few actual mathematical calculations - a lot of frequency counts and Date/Time spreads and calculations on Date/Time.

No matter what - I am really grateful for your help broadening my functional awareness! I still have to develop the part-specific aspect of the db. I'm waiting for our manufacturing engineer to finish up a documentation management system which houses all the drawings - soon to be in a modernized, streamlined, and logical system. Hoping to make my next tasks easier. This model for that branch of the db might work really well as there are several hundred different parts/accessories, assemblies, etc. And at least 60% of those inspection criteria overlap. This could be a very good model for that.

So Thanks!

Tim

 

[MajP]

In the above code, I see "AttributeDataType." Within the values I see numbers representing the value of that field. Is the idea that those numbers are referencing a look up table?

Sorry that was not very clear. Those dataType values are the actual DAO datatype. I could have instead just use names (Boolean, Date, Text, Currency, Double, Long, Integer). Since all the "values" get stored in the inspection table AttributeValue field you need a link to determine what kind of value it is if you need to do something more than just look at the value. The AttributeValue field has to be a string field to handle all types of values like
Yes
1/1/2019
2.75
Good
N/A

In DAO 8 is numeric, 10 is string, 1 is boolean. I could have also wrote the words instead of using numbers. So assume you want to have a date Attribute (lets assume a date is 6 since I do not know off the top of my head). Everything gets stored as a string so you may have the value "1/1/2019", but you know that attribute is of data type 6 (date). If necessary you can write code for a query or other calculation to convert to date any attribute that datatype is 6.

Example

Select AttributeName, Cdate(AttributeValue) as AttributeDate from TblInspection inner join TblInspectionAttributes on AttributeID_FK = AttributeID_PK where AttributeDataType = 6

With that you know could do real date time things since you converted the strings into dates.

 

[MajP]

FYI. I have written thousands of Access DBs and may have only done 2-3 entity attribute models. Looking at what you are doing, it is a case where it would likely fit nicely and could reduce a lot of forms, tables, code, and queries. However this is no panacea, and I am not recommending you do it because you need to have enough experience to envision all the down stream consequences. In some places this will really simplify things and in others it will make it more complicated. You have to know enough to weigh it all out before building. You really cannot do a "little" bit of EA. I think it is all in or not. You would be doing a non-traditional approach and you may end up writing some more complex code as a trad-off for less forms and tables..

 

[Zydeceltico]

Sorry that was not very clear. Those dataType values are the actual DAO datatype. I could have instead just use names (Boolean, Date, Text, Currency, Double, Long, Integer). Since all the "values" get stored in the inspection table AttributeValue field you need a link to determine what kind of value it is if you need to do something more than just look at the value. The AttributeValue field has to be a string field to handle all types of values like
Yes
1/1/2019
2.75
Good
N/A

In DAO 8 is numeric, 10 is string, 1 is boolean. I could have also wrote the words instead of using numbers. So assume you want to have a date Attribute (lets assume a date is 6 since I do not know off the top of my head). Everything gets stored as a string so you may have the value "1/1/2019", but you know that attribute is of data type 6 (date). If necessary you can write code for a query or other calculation to convert to date any attribute that datatype is 6.

Example

Select AttributeName, Cdate(AttributeValue) as AttributeDate from TblInspection inner join TblInspectionAttributes on AttributeID_FK = AttributeID_PK where AttributeDataType = 6

With that you know could do real date time things since you converted the strings into dates.

This is all really becoming much clearer to me. Thanks! It will definitely be a more productive approach for what I have yet to develop.

Out of curiosity, you mention that the numeric datatype values are "the actual DAO datatype." IF I were to use the values instead of the name (Boolean, Date, Text, etc...) how does Access "know" that they aren't simple integers? In other words where/how do I tell Access that "Hey - I am, using DAO values in place of words?" Or is it that, going, the route you suggest I am working primarily with DAO recordsets? (...another teaching moment for you, another learning moment for me .

...And I see how to use SQL basically the same way to extract the records I would be looking for - - makes sense. Really a lot more streamlined in a lot of ways.

 

[Zydeceltico]

FYI. I have written thousands of Access DBs and may have only done 2-3 entity attribute models. Looking at what you are doing, it is a case where it would likely fit nicely and could reduce a lot of forms, tables, code, and queries. However this is no panacea, and I am not recommending you do it because you need to have enough experience to envision all the down stream consequences. In some places this will really simplify things and in others it will make it more complicated. You have to know enough to weigh it all out before building. You really cannot do a "little" bit of EA. I think it is all in or not. You would be doing a non-traditional approach and you may end up writing some more complex code as a trad-off for less forms and tables..

Understood. I'm not planning on redoing everything I've already done....but I've been struggling with conceptualizing how to handle the myriad part-specific drawing inspections and I can clearly see how this approach will benefit me there. And there's not a lot of overlap with that "branch of the tree" with the other inspections. It is almost a standalone area.

 

[Zydeceltico]

It is almost a standalone area.

I've been looking at my existing tblAssemblyComponents structure which is where the parts drawing branch (the EA unit) would connect to the rest of the DB and I'm pretty positive it will integrate well as a singular branch off of that table using an existing ID as an entity_ID.

 

[gemma-the-husky]

Just an observation regarding the "value" of the attribute. I can't see this anywhere in the thread.

all of the atrtrbutes have named constant equivalents.

So 10, the value for text, is equivalent to a constant called DbText

see this table. this is as good a reference as any.

http://allenbrowne.com/ser-49.html

 

[Zydeceltico]

Just an observation regarding the "value" of the attribute. I can't see this anywhere in the thread.

all of the atrtrbutes have named constant equivalents.

So 10, the value for text, is equivalent to a constant called DbText

see this table. this is as good a reference as any.

http://allenbrowne.com/ser-49.html

Thank You! So - - - How do I let Access know that I am using DAO datatypes?

Would they need to be declared at the beginning of a function or some such thing?

 

[MajP]

Thank You! So - - - How do I let Access know that I am using DAO datatypes?

Do not over think this one. I could have used plain text, vba constants, or actual values. All I did was somehow identify what the attribute stored value is (text, boolean, numeric,....). As long as you have a rule that allows you to change
"1.24" to 1.24
or
"Yes" to a boolean -1
"1/1/2019" to a real date of 1/1/2019

You can then refer back by code to do the conversion.

As I said you may never need to do a conversion. It may be fine just to read that the weld was ".23" inches. However, if you need to find the min, max, average weld length etc. you have a way to convert the text to an actual value.

In a normal design this is not an issue because every field has a data type when you make it. In the EA model you are storing all values in a single field which has to be text to accommodate all types of values.

 

[Zydeceltico]

Do not over think this one. I could have used plain text, vba constants, or actual values. All I did was somehow identify what the attribute stored value is (text, boolean, numeric,....). As long as you have a rule that allows you to change
"1.24" to 1.24
or
"Yes" to a boolean -1
"1/1/2019" to a real date of 1/1/2019

You can then refer back by code to do the conversion.

As I said you may never need to do a conversion. It may be fine just to read that the weld was ".23" inches. However, if you need to find the min, max, average weld length etc. you have a way to convert the text to an actual value.

In a normal design this is not an issue because every field has a data type when you make it. In the EA model you are storing all values in a single field which has to be text to accommodate all types of values.

I understand. Thank you. Seriously.

 

[MajP]

I understand. Thank you. Seriously

As I said this is a niche concept; however, it kind of fits what you are doing. If you are interested then explore it, although it may not solve any of your current issues. However, it is a concept worth exploring.