Edit: I have been enlightened on the proper etiquette of cross-posting across multiple forums. I have also posted this question in the following places on Reddit/r/MSAccess and at UtterAccess. I can't post links yet because I haven't reached 10 posts but will add them as soon as I am able.
My reasoning for cross-posting was to try and get my issue out in front of as many eyes as possible.
Based on current feedback, I will be restructuring my tables and will update this post when I have made the changes. Who knows, I may be able to figure out my own problems.
---
Good morning. I really need some guidance. I’ll be posting for help on multiple sites and when I find out something helpful at one place, I’ll update posts on others. If at any point more detail can be helpful, please let me know. I’ll try to limit the information to what I think is important for the task I’m trying to complete at this time. The database will ultimately be used for more than this task, but this is the part that has me stuck right now. I've attached a copy of my current database.
I’m using Access 2016 64-bit. The primary goal of this database is to be able to execute equipment (typically HVAC equipment) checklists for a Project on a Windows Tablet device (i.e. Surface). Each piece of equipment will have a minimum of (2) checklists, a Pre-Functional Check (PFC) checklist and a Functional Performance Test (FPT) checklist. I have filled in a tiny bit of example data in the linked database in t_TU (terminal unit equipment) and t_TUPFCDraft and will use to explain what I’m trying to do.
When we develop a checklist, we draft a list of check items that will cover ALL of a certain type of equipment. Then when we extrapolate the list out for each piece of equipment, we’ll remove the non-applicable check items. Up to now, we’ve developed/maintained these checklists in Word. It’s important to note that these checklist items can change from one project to the next, even if it’s the same exact piece of equipment.
Example: Terminal units can be piped with 2-way or 3-way valves. So, the draft checklist will have the following (2) check items:
When the checklist is extrapolated out, if the individual unit being checked is expected to have a 2-way valve, the check item for 3-way valve will be deleted from that unit’s equipment list.
To get to this extrapolation point, I’ve done the following:
I then need this information brought into t_TUPFCField. This is where I’m getting stuck because I can’t figure out how to get this information into t_TUPFCField and then be able to edit the other fields (other fields being TUPFCApp, TUPFCPass, TUPFCNote). I’ll then use the TUPFCApp field as a checkbox for whether the check item applies to the individual piece of equipment. Then once in the field, use the TUPFCPass checkbox for whether or not the check item passes (obviously, this will all be done through forms, not in the tables).
I was also considering using a single field in lieu of the Applicable and Pass fields (a single dropdown field with PASS, FAIL, and N/A). I could then use the N/A option to drive whether or not the checklist items shows up in the report. However, it is my understanding that the dropdown list fields don't translate to the higher caliber database software. (I understand that I'm nowhere near that level, but I want to do as much future-proofing as possible).
If for any reason, I need to update a check item’s verbiage in t_TUPFCDraft, I need that update applied to all occurrences of that check item in t_TUPFCField, WITHOUT losing information in any other fields for that row in t_TUPFCField.
After all the checklists are completed, they will then be printed from a report.
Other things I think may need to be considered to develop a working solution:
Other things I’d really, really like.
I’ve tried wrapping my head around append and maketable queries, but it just hasn’t clicked yet. I have not gotten into writing SQL or VBA yet but understand it might be necessary for this. I just have no idea where to even start with that.
Note: If you DL the copy of the database that I linked, you’ll see some ludicrous forms. I don’t plan on using those, but left them these in case I needed to refer to any of their functionality. I made those at the beginning of the year before I knew any better. Once I create their replacements, the old ones will be eradicated. There are also a couple of tables that still exist, but I don’t plan on using. Again, once I’m certain they won’t be necessary, I’ll delete them.
Please feel free to ask as many questions as you’d like and I’ll answer them the best I can. If it would be beneficial for me to create mockup forms to show the end result of what I’m looking for, please let me know and I will do so. I’m going to continue doing research and try to figure this out.
If anyone would be willing to help out via Skype, Discord, TeamViewer, etc., that would be amazing and some kind of compensation could be worked out. I would also document any kind of resolution so that the community might benefit as well.
Thanks in advance!
My reasoning for cross-posting was to try and get my issue out in front of as many eyes as possible.
Based on current feedback, I will be restructuring my tables and will update this post when I have made the changes. Who knows, I may be able to figure out my own problems.
---
Good morning. I really need some guidance. I’ll be posting for help on multiple sites and when I find out something helpful at one place, I’ll update posts on others. If at any point more detail can be helpful, please let me know. I’ll try to limit the information to what I think is important for the task I’m trying to complete at this time. The database will ultimately be used for more than this task, but this is the part that has me stuck right now. I've attached a copy of my current database.
I’m using Access 2016 64-bit. The primary goal of this database is to be able to execute equipment (typically HVAC equipment) checklists for a Project on a Windows Tablet device (i.e. Surface). Each piece of equipment will have a minimum of (2) checklists, a Pre-Functional Check (PFC) checklist and a Functional Performance Test (FPT) checklist. I have filled in a tiny bit of example data in the linked database in t_TU (terminal unit equipment) and t_TUPFCDraft and will use to explain what I’m trying to do.
When we develop a checklist, we draft a list of check items that will cover ALL of a certain type of equipment. Then when we extrapolate the list out for each piece of equipment, we’ll remove the non-applicable check items. Up to now, we’ve developed/maintained these checklists in Word. It’s important to note that these checklist items can change from one project to the next, even if it’s the same exact piece of equipment.
Example: Terminal units can be piped with 2-way or 3-way valves. So, the draft checklist will have the following (2) check items:
- “Unit is piped with a 2-way valve”
- “Unit is piped with a 3-way valve”
When the checklist is extrapolated out, if the individual unit being checked is expected to have a 2-way valve, the check item for 3-way valve will be deleted from that unit’s equipment list.
To get to this extrapolation point, I’ve done the following:
- In t_TUPFCDraft, I have the category of the check item, the check item itself, and the reference for said check item. (I’ll need certain things done with the category/reference, but I’ll save that for a separate post.)
- In t_TU, I store all the pertinent information for each terminal unit.
- To extrapolate all the drafted check items to the equipment, I perform a cross-join query between those 2 tables.
I then need this information brought into t_TUPFCField. This is where I’m getting stuck because I can’t figure out how to get this information into t_TUPFCField and then be able to edit the other fields (other fields being TUPFCApp, TUPFCPass, TUPFCNote). I’ll then use the TUPFCApp field as a checkbox for whether the check item applies to the individual piece of equipment. Then once in the field, use the TUPFCPass checkbox for whether or not the check item passes (obviously, this will all be done through forms, not in the tables).
I was also considering using a single field in lieu of the Applicable and Pass fields (a single dropdown field with PASS, FAIL, and N/A). I could then use the N/A option to drive whether or not the checklist items shows up in the report. However, it is my understanding that the dropdown list fields don't translate to the higher caliber database software. (I understand that I'm nowhere near that level, but I want to do as much future-proofing as possible).
If for any reason, I need to update a check item’s verbiage in t_TUPFCDraft, I need that update applied to all occurrences of that check item in t_TUPFCField, WITHOUT losing information in any other fields for that row in t_TUPFCField.
After all the checklists are completed, they will then be printed from a report.
Other things I think may need to be considered to develop a working solution:
- There will occasionally be check items where when filling out the checklist, I will need a field-entered value (as in out in the field, not an Access field). For example, the check item may be “Record current outdoor air conditions” and I’ll need a spot to be able to fill those in.
- Another common check item is “Installed unit is in accordance with the approved equipment submittal” and then the model would be pulled from the respective equipment table.
Other things I’d really, really like.
- If I get this figured out, using this method, I’ll still need to have a *PFCDraft and *PFCField table for each type of equipment. Ideally, I’d like to have only one PFCDraft and PFCField table and somehow use a field to relate them to the different types of equipment.
- If the field where I write the question can support Rich Text so I can use Italics and/or bold to emphasize things, that would be great, but it is by no means a 100% necessity.
I’ve tried wrapping my head around append and maketable queries, but it just hasn’t clicked yet. I have not gotten into writing SQL or VBA yet but understand it might be necessary for this. I just have no idea where to even start with that.
Note: If you DL the copy of the database that I linked, you’ll see some ludicrous forms. I don’t plan on using those, but left them these in case I needed to refer to any of their functionality. I made those at the beginning of the year before I knew any better. Once I create their replacements, the old ones will be eradicated. There are also a couple of tables that still exist, but I don’t plan on using. Again, once I’m certain they won’t be necessary, I’ll delete them.
Please feel free to ask as many questions as you’d like and I’ll answer them the best I can. If it would be beneficial for me to create mockup forms to show the end result of what I’m looking for, please let me know and I will do so. I’m going to continue doing research and try to figure this out.
If anyone would be willing to help out via Skype, Discord, TeamViewer, etc., that would be amazing and some kind of compensation could be worked out. I would also document any kind of resolution so that the community might benefit as well.
Thanks in advance!
Attachments
Last edited: