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

 

[Uncle Gizmo]

I haven't studied your question in-depth because I suspect your tables are not constructed properly. What I mean is I suspect you are using columns which are basically yes/no Fields. If that's the case there's not much I can do to help you, except advise you that you need to change the structure of your DB.

 

[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.

 

[Uncle Gizmo]

To give you a better idea of what I'm thinking you might like to have a look at the videos at this link:- Videos - Add a Check List to your DB --- the first video is an overview. Basically it demonstrates a system of creating a dynamic checklist in the sense that the checklist isn't created until you create the user for it. The reason for using this method of creating a checklist is you get a checklist which is "vertical" in your database, instead of horizontal (the usual method employed by beginners in MS Access). Vertically stacking the information like this just makes it very easy to query and manipulate in anyway you want really if you have your information in a tabular format like Excel then really you want to move it all back to excel, you won't get very far in MS Access.

BTW It doesn't have to be yes/no Fields, it could be date Fields text Fields anything really...

 

[Uncle Gizmo]

Why is that a problem?

Like I said I haven't studied your problem, and I don't know what your database construction is like --- however if it is in the format of columns that represent the check boxes or in your case dates then you will have difficulty in extracting useful information. It's not impossible, but you will have to write a lot of code and difficult queries, whereas if you spend a little extra time understanding how MS Access works you will get much more out of it.

 

[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.

 

[Uncle Gizmo]

However, I don't see how it addresses the problem of different types of checklists

The most important thing is the vertical structure. If you have more than 1 checklist then have a look at this set of videos "Videos - Multiple Check Lists" which follows on from the single checklist video instructions --- "Videos - Add a Check List to your DB" . You need to know how the single checklist works, and then it's a simple step to going to multiple checklists. They don't have to be checklists, they can be text boxes, Dates, memo fields and I think "combo boxes" (although I haven't worked out how to do that yet)... The vertical structure is the MOST important thing. That's the main thing! If you need a vertical table with a column of checks and next to the checkboxes you have a comment field, then that will be one particular structure. In other words you use different vertical tables for the different structures. You can see this technique in the multiple checklist examples, that is to include an identifier in the row to identify which set of Records the record belongs to. In other words your table becomes multiple tables all within one table. The identifier (1) - saying this is one set of data and (2) saying this is another set of data. However when you are first learning these techniques you might find it easier to actually use separate tables and then combine them into a single table at a later stage. I don't mean added together by Union query, but actually physically - copying all the data from the tables into a master table. Not an easy task in itself, well it's relatively simple but it's quite easy to make mistakes.

 

[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.

 

[Uncle Gizmo]

2 things, 3 things, greetings season's greetings even. Don't get too drunk! With regard to watching the videos, they are on YouTube. On your PC you have the option to watch the video at double speed.

I tend to talk quite slowly so watching them at double speed is fine, unless you can't understand my accent! I watch quite a few videos myself and I seldom find a video I can't watch at double speed. Some people do have strange accent which means I need to watch them at 1.5 or sometimes 1.

I would be glad of any feedback. Have I missed anything important? Is there something that is particularly muddled and difficult to understand? Anthting that could do with a better explanation?

Any comment criticism and advice will be received gladly.

 

[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

 

[Uncle Gizmo]

It's a while since I looked at it, so I will have to refresh my memory; and I'm in the middle of a job at the moment so I can't spend the time. Two jobs actually, doing a database and tiling the bathroom!

From memory the master table can have anything you like in it. All you need from the master table is the unique ID. As for the list table I think this should only carry the list, it will be the same list for each unique ID in the master table.

The data table needs a field to receive the list entries you are firing into it from the list table, but you can have any other fields you like in it.

 

[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?

 

[Uncle Gizmo]

Checklist in the bathroom is quite simple. Check to see if the wife's not looking and sneak in to my office and go on the computer...

 

[Uncle Gizmo]

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?

Are all the checklists within one database? A special database for creating and storing and filling in checklists?

 

[AdamFeerst]

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

 

[Uncle Gizmo]

I think we are at cross purposes. I thought your question was about the checklist database idea itself, however your question is more about adding functionality to the database? Am I correct?

 

[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?