Solved Creating a db thoughts (1 Viewer)

[Pmetro]

Good day all. I am wanting to create a db and I have had difficulties with the queries, because of the MVF in my test db. This is the scenario: I am to evaluate the condition of a piece of equipment, if it is good, no problem. But, if it is bad, I am to assign defect codes for each defect. Each code would look like this....##-##-##. The first 2 digits are a location, the second set are a system and the third a sub-system. Sometimes there my be up to 20 or more codes. I have tried to input these codes into a MVF separated by a comma, but found that my queries weren't searching the entire string in that field for a particular system (second set of digits) or the query brought up incorrect results. What suggestions does anyone have about how this db should be created? I am open to all and any suggestion. Thanks.

 

[arnelgp]

if you are Unfamiliar of what an MVF is, just avoid it and use Junction table instead.
for those code, it is better to use Separate fields for each code (one field for location code, another for system code, etc.).

there should be atleast:
master table for location.
master table for system/sub-system or
another table for sub-system.

 

[LarryE]

If you have pieces of equipment that may have multiple defect codes, then you need a separate defect code table that has a foreign key linked to the equipment ID number in the equipment table. Then you create separate codes for each piece of equipment defect.

The defect code table should have a DefectCodeID field, a DefectCode field and perhaps a DEfectCodeNote field. I would not attempt to use a Number Data Type for the DefectCode field. Use a Text Data Type.

 

[jdraw]

As arnelgp suggested---don't use MVF.
Suggest following set up
tblEquip---->tblEquipHasDefect<----tblDefect

you may also want to include a Datestamp for when the defect was identified - depending on your requirements.

 

[The_Doc_Man]

Adding to the other voices on this subject, a multi-valued field looks all neat and spiffy - until you need to actually do something complex with it. Here is the simplest test for the need to use something OTHER than an MVF. When you generate a report, do you have to translate the meaning of one of the codes that you stored on a form? You can do it with an MVF but it is FAR easier to use a separate lookup table that you can EASILY reference from a combo box and that you can easily translate using a query with a JOIN to that table. But you can easily use that translation table ANYWHERE in ANY CONTEXT, precisely because it is so easy to build that JOIN.

Also, concur with arnelgp regarding having separate tables for the parts of your code. You can build the parts and then easily concatenate them once they are built. Trying to build a complex encoded string is easier if you divide and conquer - by building individual elements before actually combining them.

Also concur with JDraw in that he is suggesting what we call a JUNCTION table. You can search this forum for that term if you are not familiar with it. The SEARCH function is at the upper right, near where your member-name shows up in the forum's menu bar.

 

[Pat Hartman]

Also, do not mush multiple attributes into one field. If the defect code has three separate parts, use three separate fields. You will thank me later when you get to the point where you need to do analysis of the defects.

 

[Pmetro]

Thanks to everyone for their advice. I agree, the MVF is simple to use ....just plug in 01-02-03,05-08-35,19-10-35 etc... But when it came to a query to identify for example how my ##-05-## defects there were....I couldn't make it work easily. Correct me if I misinterpreted, so each defect code would need to be a single record line too?

 

[Pat Hartman]

Each defect is a separate row in the table and each part of the code is a separate field so the table would be:

DefectID (autonumber PK)
ProductID (FK)
Location
System
SubSystem
Notes

You can display the defect code however you want to but store the parts separately to make analysis easier.

 

[arnelgp]

here is a simple database for your reference.

 

[Pmetro]

Thanks for the db. I used it as a template and created one similar.

 

[jdraw]

Interesting examples in the database.

 

[isladogs]

More info on MVFs:

Multivalued Fields

Multivalued fields (MVFs) were introduced with Access 2007. This article explains what MVFs are and how they can be used. However, most experienced developers avoid them completely. This article explains why this feature is really not a good idea

www.isladogs.co.uk

 

[Pat Hartman]

Nice article but I'm confused. Isn't the same method used to define both a table level lookup and an MVF. If you choose the from a table option, you end up with a table level lookup. But the other option leads to an MVF?

 

[isladogs]

No - there are 2 methods of creating the data for an MVF field; value list or lookup from a table/query
In each case you start with normalised data

That part of the second method is indeed basically the same as creating a table level lookup

However, to create the MVF, you then need to set Allow Multiple Values = Yes using the wizard or the lookup tab on the main table field.
Doing so triggers the creation of the deep hidden system table to store the normalised data with the MVF version being saved in the main table field.

Hope that's clear.
I do this so rarely that I had to check the details again before replying

 

[Pat Hartman]

I didn't realize there was a second step. I probably haven't created one since 2006 when I was doing beta testing on 2007.

 

[Pmetro]

Thanks for the article. I wish I had read that a few days prior. It would have prevented a lot of frustration during my query testing.

 

[Pmetro]

I would like to thank everyone again for their assistance with this. The db is up and running without MVFs. My manager asked why didn't I allow for them and I then explained the difficulties of queries. I followed Arnelgp simple design and created other queries and reports. My lastest question regards the reports. The reports show the associated SystemID and SubSystem ID numbers not the description. Is there a why to alter that so my reports show the description and not the ID number?

 

[arnelgp]

use query.
on the query join your table with your lookup tables (see Query2 on demo).
now use the query (query2) for your report (see query2 report).

 

[Pmetro]

I opened up to review your relationships. I don't have the MSys relationship stuff.....what is that? I created the relationship around the DataEntry only.

 

[Pmetro]

The tables in my db are linked tables from another db.....is that a concern when creating relationships?