Designing Checklist data structure. (1 Viewer)

Any thoughts on how to develop a database that tracks BUILDINGS in one table and links to another table that tracks DELIVERABLES. DELIVERABLES is essentially a "To-Do" or checklist that confirms the completion of each DELIVERABLES item/record required for each BUILDING record.

I have tried a many-to-many junction table, with a "COMPLETED Y/N" boolean field and "DATE" field. But a link needs to be established betwen each BUILDING record (100+) and each DELIVERABLES record (300+), before it will show up in the subform list on BUILDING form. Plus, if another DELIVERABLES record is added to its table, then a link for each building in the junction table would have to be made to the new DELIVERABLES record!

Creating the DELIVERABLES items in the BUILDING table itself, as boolean fields Y/N, won't work either. The user of the database has to be able to add more DELIVERABLE items/records and there's too many items/records (I think the limit is 256 fields).

Any help would be appreciated (even if you tell me I'm off my rocker).

Much obliged.

I think if you spend some more time studying about relating tables to each other, or relational database theory you'll get the answers you need. If you're going to be responsible for the database now and in the future, you will need to know it so invest the time. Once you get a decent grasp of it, Access becomes very powerful and very enjoyable.

To elaborate on what Found said--your database shouldn't be growing horizontally, it should be growing vertically. You don't add new columns to accomodate new data, you add new rows.

Read up on normalization (http://en.wikipedia.org/wiki/Database_normalization), and if you like post your database structure here and we can guide you.

I found another thread on this site, for those that are interested in the problem that I was defining. "Checklist How-to : Display/Storage"

I can't post the link but search using the above keywords in the General Discussion section. A quote in the thread states:

"This is actually quite difficult as a typical form/subform will only show records that you add, not records that are added as default and then shown."

So displaying a checklist is inherently difficult for a database to achieve. However a solution is proposed using recordsets, some VB code and ActiveX controls. A least its one understanding of how to solve the problem, anyways. Sample code is included. Very useful. Thanks and good luck!

See my "Checklist" Blog here:- for Videos, a presentation and code walk through...

To do a proper checklist, consider this approach.

Table of buildings. Prime key is a building ID. Table describes buildings.

Table of potential checklist items. Prime key is a checklist item ID. Table defines or describes checklist actions, conditions, qualifications, etc.

Table of checklist actions. Prime key is probably autonumber. Table includes building ID, checklist ID, date completed. You COULD include a Y/N field but don't bother.

Query of buildings, checklist items, checklist actions using an OUTER JOIN that gives you every building and every checklist and every checklist action. BUT this query includes use of the NZ function so that if a checklist item has not been recorded for a given building, you SUPPLY a "not checked" for the checklist item, and if you DO have that action, you SUPPLY a "checked" for the checklist item.

This query would use what is called a PERMUTATION join to give you every possible combination of buildings and checklist items. You would then use the ability of the query with (perhaps) an IIF to supply the check or non-check value - which doesn't actually have to exist in the checklist action table. The query can compute that on the fly.

You need to read up on the concept of PERMUTATION JOIN (also called a CARTESIAN JOIN) and you need to understand that such a query will be a huge strain on your system if you have a LOT of buildings and a LOT of checklist items.

What this does, in English, is it dynamically creates a matrix for all buildings and all checklist steps, and then consults the action list to see which steps have been done (because they will NOT have a null date) and which steps have NOT been done (because they WILL have a null date).

Then when you build the form to record a checklist completion, you need only store the building ID, checklist step ID, and date of completion. And any combination of building and checklist step that has no such record just hasn't been completed yet. If you need to define a new building, just add it to the table of buildings. If you need to define a new checklist item, just add it to the table of checklist items. And your form to remember what checklist actions are complete needs two single-choice combo boxes - one to select a building and one to select a checklist item - and one text box or calendar control to enter the date of completion of the checklist item. Then maybe a command button to save the record in the checklist action table.

BE WARNED: This approach of having a query to dynamically show what has and what has not been done must NEVER be stored to another table. Keep it ONLY as a query. Keep the building, checklist step, and completed checklist action tables - but never actually store the results of the query. (If you do, your database suddenly resembles a hot air balloon.)

Call me a glass-95%-empty-type of guy, but I don't think this new advice is going to be of much help to barnestormer who hasn't logged on to the site in 4 years. But perhaps he is still struggling with this and will help.