Flexible Checklist Scheme

[jpa]

Hi, I'm trying to put together a checklist that is flexible enough to have varying datatypes for the results of each checklist item. For example, I'd like a table structure that would allow me to create an editable query that might allow:

Went to the Store: yes/no
Bought Bread: yes/no
Cost of Bread: $4.00
See by date of bread: 11/27/02
Paid cash: yes/no

It would be simple to build a big table with all the items hardcoded on the list, but I want to normalize it into checklist items all related to a checklist. The difficulty comes in when I want each item to have results that are different datatypes and I have to predefine somewhere in another table what the results could be.

In addition, I want to set up template checklists which pre-populate the items on the list, but leave the results open.

I've already created a structure that, I think, covers these requirements. I'm just not sure that I can build the queries/forms necessary to populate the lists.

I can e-mail an example of my first attempt if that would help this make more sense. This is just a small part of a much larger, existing database.

-Thanks

 

[Frink]

Hallo,

You could do it by having an item table (tblItem) to contain a lngItemId autonumber and any fields which apply to all items (such as name)
Then I'd create other tables, one for each Item Type,
ie tblItemBoolean which would have a lngItemId number field to link it to tblItem and fields appropriate to that data type, (such as value, units etc.)

Actually, thinking about it, that sounds a bit hellish. I'd have a single table, tblItems with strName, strValue and intType fields.
strValue would be a 'plain language' representation of the data and intType would indicate how to interpret it.

ie. if intType was cintDate then you would use CVDate(strValue) to retrieve the data.

Templates could be held as a separate Template table with multiple TemplateItems.

- Frink