Yes / No Checkbox to Add Records to Another Table?

[ECCS]

Greetings All,



Forgive me if I'm asking an easy question ...

Here's what I've got: I'm creating a database to store IT assets. Every few years or so, we purge old equipment out of the facility as new equipment is brought in. So, we want to keep the discarded inventory in the database just for record keeping purposes. What I've done is in the table with the actual items, I've placed a Yes/No checkbox to indicate whether or not that item has been discarded or not. Then, I have another table that stores the date the item was discarded (DateDiscarded), the way it was discarded (DiscardMethodList), and then a memo field to provide a brief reason as to why the item was discarded (ReasonDescription). There is also an Auto-number field called "DiscardID" that can automatically create a unique ID for each discarded item. It would be a one-to-one relationship with each of the asset (items) tables.

What I would like to do, is set it up so that if the checkbox is checked, another form opens that allows us to enter in the specific discard details.

So, I have two questions:

1) How would you guys (gals) suggest that I set this up? I don't need to use VBA for this, do I?
2) How would I connect these two tables, do I need to somehow store the "DiscardID" field in both tables? Bear in mind that not all the items in the master table are going to have discarded items. So, I don't want to have a form entry showing a "DiscardID" field unless the check-box is checked and the item is being (or has been) discarded.

I would really appreciate any feedback or advice on this.

Thanks!

[jzwp11]

Personally, since it is a one-to-one relationship between the item and whether it is discarded, I would skip the yes/no field and include the discard date, method and reason fields in the asset table. On a form, you can just hide those controls until needed. You can have a button that makes them visible when you need to record the discard info. You would need some simple VBA or macro to hide the controls in the on current event of the form and to unhide them on the button click

me.controlname.visible=true (to show the control)

me.controlname.visible=false (to hide the control)

[ECCS]

That's kind of what I was thinking, combining the two tables that is ... I was just getting a little concerned about the "length" of the table. It didn't occur to me to just hide unnecessary fields until needed.

For simplicity's sake, I'm going to mark this as "Solved" and just go ahead and combine them.

Thanks for the input.

[jzwp11]

I was just getting a little concerned about the "length" of the table

Could you explain further? How many fields do you have? Can you list them? You may have a normalization issue, but we need to see what you are dealing with first. It is best to get any normalization issues resolved before you start working on forms or anything else.

[ECCS]

Here's what I've got:

[tbl-All-in-One-Machines]
* AssetID
SerialNumber
ServiceTag
Manufacturer {List select: From tbl-Manufacturers}
ServiceDate
Model
ScreenSize
ProcessorMake {List select: Intel or AMD}
ProcessorModel
ProcessorSpeed
ProcessorCores {List select: 1 thru 8}
RAM
PrimaryStorage
OS
NetworkName
IPAddress
LocationID {List select: From tbl-Locations}
Discarded {Yes or No}
DateDiscarded
DiscardMethodList {List select: Recycled, Donated, Landfill, or Sold}
Notes {Memo field for general notes about this particular system.}

As you can see, that's an awful lot of fields for one table ... but I can't see any way to normalize this any further than what it already is. All of these values would be unique to this one device, but then again ... now that I look at it, NetworkName MUST be unique across all devices on the network, so that probably needs to be put into another table so that my other tables ([tbl-Laptops] , [tbl-Servers] , [tbl-Workstations]) don't somehow end up having the same Network name. IP Address would be the same way ... so I'll have to figure out how to put the Network Name and the IP Address into separate tables for sure.

[ECCS]

Quick update: The IP address field is going to be tricky because many devices get addresses via DHCP, so I have to be able to have many devices being stored with "DHCP Assigned" in the IP address fields, but once any one device is assigned a static IP, it would have to be unique across all device tables, and there are several:

Switches, Routers/Firewalls, Workstations, Laptops, Servers, All-in-One Machines, and Mobile Devices

[jzwp11]

For those fields that you identify has "list select", are you actually using a lookup (combo/listbox) in your table? If so, I strongly recommend against that. This site has more details on the problems that table level lookups can cause. I would split the lists off into their own separate tables and then just reference a foreign key. You would use corresponding combo box in your forms to allow the lookup.

BTW, why do you have these tables:[tbl-Laptops] , [tbl-Servers] , [tbl-Workstations])? All of these things are computer systems and thus all are assets. The rule is that like data should be in 1 table


What do the fields in these other tables look like? I am guessing that they are similar to those in the table you presented.

I think you have many more opportunities for normalization.

If you consider that an asset is a combination of various types of items (cpu, processor, OS, etc.) that would lead you to a completely different table structure


tblAssets
-pkAssetID primary key, autonumber
-fkAssetTypeID foreign key to tblAssetTypes -TagNumber


tblAssetTypes (laptop, server, workstation etc. each as a record in this table)
-pkAssetTypeID primary key, autonumber
-txtAssetTypeName

tblItemType (CPU, OS, processor etc.)
-pkItemTypeID primary key, autonumber
-txtItemTypeName

tblManufacturers
-pkManID primary key, autonumber
-txtManufacturerName

A manufacturer can produce many items

tblManufacturerItems
-pkManItemID primary key, autonumber
-fkManID foreign key to tblManufacturers
-fkItemTypeID foreign key to tblItemTypes
-txtModelName
-txtModelNumber

Now an asset consists of many items

tblAssetItems
-pkAssetItemID primary key, autonumber
-fkAssetID foreign key to tblAssets
-fkManItemID foreign key to tblManufacturerItems
-txtSerialNumber

Now you have many events that take place with an asset (buy, service, discard)

tblAssetEvents
-pkAssetEventID primary key, autonumber
-fkAssetID foreign key to tblAssets
-fkEventTypeID foreign key to a table that holds events such as buy, service discard
-dteEvent (date of the event)
-txtDetail (a field to hold detail so what was done)

[ECCS]

For those fields that you identify has "list select", are you actually using a lookup (combo/listbox) in your table? If so, I strongly recommend against that. This site has more details on the problems that table level lookups can cause. I would split the lists off into their own separate tables and then just reference a foreign key. You would use corresponding combo box in your forms to allow the lookup.

I am using the "Lookup Wizard --> Type in the Values I Want" option for these. I can easily convert them to tables if that is the better way to do it.

BTW, why do you have these tables:[tbl-Laptops] , [tbl-Servers] , [tbl-Workstations])? All of these things are computer systems and thus all are assets. The rule is that like data should be in 1 table.

I agree, but I think the way I was looking at it was that each device type has different criteria that we need to record. For example, for Servers, we want to track what services are running on them. For laptops and All-in-One machines, we want to keep track of what screen size they have. I guess what I was trying to avoid was having too much irrelevant information in a single table. As an example, Workstations don't have built-in screens, and Workstations, All-in-One machines, and Laptops don't have any network services running on them.

If you consider that an asset is a combination of various types of items (cpu, processor, OS, etc.) that would lead you to a completely different table structure

tblAssets
-pkAssetID primary key, autonumber
-fkAssetTypeID foreign key to tblAssetTypes -TagNumber


tblAssetTypes (laptop, server, workstation etc. each as a record in this table)
-pkAssetTypeID primary key, autonumber
-txtAssetTypeName

tblItemType (CPU, OS, processor etc.)
-pkItemTypeID primary key, autonumber
-txtItemTypeName

tblManufacturers
-pkManID primary key, autonumber
-txtManufacturerName

A manufacturer can produce many items

tblManufacturerItems
-pkManItemID primary key, autonumber
-fkManID foreign key to tblManufacturers
-fkItemTypeID foreign key to tblItemTypes
-txtModelName
-txtModelNumber

Now an asset consists of many items

tblAssetItems
-pkAssetItemID primary key, autonumber
-fkAssetID foreign key to tblAssets
-fkManItemID foreign key to tblManufacturerItems
-txtSerialNumber

Now you have many events that take place with an asset (buy, service, discard)

tblAssetEvents
-pkAssetEventID primary key, autonumber
-fkAssetID foreign key to tblAssets
-fkEventTypeID foreign key to a table that holds events such as buy, service discard
-dteEvent (date of the event)
-txtDetail (a field to hold detail so what was done)

What you've provided here does make a lot more sense than what I've come up with so far ... I might need to go back to the drawing board on this and re-think how I'm structuring my tables. My original design did take into consideration that there could be multiple assets associated with a single manufacturer, EX: we have Dell projectors, workstations, switches, and laptops.

Thanks for your help.

[jzwp11]

As an example, Workstations don't have built-in screens, and Workstations, All-in-One machines, and Laptops don't have any network services running on them.

Instead of having a field called screen or network services, why not have the various attributes/features as records in a table and the associate the applicable ones to the asset in a junction table?

tblFeatures
-pkFeatureID primary key, autonumber
-txtFeatureName

tblAssetFeatures
-pkAssetFeatureID primary key, autonumber
-fkAssetID foreign key to tblAssets
-fkFeatureID foreign key to tblFeatures
-txtFeatureDetails (a field to capture specifics of the feature relative to the asset for example the feature may be a screen and you could put the size in the detail field)

[ECCS]

I'm working on restructuring the tables now, I've gone back to the drawing board with everything. As far as "junction" tables go, I'm not sure I know how to make those ...

Fortunately, this database doesn't have any data yet. It's going to be replacing an Excel Spreadsheet that the previous IT Administrator was (trying) to use as a database for keeping up with our IT inventory.

[ECCS]

Nevermind ... I figured out "junction" tables. My bad.

[jzwp11]

No problem.