Solved Normalization - Struggles In Access

[dalski]

New to db design & really struggling with it. Watched countless tutorials; implementing seems far harder. Been told to avoid composite keys in Access as it makes hard work, which seems to cause violations but I really don't understand normalization.

• ResTypesT - Labour/ Plant/ Material/ Sub-Contractor/ Other/ Prelims
• RgsT - (ResourceGroupsTable) Records to organise resources created from the ResDetailsLibF (form)
• ResourcesLibT - resources are entered through the ResDetailsLibF

Problem
"Activites" - think of them as a resource from a resource & my predicament:

• ActvTitlesLibT - the parent record of the "Activity" itself. It will be held under RgsT (Resource Groups); though this is just for organizational purposes; do not dwell on this. The reporting of the rates themselves will fall under the ResourceType & ResourceGroup; NOT THE ACTIVITY RESOURCE GROUP.
• ActvCompositionLibT - Contains records which determine that particular "Activity's" resources.
• PROBLEM - Say a resource is changed in the ResourcesLibT; it's ResType or Rg. This change is not reflected in the ActvCompositionLibT. How do I account for this?

The forms contain nested comboboxes which refine selection of resources for the user.

 

[jdraw]

dalski,
I recommend you provide a 30,000 ft overview (simple terms no jargon) of the business/process you are trying to automate. It will help put your info into context.

 

[plog]

I understand normalization, but have no idea what your organization does, nor how this database fits into your organization. So, let's step back from the database for a second and explain 2 things to me, using a paragraph each:

1. What does your organization do? Pretend its career day at an elementary school and explain to us in simple terms what it is you do. No database jargon allowed in this paragraph.

2. What will this database help your organization do? A little database jargon allowed, but focus on its role within the organization--what data will it organize and how will its output be used?

In general, the biggest issue I see is a spiderweb of relationships. In a database there should only be 1 way to trace a path between 2 tables. In yours, from ResTypesT to ActCompositionLibT I can trace 4 paths between them. That's not right. Because I don't understand your organization I don't know which one is correct.

Additionally, I feel you might have over-normalized your tables by having too many tables. My gut is telling me at least 2 of them can be merged. Again though, not enough understanding of your organization to know though.

 

[MajP]

To help discussion, I put these as images so you do not have to pop it open to see it.
People mistakenly freak out when they see a closed loop thinking somehow you created a circular relationship. They are wrong, and do not know what a circular relationship is. It is very hard to create a circular relationship. So I modified it by adding a second instance of the table, but think once you save it Access will revert to a single table.

With second instance (technically the exact same thing), but to keep people from freaking out.

 

[MajP]

Here is the common problem.
If I have a table that hold a key to another table for example

tblActions
--Action ID
--Action Name
--Action TypeID

tblActionTypes
--ActionTypeID
--Type Description

If I have a data table
tblActionsTake
--ActionsTakenDate
--Actions ID_FK 'Relates to my actions

I do not have to pull into this table the Action Type ID. I can get that through the relation to the action table in a query.

So a RG is related to a ResType

Rin your resourceLib table you only need to hold the RGid and not the ResTypeID. That ResType (and all res type fields) would come through the relation to the RgT id.

 

[MajP]

FYI. One thing that I find helps is to call my Foreign Keys something different.
Instead of RegID in the ResourceLib I always call these RegID_FK (telling me it is a foreign key relating to the RegID). It helps a lot in viewing queries. But you did a good job giving the PK a unique name without spaces (some people just put ID).

 

[MajP]

I also see why you are struggling on that, because the form design is hard to do with a proper data structure.

You want to do a cascading combo on a continuous form, but you do not need to actually save the ResTypeID. You only need it for filtering the RgID. If you make this an unbound control then you get the problem that all unbound controls show the same value. I will demo a way to do this.

 

[dalski]

Thankyou all so much. I'm sorry I did not post all because I thought too much info deters people's interest & I think it's too big a topic for single post. Usually & people rarely understand me so I tried to simplify in OP. Looks like MajP has explained it for me which I'm going to read as soon as I post this.

1. What does your organization do? Pretend its career day at an elementary school and explain to us in simple terms what it is you do. No database jargon allowed in this paragraph.

2. What will this database help your organization do? A little database jargon allowed, but focus on its role within the organization--what data will it organize and how will its output be used?

1 - Cost Estimation of construction projects. Specific to each tender enquiry. I have used several db's relating to this & the best by far is the type of features I am trying to implement here. A Tender is a particular enquiry, inside a tender are Bills. Inside the bills are BillRows; individual items contravariantly related to the parent bill.

2 - Estimating the cost of a construction project; which is determined by the resources used (from Resources). A library is where resources exist. Their prices change as time goes on & although it looks like redudant data resources are unique to each tender; their descriptions, the units used... the user needs the freedom to change as he/ she desires on that particular Tender. I believe it to be one of the rare exceptions for redundant data.

Resource records need to be assigned to each BillRow, entered inline to each bill OR/AND via an Activity (Resources from resources/ groups...). My plan was to have near identical Resources for the Tender & copy instances from the Library, but records were needed for that particular tender should the user deviate from it's typical make-up.
I'll have queries which breakdown the cost relating to their applicable headers... This is another topic. So don't worry about this.

No doubt I have not explained myself clearly & there is quite a bit to this topic; which I only mention as you wanted a detailed breakdown.

To help discussion, I put these as images so you do not have to pop it open to see it.

 

[MajP]

I believe this is the correct Relationship, and it will make the data much easier. However, it will make the form design more challenging.

Example.
If an adding something to the Active composition library. I simply select a Resource from the Resource Library. I can show in the query the RG, ResType through the "Chain". However, you are going to want to do a lot of cascading combos (or some other way to select) and this is hard to do especially in continuous forms. I would do a more non standard design.

I would use pop up forms as my picker instead of cascading combos. Once you build one they are all slight variations. But wrestling with unbound cascading combos is an continuous form is a huge PITA.

Here is my selector that I use to do this.

I use a Pop up Form with a datasheet subform. There is code involved, but if interested you can look at what I did. You click on the pencil and it pops open the form. Nice thing is you can resize, sort, and filter all the columns because it is a datasheet. Then click on a row and hit OK and it will edit. If you open to an existing record it will move to the selected row. The popup forms are reusable and can be called from any form. It takes a little work to do this the first time, but I use this idea often and it comes in handy once you build one of these.

 

[dalski]

Thanks MajP , trying to figure it out. Anyone have any tutorials on this? What's with the Nz & 0 in the opening argument. MSN does not have e.g's like this. Is Nz a local variable not requiring declaration, & lost for what the ,0 is for.

Nz(Me.ReLibID_FK, 0)

Would there not be a lot of error handling with different forms open?

 

[MajP]

Anyone have any tutorials on this?

Like I said, I can see why this may have been confusing because of the form design that you needed.
You need to cascade, but you do not need to hold the additional foreign key. This condition occurs some times but not that often.

Bottom line if you have TableA, TableB, and TableC
If tableB has a foreign key relating to TableA and TableC has a key relating to TableB you do not need TableC to have both foreign keys.
If C relates to B and B relates to A then C relates to A through B

TableA
A_ID
A_Description

TableB
B_ID
B_Description
A_ID_FK

TableC
C_ID
C_Description
B_ID_FK

Now you can join A to B and B to C and show some or all fields from the three tables.

The way you had it can work but there is a drawback to data integrity

If TableC held both keys to A and keys to B, it is possible that you can modify one of those keys and not be in synch. Referential Integrity can not catch that. So you could get the case where you are holding two out of synch keys.
Labour (type)
Bull Dozer (resource)

Nz(Me.ReLibID_FK, 0)

I am trying to pass the current RelLibID_FK to the pop up in openArgs. The form then moves the pointer to that record in the pop up.
If I am on a New record the RelLibID_FK will be NULL.
This way I pass 0. I could pass anything that does not exist like
Nz(Me.ReLibID_FK, -1) since no RelLibID can ever be less than 1.

Since no RelLibID = 0 the find first will not find anything.

That form is a little involved, by trying to do this cascading is a real pain. I think this is more intuitive and can be reused.

Would there not be a lot of error handling with different forms open

No. But I only open pop ups as Modal. So you can only work in that form and go back to the other form only after closing the pop up. I used a continuous form on my pop up, but could have used a listbox as my selector. But then you lose the sort and filter capability. The pop up is locked (no edits, additions, deletions).

 

[MajP]

Here is a version using a Pop Up listbox. In theory you could bind the listbox directly on the form, but I find that confusing to see the selection.

 

[MajP]

There is another way to do this and that is a two column combo box, but it is not such a great solution.
So you could have a combo that holds the RgID, Resource Group Description, Resource Type Description. Sorted by Type then by Group. (like the listbox above)
When you pull it down it will show two columns but once selected only show the RG Description.

I kind of like this listbox idea unless the list gets really long, But at a reasonable size this is easy to visualize and select.

 

[June7]

Nz() is an intrinsic Access function. https://support.microsoft.com/en-us/office/nz-function-7ae941b1-fc69-47a0-a279-a6a860cfb4c5

 

[dalski]

Thanks, I have learnt so much here. Pop-up forms is originally what I wanted. What are the benefits of not binding the subforms? I was not even aware of the datasheet form type.

1 - frmResourceSelector > Event Form_Load.
(a) Is the below function itself not superfluous. We jump here regardless if the Nz returns the FK or 0. Or is it just good practice?
(b) Cannot find anything on Google relating to statement itself. OpenArgs blank not blank. Is this just error handling to avoid if a null was returned from the OpenArgs property?

OpenArgs & "" <> ""

2 - Module 1 never fires; is it that you are just practicing good practice to cover angles?

Public Function GetResType(RgID As Variant) As Variant
  If Not IsNull(RgID) Then GetResType = DLookup("restype", "qryRg_ResType", "RgID = " & RgID)
End Function

3 - frmResourceSelector put in design-view state unwittingly; hinders save as command & sometimes causes hidden instance of Access running:
Try this:
ActVtitlesLibF > Select new record > Pencil > You get a prompt to save changes to the design of the form unnecessarily - not record modificiation; but a change of design.

 

[MajP]

Is the below function itself not superfluous. We jump here regardless if the Nz returns the FK or 0. Or is it just good practice?

I do this on any pop up form that might use open args. This allows me to test the form without having to call it from another form. I can just click on it to open it, and since there is no openargs it is not going to try and execute any code.
Sometimes I cannot remember if certain properties when empty are NULL or "", the two are different. That is why I wrote it like this because
If Me.OpenArgs & "" will = "" in either case. Null & "" = "" and so does "" & "".

This is a better check on a control. In theory a blank control can be NULL or have an empty string "".
so you can check
if textbox.value = "" ' misses null case
if isnull(textbox.value) then ' misses the empty string case

if (textbox.value & "") = "" then ' catches both cases

Very rarely you can get a space or spaces. (normally from an excel import).
the ultimate check for all 3 cases
if trim(Me.textbox & "") = "" then ' catches null, "", and " "

Module 1 never fires; is it that you are just practicing good practice to cover angles?

I was trying to do this first with cascading combos and was using this. The cascading combos was such a pain that I quit.
Coding wise this is something I do in all applications. I tend to make lots of wrappers of Dlookups and other domain aggregate functions. Usually I need to get some piece of information about a specific record. So I wrap dlookups in a function and can now easily use it in forms, reports, queries, and calculated controls
If I have a RgID and want to show the ResType in a form I do not have to rewrite some convoluted dlookup
msgbox "The resource type for this ID is " & getResType([me.RgID])

frmResourceSelector put in design-view state unwittingly; hinders save as command & sometimes causes hidden instance of Access running:

The reason is the OK button on the form. The way I pull information from pop ups is as follows.
1. I put an OK and cancel button on the form.
2. The OK button only hides the form, the cancel button closes the form.
3. I call the popup passing the parameter ADIALOG. This means that code execution in the calling form stops until the pop up is hidden or closes.
4. If the pop up is hidden the calling form reads the value from the pop up and then closes the pop up
5. If the pop up was canceled then nothing happens and no updates.

So you went to design view and opened the form. Then you hit ok which hid the form but since it was not closed by the calling form it stayed hidden and actually unsaved.

This technique of OK, Cancel hiding and closing has a few steps that need to happen, but it is a very flexible way to do this.
You do not have to tightly couple the pop up to one form or control. Any form or control can call the pop up and pull the values.

I attached a demo that expands on this ID using a calculator. You can double click a textbox and pull up a calc and return the values. See frmDemo.

 

[dalski]

Thankyou, grasp it all I think now; but still not the below.

I understand you are error handling. I would understand if your below said OpenArgs = "", I don't understand how the OpenArgs concatenates a blank returned string, a not operator and another blank string. I am stumped as to why there are no Google results for this. I understand completely you're covering the returned string possibliities but I am stumped as why there is nothing on the statement.

OpenArgs & "" <> ""

Is what is written. So in my mind the OpenArgs will be returned. It will be the result of the

If Me.OpenArgs & "" will = "" in either case. Null & "" = "" and so does "" & "".

if (textbox.value & "") = "" then ' catches both cases

the ultimate check for all 3 cases
if trim(Me.textbox & "") = "" then ' catches null, "", and " "

 

[MajP]

I would understand if your below said OpenArgs = ""

When the result is "" I do NOT want it to do anything. I want it to look for the record when the result <> ""
The result will = "" when either openargs is null or openargs is ""

The whole code is this.

Private Sub Form_Load()
  If Me.OpenArgs & "" <> "" Then
    Me.subFrmResLib.Form.Recordset.FindFirst "reslibid = " & Me.OpenArgs
  End If
End Sub

1. If I pass in a valid id number from the other form lets say (4)
openArgs = 4

4 & "" = "4"
This does not equal "" so
If me.openArgs & "" <> "" then it moves to the record with reslib = 4

2. If I pass in 0 there can be no Reslib = 0 since all autonumbers are > 1. So it too passes the if check but nothing is found so the pointer does not move If you findfirst a record that does not exist, nothing happens.

3. If I just click on the form then no open args is passed. I think that is NULL, but maybe it is an empty string. (to lazy to test). But it does not matter.
Assume it is NULL
Null & "" = "" (FYI, NULL + "" = NULL and not "")
Or Assume it is an empty string
"" & "" = ""

So either way the if check is not met

If me.openArgs & "" <> "" then ...
both of these cases do = "" so the check is not passed.

 

[dalski]

Thank you very much, learnt an awful lot from you here.

 

[MajP]

There are a lot of people on this forum that can share a lot of tips and tricks. There are probably a lot of different approaches to do what I suggested.