ID certain tables by key fields/properties

[AdamFeerst]

I want to be able identify certain tables, and then run actions using only those tables. How do I do this?

Background: I have a database of checklists. They want to use this to make sure they follow consistent and complete procedures on different types or projects, and to track the progress of those projects.

Each type of project (e.g., adding a new client, adding a new product/service for an existing client, POS name change) has a different checklist/set of procedures. I've set it up so that each checklist uses a different table. There is not enough commonality of procedures across types of projects that it didn't make sense to put them all into a single table, and just use the appropriate fields. However, each of the individual checklist tables does share a few standard fields - ProjID (autonumber) and ProjType (long integer linked to an autonumber field in tblProjType) would be the unique identifiers.

What I'm thinking of doing is creating a table, tblChecklistMaster, which will be a master list of all projects. The purpose is to be able to see a list of all projects. I thought it would be easier to have a master table rather than a very long union query for a few reasons including:

1. They are a pain in the butt to write.
2. As project types are added/deleted, I'd have to rewrite the query.

My idea is to run a procedure, when the start form is loaded, that will scroll through all the tables, with each table that is a checklist source, add any new projects not in tblChecklistMaster. Can you do something like add a tag to a table property to identify those tables (in VBA)? Can you identify those tables by seeing if they contain the two unique identifying fields mentioned above?

As I'm writing this, I wonder if there might be a better way (seems to happen a lot with me). What do you think of the following?:

1. Starting each new project in a form based on tblChecklistMaster
2. Have a subform for the checklist
3. Change the recordsource for that subform based on the project type on the main form.

Any other ideas? Regardless, I'd still like to learn how to select/identify certain tables in the database.

Thanks

 

[AdamFeerst]

They are actually date fields, but function like Y/N. Why is that a problem? There are also comment fields associated with each step.

 

[pbaldy]

For the record, +1 for revisiting the design. As soon as you said "each checklist uses a different table" I suspected a problem.

If the powers-that-be adding an item to a checklist requires you to change the design of tables/forms/reports, you've got a problem. As Tony said, think vertical not horizontal. You should have 1 checklists table. Adding a new checklist or modifying an existing checklist should all be doable by users with no design changes to the db.

 

[AdamFeerst]

I've started watching the videos. It'll take some time for me to absorb the vertical structure. It's always good to learn new techniques that I can use over and over again.

However, I don't see how it addresses the problem of different types of checklists. It's not adding an item to a checklist (e.g., adding sending out thank-you notes to the wedding plans checklist), but tracking a set of completely different checklists (e.g., planning a wedding for the Smiths, planning the honeymoon for the Joneses, divorce for the Davises). It looks like I will still need a separate tbllist for each different type of project. So, once I do make each project type vertical, that then brings me back to my original question of how I bring them all together.

Thanks

 

[AdamFeerst]

I'll track the discussion, but I won't get back to the project until next Tue. Until then, Happy New Year to you Giz in 5 hours, and you Paul 1 hour after me.

 

[pbaldy]

You too! Heck, for my daughter in NZ it's already 2016. Seems like we're always last here in the west, except for those pesky Hawaiians.

I haven't watched the video(s), so if you're still confused after watching post back and one of us will clarify the structure.

 

[AdamFeerst]

Thanks. The idea for a single list makes sense, meaning I get the logic with tbls Master, List, Data, and the code of how to make them work. I'll start there - baby steps. Maybe I'll celebrate NYE watching the multiple list videos after the kids are in bed.

I know the time zones. 26 years ago, when I was living in Asia, and my parents visited me, they celebrated NYE with me in Bangkok, flew home to L.A., then went to a NY party there.

 

[AdamFeerst]

Giz,

I've watched the multiple list videos. Other than adding the Group field, that's not what I'm doing.

Each project will only have a single list. Depending on the type of project, it might use the ceremony, or honeymoon, or apparel list. How would I do that? I think I would still use the main form with a single subform. The child-master link would be on the ProjectID field (auto in tblMaster, and long in tblData. The main form would have a combo box used to select the type of project, which would then populate tblData with only those checklist items.

The next question is how to handle the comments with each checklist item. Would that be as an additional field (probably short text) in tblData? Here's what I think the data structure it will look like.

tblMaster
*ProjID - auto
Various Customer Info fields
ProjType - Long
Dates and other fields and necessary

tblList
*TaskID - auto
ProjType - Long
Task - short text
Comments - Y/N. Drives whether comments are allowed on task

tblProjTypes
*ProjTypeID - auto
ProjName - short text

tblData
*DataID - auto
ProjID - Long
TaskID - Long
Done - Date
Comments - short text

*Primary fields.

Thanks

 

[AdamFeerst]

When you have a chance, I'd appreciate it. Briefly:

The group I work with has created checklists for different types of projects. Each type of project (e.g., adding a new client, adding a new product/service for an existing client, POS name change) has a different checklist/set of tasks. Amongst other things, I want to be able to display all the different projects, of different types, from different people, on a single list.

In your last comment, what you are essentially telling me is to create a separate list table for each type of project. I could do that, but it seems easier to put them all in a single list. Then, when someone selects a ProjType of 2, only those tasks from tblList will be populated in tblData.

Do you have a checklist for the bathroom?

 

[AdamFeerst]

Yes they are all in one database. Doesn't seem to be a compelling reason not to.

 

[AdamFeerst]

Yes and no. You helped me improve how I do checklists.

However, the original question was how do I manage multiple checklists. The different checklists are not as part of the same big project as in your video examples, but different, unconnected ones.

Suppose you are a security company. There would be different checklists for, for example:

1. Setting up a basic alarm system for a new home customer
2. Setting up an advanced system (different technology) for a new home customer
3. Adding a regular security patrol for a customer
4. Setting up a system a new business customer
5. Adding a new property to an existing property management company
6. Changing the bank for automatic billing on an existing customer

Now, suppose you want to be able to manage all of the projects. That might include:

• checking the volume of type 2 projects
• checking the volume of projects for service rep Sally
• grouping projects by when they go live

Now that I know how to build a single checklist properly, this is what I'm looking to do. I think I can do this using your 3 basic table model:

• tblMaster: customer info, service rep, ProjType. Key field ProjID (auto)
• tblList: A list of tasks, with the added field ProjType. Key field TaskID
• tblData: DataID (key), ProjID, TaskID, Done

Other tables would include tblProjType (Key ProjType), tblServiceReps.

I would modify your process of populating tblData to only add those from tblList where the ProjType (similar to what you call Group) matches that selected in the main form.

Clear as mud?

 

[AdamFeerst]

I would let them use the main form. They would enter both the client's name and the proj type. The Smiths might have multiple projects of the same or different type, either simultaneously or over time. The Jones, Glenns, etc., will have projects too. The SQL to insert the tasks into tblData would look something like:

"INSERT INTO tblData SELECT " & me.ProjID & " As ProjID, TaskID FROM tblList WHERE ProjType=" & me.cmbProjType
​