Help with crossjoin, maketable/applen query, and then edit new table

[Sam_I_Am]

I believe I have an understanding of both approaches. The approach used just depends on personal preference and whether or not I plan to scale out to a larger DBMS (database management system?).

While I appreciate using the combo box would be easier to initially setup and would result in less tables, I don't want to get into the habit of doing something in a way that won't scale up in the future.

 

[Mark_]

Sam I Am,

If you change a value in "t_EqTypes", do you want that to reflect in all records?

IF yes, then you would store a reference to the entry in "t_EqTypes" and fetch the appropriate value each time you access a record.

IF no, then you would want to create a list (I don't use hard lists like plog, I use a table that holds the values instead) that allows you to limit what the user enters. This avoids having the data change later.

The reason for the two different approaches is very important.

First case is normally used when you are dealing with dynamic information. You want to store which customer placed an order. If the customer changes their contact information you want to get the correct information to reach them NOW.

Second case is when you know you will need to know what was entered but you want to make sure the user enters a valid value. Customer selects "Auto" for an insurance type. Later you break that out into various types. You don't want the value originally selected "Auto" to change to "ERROR-Category type" when you update and have "Auto" now as a category with many sub-types beneath.

 

[Sam_I_Am]

Sam I Am,

If you change a value in "t_EqTypes", do you want that to reflect in all records?

No, I would not. However, I want the user to be able to add types to the list in case the type they need is not currently in there. But I do not want them to be able to edit the types that are already there.

Second case is when you know you will need to know what was entered but you want to make sure the user enters a valid value. Customer selects "Auto" for an insurance type. Later you break that out into various types. You don't want the value originally selected "Auto" to change to "ERROR-Category type" when you update and have "Auto" now as a category with many sub-types beneath.

It's funny you mention sub-types as I was planning on setting that up as well. I am going to try and follow the example provided by Ken Sheridan in the link below. https://answers.microsoft.com/en-us/office/forum/office_2013_release-access/access-2013-filtering-following-drop-down-list/766c2589-ff2c-41ca-bba9-5afbeba72926

The reason being is that there are different types of boilers, pumps, AHUs, chillers, etc., and I think it will be beneficial later on to sort by the equipment sub-types.

 

[Mark_]

Sam I Am,

The format I normally use for these kinds of look ups is as follows:
T_Lookup
ID PK-Autonumber
Sort Text What generic thing you are looking up
SortSub Text Sub-category
Val Text What goes into a field being looked up
Des Text Description (optional) of what is being looked up
SortOrd Number Order for sort (optional)

This means I have data that looks like this:
Status,,Apply,Applied to the Nursing program,2
Status,,TEAS-Acpt,Accepted to TEAS,3
Status,,TEAS-Rej,Not accepted to take the TEAS,8
Status,,TEAS-RR,Not drawn in the Random Draw for TEAS,9
Status,,PEND,On the wait list for the NURSING program,4
Status,,ACTIVE,Attending classes,1
Status,,DROP,Dropped out of nursing program,6
Status,,OUT,Out of program - normally 2 fails,7
Status,,GRAD,Graduated from program,5

In this case there is not a SortSub
To use, I normally have a query that returns
Val
Des
Sort
SortOrd
and is based off of Sort (and SortSub, if needed) for the WHERE. I create one for each place I would normally be looking up information that needs to stay the same even if the lookup data changes.

The SQL for this query is
SELECT T_Lookups.Val, T_Lookups.Des, T_Lookups.Sort, T_Lookups.SortOrd
FROM T_Lookups
WHERE ((T_Lookups.Sort = "Status"))
ORDER BY T_Lookups.SortOrd;

Look at the combo box Column Count and Column Widths properties for how you can display this in a reasonable way.

 

[Sam_I_Am]

My new (and hopefully improved database) is attached along with a picture of the relationships window.

tblCheckItems is currently not related to any other tables. I'm not sure where I should relate it. I was initially going to relate it to tblEquipment but we typically have at least one checklist on every job that will encompass an entire system (e.g. an FPT for a chilled water system that can encompass a chiller, pumps, a cooling tower, etc.). I was thinking of relating it to the tblJobs, but then I'm not sure how I would relate the ones that would be for a particular piece of equipment to their EqID without having circular paths, which I've been advised against.

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.

Additionally, once I do figure out how to relate the check items (to equipment, building, job, whatever), how do I go about relating a check item to multiple pieces of equipment without repeating that check item for each piece of equipment? Would I need another table relating the equipment/bldg/job to the check item like below?

tblCheckList
CheckListID PK
JobID FK
BldgID FK
EqID FK
PassFail

Again, this would result in some circular references in my relationships.

 

[Sam_I_Am]

Sam I Am,

The format I normally use for these kinds of look ups is as follows:
T_Lookup
ID PK-Autonumber
Sort Text What generic thing you are looking up
SortSub Text Sub-category
Val Text What goes into a field being looked up
Des Text Description (optional) of what is being looked up
SortOrd Number Order for sort (optional)

This means I have data that looks like this:
Status,,Apply,Applied to the Nursing program,2
Status,,TEAS-Acpt,Accepted to TEAS,3
Status,,TEAS-Rej,Not accepted to take the TEAS,8
Status,,TEAS-RR,Not drawn in the Random Draw for TEAS,9
Status,,PEND,On the wait list for the NURSING program,4
Status,,ACTIVE,Attending classes,1
Status,,DROP,Dropped out of nursing program,6
Status,,OUT,Out of program - normally 2 fails,7
Status,,GRAD,Graduated from program,5

In this case there is not a SortSub
To use, I normally have a query that returns
Val
Des
Sort
SortOrd
and is based off of Sort (and SortSub, if needed) for the WHERE. I create one for each place I would normally be looking up information that needs to stay the same even if the lookup data changes.

The SQL for this query is
SELECT T_Lookups.Val, T_Lookups.Des, T_Lookups.Sort, T_Lookups.SortOrd
FROM T_Lookups
WHERE ((T_Lookups.Sort = "Status"))
ORDER BY T_Lookups.SortOrd;

Look at the combo box Column Count and Column Widths properties for how you can display this in a reasonable way.

If I'm understanding this correctly, the data for the list lives in a table and you're using SQL to pull a piece of this data out at the time the user makes the selection using the lookup. None of its fields are tied to another table as a FK. And since the data lives in a table, a Form can be made to add/remove/change the list options, but it will not change any data that has already been entered via the lookup function.

Is all this correct?

(I would have asked this in my previous post, but I was working on that response when you posted and I hadn't seen it yet.)

 

[Mark_]

If I'm understanding this correctly, the data for the list lives in a table and you're using SQL to pull a piece of this data out at the time the user makes the selection using the lookup. None of its fields are tied to another table as a FK. And since the data lives in a table, a Form can be made to add/remove/change the list options, but it will not change any data that has already been entered via the lookup function.

Is all this correct?

Yes.

I'm not writing the SQL myself, I let Access do that for me. I just find showing what the SQL looks like much more informative than trying to do screen shots of a query in designer.

Yes, it means if you select a 2m CAT 3 cable with RJ 45 jacks today, a decade from now when they are obsolete you won't be able to select them but prior usage will NOT be changed.

 

[Sam_I_Am]

Thank you everyone for your help. I think I'm finally to the point where I can start asking more specific questions and will do so in new threads.