Yes/No field v.s. Other options

[DavidCB]

Greetings,

Wondering if I am on the right track, how Ugly , if at all, is my Ugly Idea. What am i missing?

Creating a DB that tracks Device Programing errors. The possible Errors "should" not deviate from 10 max list, Text1, Text2, Text3.... Text10

The idea is to generate a report (email), listing the items that need attention in the body of the report (email) with some canned text, (80% of time a device has no more than 4 errors)



Each Device will have one open occurrence , and the record will not need to be cross referenced or calculated in any further functions.

Good idea [Device + 10 error fields] - create a Table to include Device and 10 fields one for each Error, with a Yes/No Form with each error, then build the report via a query ?

Bad Idea [Device + 1 error field/ combo-box] - create Table to include Device and only one error field, and build "some kind" of continuous form with Device on the header,... "some kind" is the unknown part ?

Ugly Idea [Device + 10 open fields/ combo-box] - create table to include Device, and 10 fields that could accept any of the 10 Errors, build form with 10 drop-downs. The appeal of this is "normally" there are 3/4 Errors per device. And all that would be needed is join them in the message body of a report.

Have a tendency to over think it, trying to keep it simple, Any direction would be very much appreciated (guess this could have been posted under Table Design ).

Thank you,

[Micron]

Bad idea - create a Table to include Device and 10 fields one for each Error,
This is Excel brain thinking and does not make for good db design. You will only make it harder to get info in and out. Seems like your "device" is the entity, so all the attributes of the device go in that table (serial number, weight, height, whatever). Attributes that repeat go into their own table(s) so that 4 defects are listed as records, not fields. The link between the two is the primary key (usually autonumber id) becomes the foreign key value in the related table (which, by the way, has it's own PK field). I think you'd be wise to learn db normalization, as your "Bad Idea" is actually the correct approach.

[June7]

Agree with Micron on data schema. Deviating from that normalized structure would need some strong justification which I don't see in your description.

As for including data in body of email - that will most likely involve VBA looping through a recordset to build a tabular presentation.

[CJ_London]

Agree with Micron and June - the Bad Idea is closest to the way forward and is the simplest from a database management/report generation perspective. You can create a report which can then be emailed. I would expect two or possibly three tables - 'device table', 'errors table' and a linking table. You would need three tables if your errors can repeat across multiple devices and are not unique to each device.

[ssanfu]

@DavidCB,

One other point:
You stated

"The possible Errors "should" not deviate from 10 max list, Text1, Text2, Text3.... Text10".

Using the "normalized" design provided by Micron, June7 and Ajax gives you an almost unlimited number of errors - not just a max of 10.

Have a main form for Device Programing info and a sub form for entering the errors. In the sub form, you can have a combo box to select the error type(s).


Good luck with your project......

[DavidCB]

"This is Excel brain thinking" , Ha ...true

" 'device table', 'errors table' and a linking table." ..Hmmm

Thank you, thought this might be the way to go, will take this route :-)