Help with crossjoin, maketable/applen query, and then edit new table (1 Viewer)

Sam_I_Am

Registered User.
Local time
Today, 07:06
Joined
Nov 2, 2017
Messages
17
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:

  • “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

  • PROTOTYPE for Reddit.zip
    113.2 KB · Views: 27
Last edited:

Minty

AWF VIP
Local time
Today, 12:06
Joined
Jul 26, 2013
Messages
7,571
Hi Sam - You have mentioned posting in other forums - which whilst this isn't "Against the Law", it is generally seen as good etiquette to list the other posts and explain why you have posted to many different sources.

Cross posting explained https://www.excelguru.ca/content.php?184

Your question(s) is quite broad (and possibly initially too detailed) so you may be better to limit your questioning to one area of the issues at hand at a time.
 

plog

Banishment Pending
Local time
Today, 06:06
Joined
May 11, 2011
Messages
10,148
Your post and database is a lot to take in. So, while my response probably doesn't address any of your posted issues, it does deal with something you need to address immediately--I don't think your tables are set up correctly.

While I don't understand all the data, I do understand how tables are to be structured and see 2 big rules you broke:

1. Tables with the same structure. At a quick glance, t_FanTypes & t_CtrlTypes
are structured the same, and you have a bunch of tables with the same structure as t_Fan (e.g. t_PF, t_CT, etc.). When you do this you are treating the table name as an extra column--you are using it to store data. That's not how tables are to work. Instead, you need to merge all the
tables with similar/same structures into the same table with a new column that holds the value that you are currently storing in the table name.

2. Circular paths among tables. There should only be 1 way to get from one table to another. From t_EqMfr to t_Building I can trace at 9 paths. That's incorrect, there should only be 1 way to do that.

I really think you need to address those 2 issues (and to be fair, fixing #1 probably takes care of #2) before you start down the path of action queries (MAKE, APPEND, etc). Those are generally hacks around poor table structures.
 

Sam_I_Am

Registered User.
Local time
Today, 07:06
Joined
Nov 2, 2017
Messages
17
Hi Sam - You have mentioned posting in other forums - which whilst this isn't "Against the Law", it is generally seen as good etiquette to list the other posts and explain why you have posted to many different sources.

Your question(s) is quite broad (and possibly initially too detailed) so you may be better to limit your questioning to one area of the issues at hand at a time.

Thank you for education me on proper etiquette. I will update all my posts with the necessary information.

And I was worried about the broadness of my question, but I felt like I was :banghead: enough that I had nothing to lose. I take it after I make adjustments and figure out how to limit my issues, I should start a new thread for the particular issue?

Your post and database is a lot to take in. So, while my response probably doesn't address any of your posted issues, it does deal with something you need to address immediately--I don't think your tables are set up correctly.

While I don't understand all the data, I do understand how tables are to be structured and see 2 big rules you broke:

1. Tables with the same structure. At a quick glance, t_FanTypes & t_CtrlTypes are structured the same, and you have a bunch of tables with the same structure as t_Fan (e.g. t_PF, t_CT, etc.). When you do this you are treating the table name as an extra column--you are using it to store data. That's not how tables are to work. Instead, you need to merge all the
tables with similar/same structures into the same table with a new column that holds the value that you are currently storing in the table name.

2. Circular paths among tables. There should only be 1 way to get from one table to another. From t_EqMfr to t_Building I can trace at 9 paths. That's incorrect, there should only be 1 way to do that.

I really think you need to address those 2 issues (and to be fair, fixing #1 probably takes care of #2) before you start down the path of action queries (MAKE, APPEND, etc). Those are generally hacks around poor table structures.

Thank you for the feedback. I understand what you mean by using the table name as a form of storing data. Combining all the equipment into a single table shouldn't be a problem with the fields that I currently have listed for each type of equipment. Where I think I'll run into an issue is when I need to store data that only applies to a certain type of equipment. For example, I'll need to store liquid flow rates for boilers, chillers, pumps, etc., but airflow rates for terminal units, air handlers, etc. Would I have a table for each type of equipment to store data that is unique to that type of equipment?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:06
Joined
Feb 19, 2013
Messages
12,611
For example, I'll need to store liquid flow rates for boilers, chillers, pumps, etc., but airflow rates for terminal units, air handlers, etc. Would I have a table for each type of equipment to store data that is unique to that type of equipment?
depends on how unique - if the difference is purely 'air v liquid' then possibly not. Just be careful you are not building in table name as a field again.

A common way to deal with these is to have a single table along these lines

tblFlowRates
FlowRatePK
EquipmentFK
FlowTypeFK
FlowRate
FlowRateUnit


FlowTypeFK would link to a table contain a field with values such as In Port, Out Port, MinFlow, MaxFlow, whatever

FlowRateUnit would be M3/hr, CF/min whatever

and with a bit of tweaking for more generic naming it could contain other values such as volume, capacity, dimensions. Or additional fields to indicate PFC/FPT and dates.

and using a negative number for EquipmentFK can indicate that the record is a template for a particular equipment type
 

Sam_I_Am

Registered User.
Local time
Today, 07:06
Joined
Nov 2, 2017
Messages
17
Thanks. I'm definitely not the greatest at stepping back and looking at the data from a larger-picture-POV. The only thing I don't get is this part.

and using a negative number for EquipmentFK can indicate that the record is a template for a particular equipment type

How would a negative number achieve this?
 

Mark_

Longboard on the internet
Local time
Today, 04:06
Joined
Sep 12, 2017
Messages
2,111
Would it be overkill to have a table for FlowRateUnit and and relate it to the FlowRate Table with a FK?

If you update the value in a "FlowRate" table, do you want all records referencing it to change?

If yes, do so. If not, do not.

NOTE: You CAN have a table that holds values you use to fill in data. This would not have a foreign key but would still be a lookup. Think of it like a dictionary that allows you to ONLY use properly spelled words.
 

plog

Banishment Pending
Local time
Today, 06:06
Joined
May 11, 2011
Messages
10,148
I do not believe you should have tables with just one real value (autonumber primary keys, aren't real data). So if your FlowRate table is just going to have a primary key and a text field, I do not think you should have that table.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:06
Joined
Feb 19, 2013
Messages
12,611
depends on your setup but you would have a table perhaps something like this - you will probably already have a similar table used as a lookup for your equipment table as already discussed, in which case just add the last field

tblEquipmentTypes
EquipmentTypePK autonumber
EquipmentType text e.g. boiler, chiller, pump
EquipmentTemplatePK number (negative numbers only, indexed, nulls allowed, no duplicates allowed)

then link EquipmentPK to the EquipmentTemplatePK in the relationships window and make it a left join.

There are other ways, but this gives you most control.

Be aware you can use a table more than once with aliases (just drag a table onto the relationships window a second time and it will be added but with _1 appended

Note this does mean that you cannot enforce referential integrity since the parent could be in either tblEquipmentTypes or tblEquipment. As you program, you will know this and the sign of the value (+ or -) tells you which table.

So you are testing a piece of equipment. You have specified the type, so now you can get the required template records which are copied and appended to the same table for the relevant equipment - substituting the negative equipmentFK with the positivePK for the equipment, all the user has to do then is add in the values
 

Sam_I_Am

Registered User.
Local time
Today, 07:06
Joined
Nov 2, 2017
Messages
17
I do not believe you should have tables with just one real value (autonumber primary keys, aren't real data). So if your FlowRate table is just going to have a primary key and a text field, I do not think you should have that table.

ATM, my flow rate table has a PK, (3) FKs, and a number entry. (see attached screenshot) I would agree with your statement if any piece of equipment would only have (1) flow rate. However, some of the more complex units can have more than (10) different flow rates associated with it. (When reading this, it sound like I'm coming across as disrespectful and dismissive. Please know that is not my intent.)

If you update the value in a "FlowRate" table, do you want all records referencing it to change?

If yes, do so. If not, do not.

NOTE: You CAN have a table that holds values you use to fill in data. This would not have a foreign key but would still be a lookup. Think of it like a dictionary that allows you to ONLY use properly spelled words.

I'm thinking that all the flow rate values will be controlled from an Equipment level form. If I have (10) different pieces of equipment and change a flow on one of them (due to a change introduced by the engineer), I wouldn't want the flow to update on the other (9) pieces of equipment. My thought process may change when I need to update the flow values on 100-someodd pieces of equipment, though :D

Also, is the lookup you're referring to the same type mentioned in the 2nd Commandment? (I'm not trying to be a smart***, genuinely curious.)

depends on your setup but you would have a table perhaps something like this - you will probably already have a similar table used as a lookup for your equipment table as already discussed, in which case just add the last field

tblEquipmentTypes
EquipmentTypePK autonumber
EquipmentType text e.g. boiler, chiller, pump
EquipmentTemplatePK number (negative numbers only, indexed, nulls allowed, no duplicates allowed)

then link EquipmentPK to the EquipmentTemplatePK in the relationships window and make it a left join.

There are other ways, but this gives you most control.

Be aware you can use a table more than once with aliases (just drag a table onto the relationships window a second time and it will be added but with _1 appended

Note this does mean that you cannot enforce referential integrity since the parent could be in either tblEquipmentTypes or tblEquipment. As you program, you will know this and the sign of the value (+ or -) tells you which table.

So you are testing a piece of equipment. You have specified the type, so now you can get the required template records which are copied and appended to the same table for the relevant equipment - substituting the negative equipmentFK with the positivePK for the equipment, all the user has to do then is add in the values

I'm really having trouble wrapping my head around this. If I had -1 entered as the EquipmentTemplatePK in t_EquipmentTypes for a pump, and then had -1 entered in (2) rows of my t_FlowRates (one for min flow, one for Max flow), then when a user goes to enter a pump and selects the pump equipment type, I'll be able to have access prompt them for all the values with a -1 in t_FlowRates? (I hope that makes sense.) I'll try and research this but if you have or know where I could find an example of this, that would be great.
 

Attachments

  • Capture.zip
    19.1 KB · Views: 20

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:06
Joined
Aug 30, 2003
Messages
34,752
Post 11 was moderated, I'm posting to trigger email notifications.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:06
Joined
Aug 30, 2003
Messages
34,752
No, you didn't do anything wrong. Sometimes the forum moderates a post and I don't know why. Sometimes it's links from a new member, but I don't see any in yours. Your initial post was moderated too.
 

plog

Banishment Pending
Local time
Today, 06:06
Joined
May 11, 2011
Messages
10,148
Nothing in your post came off as being an a-hole in any way, no need to apologize.

Looking at your screenshot, I see 3 tables that really don't need to exist (t_EqTypes, t_FlowType & t_FlowRateUnit). All those have only 1 real field of data and that data would just as well be stored in the table they link to.
 

Mark_

Longboard on the internet
Local time
Today, 04:06
Joined
Sep 12, 2017
Messages
2,111
Sam I am,

There are two very different ways lookups are set up; By reference and by value.

In a by reference lookup, you choose "XYZ" and "1178" is placed in your data. 1178 is a reference to XYZ. This is useful when you want users to be able to change XYZ and still have it come across the same. These are also very much a pain when put into table level validation or when used in a combo box as often you will need to do a lot more than just ask the user what they want.

By value lookups are conceptually different. They are used when you want to enforce consistent data for various reasons. If you cannot brook the thought of allowing a user to choose "Y" or "N" when you want them to enter "Yes" or "No" (Or you want them to enter "10m/s" instead "Ten Meters per second" or "10 M/S", then you use a value lookup. This is normally a value held in a "Lookup" file of some type and is used much like a dictionary. It does have the advantage that old values are not changed, though depending on your business model this may be an issue if you have need for it to change.

You are the only one who knows if you will need to change after the fact though. Of the two, a by value is often the one that is least problematic as it forces uses to use consistent inputs.
 

Sam_I_Am

Registered User.
Local time
Today, 07:06
Joined
Nov 2, 2017
Messages
17
Nothing in your post came off as being an a-hole in any way, no need to apologize.

Looking at your screenshot, I see 3 tables that really don't need to exist (t_EqTypes, t_FlowType & t_FlowRateUnit). All those have only 1 real field of data and that data would just as well be stored in the table they link to.

My plan was to have a list box in the higher table (tblEquipment) have it point to the lower table (tblEqType, tblEqMfr) as its row source. This will prevent a user from misspelling one of these items and I can also have a form that will allow a user to Add a manufacturer/equipment type if need be (this would generally be handled by me, though). Is there a way this would be possible if I just used a Short Text field type, List Box Display Control, and chose a Value List Row Source Type?

By value lookups are conceptually different. They are used when you want to enforce consistent data for various reasons. If you cannot brook the thought of allowing a user to choose "Y" or "N" when you want them to enter "Yes" or "No" (Or you want them to enter "10m/s" instead "Ten Meters per second" or "10 M/S", then you use a value lookup. This is normally a value held in a "Lookup" file of some type and is used much like a dictionary. It does have the advantage that old values are not changed, though depending on your business model this may be an issue if you have need for it to change.

Is this similar to what I'm referring to above, using the table as a row source for a List Box?

Also, on over on Reddit, someone asked for an example of the checklists. I've attached them for reference but I understand I've still got a ways to go before I can start asking questions directly related to developing these.
 

Attachments

  • Examples.zip
    1.4 MB · Views: 26

plog

Banishment Pending
Local time
Today, 06:06
Joined
May 11, 2011
Messages
10,148
The way I limit text inputs to a set list is by going into Design View of the table, selecting the field I want, going to the Lookup tab on the bottom. Change the Display Control to 'Combo Box', change the Row Source Type to Value List, put in the acceptable values seperated by a semicolon in Row Source and then changing Limit To List to Yes. That method ensures that only approved values make it in.
 

Minty

AWF VIP
Local time
Today, 12:06
Joined
Jul 26, 2013
Messages
7,571
@plog, whilst I agree with you for Access only databases, this approach doesn't scale out to SQL server, or most other larger DBMS.

It's why I always add a PK Ident field and store that for look-ups.
 

plog

Banishment Pending
Local time
Today, 06:06
Joined
May 11, 2011
Messages
10,148
That specific approach doesn't work for other databases, but the general approach does. Control what the user can input into your database via dropdowns in the interface. No need for unnecessary tables.
 

Users who are viewing this thread

Top Bottom