record value in table when one of many buttons are clicked

[Zydeceltico]

Hi All -

I have a main form bound to a main table. Eight buttons on frmMainForm open eight different popup forms. Each of those popup forms are bound to a unique table. Each of them represents a unique type of inspection.

In frmMainTable I have field called InspectionType_FK that has a datatype of Integer.

I also have a lookup table called tblInspectionType with 2 fields: the autnumber PK (InspectionType_ID) and InspectionType as a string (e.g., Mill, Assemble, Weld, etc...)

So I know I could put a combobox on frmMainForm and have the user select which type of inspection they are going to do, immediately grab that value to be stored in my field InspectionType_FK and then click a button to open the inspection.

However - - 1) I think that is prone to create errors and 2) I think it would be much more elegant to NOT have a combo box at all, the user clicks one of the eight buttons to open an inspection form, and in the background a uniqque numeri value corresponding to InspectionType_FK is captured to be placed in tblMainForm's InspectionType_FK field.

I think I am working this out as I type right now but I am sure I have wholes in my logic.

So I think I need to create a variable in frmMainForm called intInspTyp As Integer. The idea being that when one of the buttons is clicked code runs that places a value in intInspType and then later when frmMainForm is saved the InspectionType_FK value is recorded. I'll have an invisible txtBox to hold the value actually instead of a declared variable.

What I don't know how to do is enumerate the behind-the-scenes values for each of the buttons corresponding to the lookup table of inspection types.

The reason I want to do this is to be able - at a later date - to easily review sets and subsets of ALL inspections of given type.

Thoughts? I'm guessing that a solution might involve Select Case and some If/Else If statements (which I have never attempted) OR one of ya'll as is typical can inform me that I making this too complicated for myself!

Thanks,

Tim

 

[theDBguy]

Hi Tim. Has anyone ever mentioned to you that you might have an improper table structure? Having separate tables for different types of inspections doesn't sound like it's the right approach. You can easily query a single table to display a specific type of inspection by simply applying a criteria to it. In any case, you could also look into TempVars if you want to use variables between different objects (forms, queries, or reports).

 

[MajP]

Has anyone ever mentioned to you that you might have an improper table structure?

We have, but once seeing his fields the different types of inspections have little if anything in common. Several people suggested doing this as an Entity Attribute model which would likely be a good design, but that is pretty complex for someone who has never designed that way.

 

[theDBguy]

We have, but once seeing his fields the different types of inspections have little if anything in common. Several people suggested doing this as an Entity Attribute model which would likely be a good design, but that is pretty complex for someone who has never designed that way.

I see... Maybe a project for another day then... Cheers!

 

[Zydeceltico]

Hi Tim. Has anyone ever mentioned to you that you might have an improper table structure? Having separate tables for different types of inspections doesn't sound like it's the right approach. You can easily query a single table to display a specific type of inspection by simply applying a criteria to it. In any case, you could also look into TempVars if you want to use variables between different objects (forms, queries, or reports).

Yes - it has been mentioned to me that I may have improper table structures.

I have explored that ad infinitum. I just can't wrap my brain around having one large table with so much disparate data collected in it. I mentioned in another post somewhere the truism about a craftsperson knowing what tools they have on hand and more improtantly HOW to use them. There is a conceptual model that many of you with much more experience than I have understand reflexively. I've been told something along the lines of "more rows, less columns" or something like that - - which I cannot grasp. Its also been mentioned to me to use an Entity Relational Model - - which - - doesn't feel right either.

I get normalizing - - a heck of a lot better than last year. plug set me straight on that.

I've attached a snapshot of my relationships and the db in its current developmental state.

I can say this - I am already able to get out of it what my supervisors want to see and - more importantly to me (with my very limited experience) I believe it is robust enough to allow me to modify and add when needs change without having to rebuild. Yes - I pass a lot of values but......I understand it.

That is not so say AT ALL - if you are able to explain a more effective/efficient table structure after looking at all I need to collect - particularly around weld tests and compared to all of the other fields - I would be really happy to give it a go. I am still developing this and want the final product to be as best as it can.

But every time the subject of my "too many tables" comes up I immediately have this vision of hundreds of fields in a single table with the vast majority of "cells" being empty at all times - which doesn't seem like good practice either.

Like I said - for some reason I just can't wrap my head around a different way to setup the inspection tables as an aggregated single table. It doesn't make sense to me - but my knowledge is limited.

Tim

 

[Zydeceltico]

For anyone perusing the db itself - I should add under the Mill Inspection Branch - that is only about half of the final number of fields. Eventually, there will be a part drawing for each specific part that we make (which are all very different from each other) where I will be collecting 5 -10 more key pieces of data related solely to that unique part. The same goes for the Welding branch of inspections.

Again - if anyone is able to take the time to help me understand what a different table structure looks like - I am all ears.

Thanks a bunch!

Tim

 

[MajP]

But every time the subject of my "too many tables" comes up I immediately have this vision of hundreds of fields in a single table with the vast majority of "cells" being empty at all times - which doesn't seem like good practice either

I think what you have is pretty correct approach for where you are at, since your different type of inspections have very specific fields for that inspection.

However, what you have is a very good candidate for an entity attribute design. You would end up with a single table with very few fields and a lot of records.
https://en.wikipedia.org/wiki/Entity–attribute–value_model

That ship has sailed. At this point a rework would be painful. This approach may have reduced a lot of work especially in the code, queries, and number of forms. However, this design has to be done up front.. However, if you have never done this approach, the design can be complex to understand.

 

[Deleted member 147267]

Since you mused about complication, I'd say yes, you are making it more complicated, and perhaps object heavy as well. Don't know the history, but sometimes 1 form opened with a specific recordsource rather than multiple similar forms is less work - especially if you have to modify one aspect of the operation. You could end up doing x times the work when you could do it once. As for "InspectionType_FK that has a datatype of Integer." related fields are usually created as Longs, not integers (when the PK is an autonumber, because the AN is a long.
Since you seem more interested in button driven decision making rather than a combo (counter-intiutive to me to have 8 buttons rather than one combo) then you could pass any value you want to the opening form and there's lots of different ways to get that value and get it to where you need it:
- recordset field value
- DLookup
- button property such as .Tag or character from the name (e.g. "1" from txtInspection1)
- property of the called form
- control value on the called form
Any method invoked from the main form could be passed as the OpenArgs parameter of the calling form. Most of those ideas I don't care for as they require certain things to be in sync, such as part of the button name vs the inspection ID value. That's one reason why a combo is probably the best method. Add an inspection type and there's far less that needs to be modified than any other method you could come up with - aside from a listbox, which is probably overkill.

 

[theDBguy]

...I just can't wrap my brain around having one large table with so much disparate data collected in it.
...

I've attached a snapshot of my relationships and the db in its current developmental state.
...
But every time the subject of my "too many tables" comes up I immediately have this vision of hundreds of fields in a single table with the vast majority of "cells" being empty at all times - which doesn't seem like good practice either.

Like I said - for some reason I just can't wrap my head around a different way to setup the inspection tables as an aggregated single table. It doesn't make sense to me - but my knowledge is limited.

Tim

Hi Tim. A "proper design" doesn't necessarily mean one huge table with hundreds of fields. I believe an entity-attribute design that MajP mentioned would actually use at least three (maybe four) tables. All inspection types would go in one table, all attributes for all inspection types would go on another, then a junction table between attributes and inspection types would be needed to tie those two together. The fourth table would then record the values/results of each attribute for every inspection. I'll see if I can find time to review your attachment and let you know if I could come up with anything new.

 

[Deleted member 147267]

I didn't get the comment about "too many tables" at first, thinking it would not be rare to find properly designed db's with scores, if not hundreds of tables. I took a look and now I get it. It's not so much that there's too many as it is that they're fractured and there's more than there needs to be. I took a quick look at the entity-relationship link; it presents a more complicated discussion than what I'm used to seeing. I just liken it to knowing what the "thing" is (entity) and what its attributes (characteristics) are. If you can grasp the concept you're well on your way to reducing/eliminating problems that are largely due to improper table relationships. F'rinstance, a work station would likely never be an attribute of a Part (as you have it). There are Parts (with part numbers, stock locations, drawing numbers, etc etc) and there are Workstations where parts might be made. To join the two requires a linking table of some sort because Workstation is not a characteristic of a part any more than my car is an attribute of me. At least I think I can say that in general terms.

After a quick look, I made the following notes. I have company showing up for a few days, so may be limited in how much I could offer in the very short term.

no way to connect coating supplier with the coating type they provide
should be a table of "suppliers" with ComdtyType field, not a table for a specific coating/commodity
no need for decimals table. tblMeasureVals with fields for decimal, fractional, metric, guage etc., assuming only linear values are required
repeating values in linestopreasons, coilrejectreasons, coilchanges - possibly valid as is, depending on the business at hand but looks suspect
personnel table: better to have inactive and/or active date fields. Y/N field provides little value
some tables could be eliminated in favor of lookup lists (e.g. where there are only 3 records) if table count was really an issue
don't quite get the AssemblyComponents table. Looks like a junction table but there's no associated Assembly or Component table
an InspectAssemble table with mostly N/A values is curious
tblInspectionTypes may be a lookup table, but doesn't follow the naming convention if it is
don't understand tblInspectPainting - looks like it's column based, like a spreadsheet
ditto for InspectWeldTests, LineStop but those might just be "first impressions"

 

[Gasman]

Even as inexperienced as I am, I'd be going for a combo for Inspection Type and the one button to open the relevant form.
The form name would be part of the record for the combo (column 2?)
That way, if another inspection type comes up, no change in coding required.?
If the form needs changing, just change a record.?

 

[Mark_]

In frmMainTable I have field called InspectionType_FK that has a datatype of Integer.

For myself, this is the reverse of how I would code this.

Each inspection would hold the ID for the record in the main table it is attached to. You then do a query on each type of inspection that returns matching values.

 

[Zydeceltico]

Hi Tim. A "proper design" doesn't necessarily mean one huge table with hundreds of fields. I believe an entity-attribute design that MajP mentioned would actually use at least three (maybe four) tables. All inspection types would go in one table, all attributes for all inspection types would go on another, then a junction table between attributes and inspection types would be needed to tie those two together. The fourth table would then record the values/results of each attribute for every inspection. I'll see if I can find time to review your attachment and let you know if I could come up with anything new.

Hi DB - I understand your explanation of the structure. The light bulb just became a little brighter.

I am particularly curious about how I would define "attributes" in a functional way. I am thinking of the current various fields on say frmInspectMill versus the plethora of fields for any weld test. If I can figure out a way to operationalize what that looks like I might be able to finally make sense of the entity-attribute model. I am really lost on how to "convert" all of my fields across all the various tables into "attributes."

Interestingly, I think I grasp the fourth table pretty clearly.

I'm just stymied as to how to functionally create the front end for something of which I don't understand the building blocks of the back end.

So - yeah - if you are able to find a little time - I'd be most grateful!

I should add: The way my ADHD brain works is that even though I've been told I am not supposed too think about how I am going to create forms and present data and and data entry through the eventual GUI BEFORE I build the table structure, I can't help myself. II am also a blacksmith and I am also always thinking about how to make tools to produce a thing I need for a job. So I am a little stuck with the entity-attribute idea because I cannot fathom how I am going to create forms that produce the work I am after. Unfortunately I "get" the morph-from-a-spreadsheet concept too well. But I am really open to learning this new tool - entity attribute.

Thanks,

Tim

 

[Zydeceltico]

I didn't get the comment about "too many tables" at first, thinking it would not be rare to find properly designed db's with scores, if not hundreds of tables. I took a look and now I get it. It's not so much that there's too many as it is that they're fractured and there's more than there needs to be. I took a quick look at the entity-relationship link; it presents a more complicated discussion than what I'm used to seeing. I just liken it to knowing what the "thing" is (entity) and what its attributes (characteristics) are. If you can grasp the concept you're well on your way to reducing/eliminating problems that are largely due to improper table relationships.

I'm looking at the link right now and they use this analogy:

"

• The "entity" is the sale/transaction id — a foreign key into a sales transactions table. This is used to tag each line item internally, though on the receipt the information about the Sale appears at the top (shop location, sale date/time) and at the bottom (total value of sale).
• The "attribute" is a foreign key into a products table, from where one looks up description, unit price, discounts and promotions, etc. (Products are just as volatile as clinical findings, possibly even more so: new products are introduced every month, while others are taken off the market if consumer acceptance is poor. No competent database designer would hard-code individual products such as Doritos or Diet Coke as columns in a table.)
• The "values" are the quantity purchased and total line item price.

"

Which I sort of get for what it is - but I still am unable to make the leap to my own situation for some reason.

, a work station would likely never be an attribute of a Part (as you have it). There are Parts (with part numbers, stock locations, drawing numbers, etc etc) and there are Workstations where parts might be made. To join the two requires a linking table of some sort because Workstation is not a characteristic of a part any more than my car is an attribute of me. At least I think I can say that in general terms.

Right - a workstation would never be an attribute for a Part BUT a part could be an attribute of a workstation. We have zero parts that can be made on more than one unique workstation. Maybe I'm confusing myself here. In other words Part123 can ONLY BE PRODUCED on Mill 9. MIll 9 makes other parts also but those parts can only be made on Mill 9 also.

After a quick look, I made the following notes. I have company showing up for a few days, so may be limited in how much I could offer in the very short term.

Thank You so much for taking the time you already have!

no way to connect coating supplier with the coating type they provide
should be a table of "suppliers" with ComdtyType field, not a table for a specific coating/commodity

Various subsets of coatings are available from various coating suppliers. Some provide one kind of coating while others provide multiple types of coating.

no need for decimals table. tblMeasureVals with fields for decimal, fractional, metric, guage etc., assuming only linear values are required

You're right tblDecimals should be deleted. All other - only linear values are required.

repeating values in linestopreasons, coilrejectreasons, coilchanges - possibly valid as is, depending on the business at hand but looks suspect

I've grappled with this one specifically because a coil change is in essence a line stop but a line stop the way I am defining it and measuring has far more time/cost effects/disruptions than a coil change which is a standard well-oiled practice operationally - most importantly a coil change is expected whereas a line stop is not.

personnel table: better to have inactive and/or active date fields. Y/N field provides little value
some tables could be eliminated in favor of lookup lists (e.g. where there are only 3 records) if table count was really an issue

don't quite get the AssemblyComponents table. Looks like a junction table but there's no associated Assembly or Component table

It's a grouping table - a feral version of a junction table. 99% of what produce is made up of 2 parts which when welded together make a Final Product. ALSO & HOWEVER - any Part can also be sold as a Final Product. This table was my solution for not only combining 2 Parts to make a Product but likewise to pass through Parts as Final Products themselves. Overbuilt or clunky as it may seem - it queries very, very well for me.

an InspectAssemble table with mostly N/A values is curious

This is where I begin to want to know more about the entity-attribute model - because yes it seems odd to me too that I have defaulted all of the fields to N/A. What it boils down to is that those fields represent features of 75% of all final products but may or may not be present in all of them.

tblInspectionTypes may be a lookup table, but doesn't follow the naming convention if it is

It's a lookup table. I just made it today and yes - dropped the ball on the naming convention - which I'll be fixing.

InspectPainting - looks like it's column based, like a spreadsheet
ditto for InspectWeldTests, LineStop but those might just be "first impressions"

InspectPainting and InspectWeldTests are very much like a column based spreadsheet. I can see how one could say the same thing about tblLineStop.

 

[Zydeceltico]

For myself, this is the reverse of how I would code this.

Each inspection would hold the ID for the record in the main table it is attached to. You then do a query on each type of inspection that returns matching values.

I do that with InspectionEvent_FK. That is passed from frmMainForm to the unique inspection form.

I just thought it would be a good idea to add another value (InspectionType) as further queriable definition ("queriable" - spellcheck is telling me that I am making that word up!

 

[Zydeceltico]

this design has to be done up front.. However, if you have never done this approach, the design can be complex to understand.

Hi majP - yeah - I really did give it a go with the entity-attribute idea months ago - and due to a major lack of experience became rapidly overwhelmed.

 

[Zydeceltico]

Since you seem more interested in button driven decision making rather than a combo (counter-intiutive to me to have 8 buttons rather than one combo) then you could pass any value you want to the opening form and there's lots of different ways to get that value and get it to where you need it:

....which is the majority of my reasoning for working with buttons rather than a combo box .... I somehow "get" the various means you mentioned to pass values around when need be.


I might also add that (re: the sync issue you bring up) that I don't think I have a lot of what I would consider syncing issues because really once the user (me and two other guys) start down one inspection type branch of the tree there is no "wandering off the path." It's all really quite linear. STart at the trunk, move to the end of a single branch, return to the trunk , go down a different branch to the end, etc., etc.

 

[Deleted member 147267]

but I still am unable to make the leap to my own situation for some reason.

No wonder with that explanation. What you posted is more about providing examples rather than explaining the concept so that you can know the difference and how to apply the concept to a design.

The "entity" is the sale/transaction id

absolutely not unless it is native to the Sales table, and even then I'd argue it is not if it is an autonumber. If it was a Sales Number or PO number being used as a PK then maybe. IMHO, an entity is a "thing" that can be described by its attributes (characteristics). It would never be considered an attribute or entity as a foreign key. Can the value be used to link to another related table? Of course, but it if your name is Joe, "JOE" is not you, it is an attribute of you. Whether you use that value as a fk or the autonumber ID field of tblPersons is a matter of design choice.

See if this stuff on normalization and diagramming the relationship helps:

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com/2017/04/entity-relationship-diagramming-part-i.html
and/or
http://holowczak.com/database-normalization/

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

BUT a part could be an attribute of a workstation

This could be an example of where a business process dictates the distinction between which is the entity and which are the attributes. This is why they say "Normalize until it hurts, denormalize until it works.". It means taken to the extreme purist point of view, absolute normalization can render a db useless or just hard to work with. For this case, I'm going to say no it can't, just to illustrate (again) part of the concept. A part is a part. It has, among other attributes, a part number. A workstation is a physical location. Can you sell it like you can a part? No. Does it have a part number? No. Do you make several workstations and stock them? No. How can it be a part then? It matters not that there is a one to one relationship between a part number and a workstation. HOWEVER, you could have a linking table (did I not use that term in a prior post?) where you link tblStation to tblPart by having a tblStationPart. IF it should ever happen that a station could retool to make a different part, or even just that the part number changes due to outside influences, you are in a spot of trouble with your approach. You could easily lose historical data by adapting to the changes due to your design. What if that outside influence was for example, safety related intervention? You'd need to keep the historical data. Now what, create new tables like tblWorkStationVersion2?

Various subsets of coatings are available from various coating suppliers. Some provide one kind of coating while others provide multiple types of coating.

Regardless, I don't see how you've got that fact covered in your designs.

I've grappled with this one specifically because a coil change is in essence a line stop but a line stop the way I am defining it and measuring has far more time/cost effects/disruptions than a coil change which is a standard well-oiled practice operationally - most importantly a coil change is expected whereas a line stop is not.

Another example of where you need to define the entity properly. Is it a delay, as in tblDelays (with field DelayType)? Entities don't have to be nouns. Do you need a related table tblProductionDelays, capturing type such as line-related, coil related, etc.? This is how I see elimination of repeated data being accomplished.

ALSO & HOWEVER - any Part can also be sold as a Final Product.

Then there should be a part# for an assembly AND for each part that can be sold individually, or maybe a table for assemblies as a sale item and one for parts as a sale item. Or do you put them all in one table? Suppose you manufactured kitchen stoves. Is the stove a part with model number an attribute, along with every part you sell belonging to a stove? Or is there a PartSales table and a PartsTable and an AssemblySales table? These decisions are best made when you understand normalization and EA relationships. When you are developing table relationships, large paper with pencil is still your best friend, even in this day and age. When mapping it out, Normalize until....

This is where I begin to want to know more about the entity-attribute model

Rather than me write a book (too late?) and you/me beat this horse to death, check out the links in this post. Go over the info as many times as it takes for it to sink in. If it doesn't absolutely clear things up, find info that speaks to you until you have a divine revelation. Then try designing something on paper (I use sheets about 24x30 inches) and see how it goes. Make versions rather than trashing everything that isn't perfect right away, as parts of versions can be cobbled together to make a good overall design. For any of that to work, you MUST understand the business, which at this point leaves me and probably any well meaning person at a disadvantage. Thus some opinions on what to do may be wrong here, but AFAIC, what an entity IS and what its attributes ARE as stated are things that I stand by.

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.

 

[theDBguy]

Hi DB - I understand your explanation of the structure. The light bulb just became a little brighter.

I am particularly curious about how I would define "attributes" in a functional way. I am thinking of the current various fields on say frmInspectMill versus the plethora of fields for any weld test. If I can figure out a way to operationalize what that looks like I might be able to finally make sense of the entity-attribute model. I am really lost on how to "convert" all of my fields across all the various tables into "attributes."

Interestingly, I think I grasp the fourth table pretty clearly.

I'm just stymied as to how to functionally create the front end for something of which I don't understand the building blocks of the back end.

So - yeah - if you are able to find a little time - I'd be most grateful!

I should add: The way my ADHD brain works is that even though I've been told I am not supposed too think about how I am going to create forms and present data and and data entry through the eventual GUI BEFORE I build the table structure, I can't help myself. II am also a blacksmith and I am also always thinking about how to make tools to produce a thing I need for a job. So I am a little stuck with the entity-attribute idea because I cannot fathom how I am going to create forms that produce the work I am after. Unfortunately I "get" the morph-from-a-spreadsheet concept too well. But I am really open to learning this new tool - entity attribute.

Thanks,

Tim

Hi Tim. I still haven't had a chance to take a look at your db. But since others have already, I am not sure I should anymore, because I want to avoid muddying the waters any further. If at the end you still need help, I'll see what I can do.

In the meantime, let me just give you an example of how I applied EAV in one of my old db projects, hoping it will give you a push towards seeing how it functionally works. The project was for our IT department. They needed a database to track all IT assets: hardware and software. So, it's like an inventory system and a maintenance log in one application. Where the EAV part comes in was in the inventory area. Hardware like monitors, desktops, printers, laptops, external drives, etc. would have different attributes. For example, a computer would have memory size but a monitor won't. It would have a display size though like the laptop but not the printer. Software assets also have different attributes than hardware items.

So, if I remember it correctly, I created an Assets table to indicate the type of asset (desktop, printer, laptop, etc.). Then, I created an Attribute or Properties table to list all possible attributes for all assets (memory size, hard drive size, operating system, manufacturer, model number, serial number, printer type such as laser, inkjet, etc.). You can probably already tell I also created a junction table to assign each attribute to an asset. For example, a desktop can have memory size, hard drive size, and operating system. A laptop can have some attributes similar to a desktop but it will also include a screen size. A monitor will only have a screen size attribute, but an external drive can only have a drive size attribute assigned to it. Or looking at it another way, the attribute screen size can only apply to monitors and laptops but will never apply to desktops and external drives.

Here's an example of how that might look:

ID    Asset
1     Desktop
2     Laptop
3     Monitor


ID    Attribute
1     Memory Size
2     Screen Size
3     Hard Drive Size
4     OS Version


ID    Asset   Attribute
1     1       1
2     1       3
3     1       4
4     2       1
5     2       2
6     2       3
7     2       4
8     3       2

Hope it makes some sense...

 

[Zydeceltico]

Here's an example of how that might look:

ID    Asset
1     Desktop
2     Laptop
3     Monitor


ID    Attribute
1     Memory Size
2     Screen Size
3     Hard Drive Size
4     OS Version


ID    Asset   Attribute
1     1       1
2     1       3
3     1       4
4     2       1
5     2       2
6     2       3
7     2       4
8     3       2

Hope it makes some sense...

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.