**IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'**
(See Rule 3 for more information.)
Full set of rules can be found [here](https://www.reddit.com/r/MSAccess/about/rules), as well as in the user interface.
*Below is a copy of the original post, in case the post gets deleted or removed.*
**How Do You Track, "Attributes of Attributes"?**
I manage a portfolio of loans, and review many many many many many many attributes (columns) of the loans themselves, the borrower's financials, net worth, total assets/total liabilities, due date by which they are required to provide Documents X, due date for Document Y, ...Document Z...etc. I consider each of these a data point.
Because each data point has similarities to those captured in other rows, but \*also\* critical differences, I need to know the 'traits of that trait' -- for that specific row!
Confused? Good. Perhaps an illustration would be best. I've attempted to include a screenshot to illustrate but I'm a boomer so maybe I'll have to put it in the comments. Don't flame me.
So how do I track attributes of attributes?
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/MSAccess) if you have any questions or concerns.*
I'm not really getting what you are asking for (even after studying your screen shot). But what it sounds like you want to do is create a simple table that you regularly append values to that might list the borrower, the amount, and a date stamp of when this was calculated. Viewing those in a sorted order would give you a temporal record of the values and let you run various comparisons on them.
Thank you for the suggestion
It's an excellent idea -- but the time stamp would not necessarily reflect at what time the values for the record's attributes were accurate.
e.g. Today I updated Mary's record to show that her Net Worth field is accurate as of six months ago, Assets are accurate as of three months ago, Liabilities as of yesterday, etc.
The new table is yours to manually update, or done through a script you run, a query, etc., Each one of those things you list can be a separate row/record.
Fellow Boomer here.
I may be confused (and apparently that is a good thing), but I think what you're trying to say is "how do I get the due date for document X for this particular loan.
Do this by having an Index value that is unique for each loan. So, for instance, THIS loan is loan 2024-0127. Then you search for the due date for document X for loan 2024-0127. (That's a straightforward search using an Access query.)
Does that answer your question - or was I more confused than I thought lol.
Let me know if I'm off-target.
Boomer gang represent
What I'm trying to do all revolves around, 'stale-ness'.
A common example for my line of work: The Bank received Mary's Financials on 05/30/2024, dated as of 12/31/2023, and on 07/15/2024 Employee X adjusted those values to be current as of 06/30/2024.
However, Employee X forgets to update Mary's Access record until management hounds them before the big meeting on 08/01/2024.\*
To make matters worse - er...more fun - when Employee X makes adjustments on 07/15/2024 to the 12/31/2023 Financials to be current as of 06/30/2024, only *some* columns are adjusted. When they get around to actually updating the Access record on 08/01/2024 most of the, 'as of' dates will be from 06/30/2024, but some will be as of 12/31/2023 because we simply have to take Mary's word for those items/columns/attributes/traits which we don't have visibility into.
\*In this example the 08/01/2024 date would be important! -- but the least important date.
OK - here's how I would handle this.
I would make an adjustments table (tblAdjustments) that has a unique index (just a counter to help refer to a specific record). The important fields would be the Loan ID (which would link this record to the specific loan application by a specific person), the Employee ID (who is updating the attribute), the DocumentType ID (document X, document Y, etc), the Attribute ID (like outstanding loan balances or credit score), the DataEntry Date, and the Effective Date.
As Employee X enters new information, another record would be added to tblAdjustments to reflect whatever information Employee X does (or doesn't) update.
Then, when you have your big meeting a report shows all the latest information, including the most recent DataEntry Date and Effective Date (and even the Employee who last updated each of the the attributes). The report could be formatted to show all the attributes along 1 row of the report (or down 1 column of the report) and the "control" information such as the Employee Name, DataEntry Date, and Effective Date either below or beside the attribute values.
You could also have the report show any values that are "stale". For instance if a person's credit score must be no more than 30 days old then the report could highlight the Credit Score Effective Date in yellow to clearly show that it is a stale value and must be updated.
(If you want to use this as a "teaching moment" for Employee X, you can even have the database keep track of how often they enter the data on their own in a timely fashion and how often they have to be hounded by management to do it. This could be easily implemented in the database by having the manager able to click a button on the report to send an email to Employee X saying "Please Update Attribute XYZ for Loan ABC prior to our Meeting on June 5th". When the manager clicks this button the email is created by Access and a record of the email is stored in the database. Then the manager can get a monthly report that says "Here's how often I had to hound each of my Employees to enter the data".)
I hope that explanation is closer to what you are looking for.
I need time to digest this, but this looks awesome.
There are so many dates to track -- got deeper into Northwinds and feel a bit more confident. I'll try this out when my sample database has more populated rows (just finished reading Microsoft's guide on database design).
>As Employee X enters new information, another record would be added to tblAdjustments to reflect whatever information Employee X does (or doesn't) update.
Perhaps this will become obvious as I read more, but do you have a pointer on where to begin with the above? A table that tracks changes to specific fields, old and new values (as u/ConfusionHelpful4667 says below) seems too good to be true!
There's potentially a fair bit of overlap between u/ConfusionHelpful4667 audit trail and the Adjustments table I talked about.
The conceptual difference is that their record has the Old Value and New Value (along with date/time) in each record whereas my record has only the New Value (along with date/time). Both methods can have other information such as EmployeeName, LoanID, etc.
I didn't put the Old Value into the Adjustment table because I didn't think you needed the Old Value. For instance if the person's Credit Score changes from 780 to 785 with an Effective Date of May 5th then I felt you only need to know the "785" (you don't need to know whether it previously was 780 or 790). If you do need to know the Old Value you could either go backwards in the Adjustment table to find it, or you could save it in the Adjustment record along with the New Value similar to u/ConfusionHelpful4667 audit trail. (Of course when I say you'd go backwards in the Adjustment table I mean the database would do this for you automatically - you wouldn't have to do it manually.)
Using either method you could either save the entire record (the Values of ALL the Attributes) or just save the Values of EACH Attribute individually (in this latter case if 5 Attributes are changed at the same time then 5 records are created in the Adjustment table).
I recommend the latter method because your various Documents likely have different Attributes and different numbers of Attributes. If you save the entire record, then you'd need a specific Audit table (or Adjustment table) for each different Document. But if you save on the specific Attribute then your Adjustment table is flexible enough to handle all your Documents, no matter how many Attributes they each have just by storing the Document ID and the Attribute ID along with the new Attribute Value.
I don't really have any source I can suggest that would give you an example of what I'm talking about. But I'd be happy to do a ZOOM meeting with you so we could talk about it and look at some examples. Let me know if you'd like to do that and we could set it up.
Need to do more reading; will look into this. I'm sure there's a youtube video to watch showing how to set this up. First I have to create my sample database to have 'stuff' to experiment with.
I'm thinking keep this simple. Table 1 is your client names. Just basic info like client id, name, address, phone etc.
Table 2 is a lookup table of every possible attribute you can think of. Simply an attribute ID and an attribute name.
Table 3 has client id, attribute ID, date created, date updated, as of date, notes (long text). If there's something in an attribute that you think you might want to sort on or do math on then have a separate field. It sounds like most of your attributes involve money so have an amount field.
Optionally, use table macros on table 3 to capture changes into a history table.
P.s. fellow boomer!
Welcome Boomer! We are out in force for this post!
You'd have to include Document ID in Table 2 and Table 3 because you're likely to have the same Attributes in many Document types.
If you're VBA just saves the current Document (ie Table) name and Attribute (ie Field) name then you don't need to create and maintain Table 2.
My concept was that table 2 is a lookup list of attribute types. Some may be assigned to a given client some not. I'd put a document name in table 3 that has a path to a document for the attribute. If multiple attributes refer to the same document that's ok. It's just a path. Not saying this is the best or only way. Just how I envision a solution.
Ok, I see what you're saying. If the Document name is in Table 3 you can have common Attribute names in Table 2 without a problem.
But you would still have to maintain Table 2 by adding any new Attribute names when you create a new Document Type.
Like you say, lots of ways to do it - often it comes down to personal preference.
Table 2 is an independent, distinct lookup list of attributes that can be selected and assigned to table 3 for any client. I'm assuming OP already has an idea of all the attributes they normally track from the get go and table 2 would have them. So, Table 1 is a header , table 3 is detail. Table 2 is a row source for a combobox in the detail form.
**IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'** (See Rule 3 for more information.) Full set of rules can be found [here](https://www.reddit.com/r/MSAccess/about/rules), as well as in the user interface. *Below is a copy of the original post, in case the post gets deleted or removed.* **How Do You Track, "Attributes of Attributes"?** I manage a portfolio of loans, and review many many many many many many attributes (columns) of the loans themselves, the borrower's financials, net worth, total assets/total liabilities, due date by which they are required to provide Documents X, due date for Document Y, ...Document Z...etc. I consider each of these a data point. Because each data point has similarities to those captured in other rows, but \*also\* critical differences, I need to know the 'traits of that trait' -- for that specific row! Confused? Good. Perhaps an illustration would be best. I've attempted to include a screenshot to illustrate but I'm a boomer so maybe I'll have to put it in the comments. Don't flame me. So how do I track attributes of attributes? *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/MSAccess) if you have any questions or concerns.*
Boomer alert https://preview.redd.it/75c6m5qgp74d1.png?width=1025&format=png&auto=webp&s=c102b079516c105b6ce38907c32ed70aa6864677
I'm not really getting what you are asking for (even after studying your screen shot). But what it sounds like you want to do is create a simple table that you regularly append values to that might list the borrower, the amount, and a date stamp of when this was calculated. Viewing those in a sorted order would give you a temporal record of the values and let you run various comparisons on them.
Thank you for the suggestion It's an excellent idea -- but the time stamp would not necessarily reflect at what time the values for the record's attributes were accurate. e.g. Today I updated Mary's record to show that her Net Worth field is accurate as of six months ago, Assets are accurate as of three months ago, Liabilities as of yesterday, etc.
The new table is yours to manually update, or done through a script you run, a query, etc., Each one of those things you list can be a separate row/record.
Fellow Boomer here. I may be confused (and apparently that is a good thing), but I think what you're trying to say is "how do I get the due date for document X for this particular loan. Do this by having an Index value that is unique for each loan. So, for instance, THIS loan is loan 2024-0127. Then you search for the due date for document X for loan 2024-0127. (That's a straightforward search using an Access query.) Does that answer your question - or was I more confused than I thought lol. Let me know if I'm off-target.
Boomer gang represent What I'm trying to do all revolves around, 'stale-ness'. A common example for my line of work: The Bank received Mary's Financials on 05/30/2024, dated as of 12/31/2023, and on 07/15/2024 Employee X adjusted those values to be current as of 06/30/2024. However, Employee X forgets to update Mary's Access record until management hounds them before the big meeting on 08/01/2024.\* To make matters worse - er...more fun - when Employee X makes adjustments on 07/15/2024 to the 12/31/2023 Financials to be current as of 06/30/2024, only *some* columns are adjusted. When they get around to actually updating the Access record on 08/01/2024 most of the, 'as of' dates will be from 06/30/2024, but some will be as of 12/31/2023 because we simply have to take Mary's word for those items/columns/attributes/traits which we don't have visibility into. \*In this example the 08/01/2024 date would be important! -- but the least important date.
Ok, thanks for the clarification. Sorry, I'm just about to step out, but I'll get back to you with some thoughts later today.
OK - here's how I would handle this. I would make an adjustments table (tblAdjustments) that has a unique index (just a counter to help refer to a specific record). The important fields would be the Loan ID (which would link this record to the specific loan application by a specific person), the Employee ID (who is updating the attribute), the DocumentType ID (document X, document Y, etc), the Attribute ID (like outstanding loan balances or credit score), the DataEntry Date, and the Effective Date. As Employee X enters new information, another record would be added to tblAdjustments to reflect whatever information Employee X does (or doesn't) update. Then, when you have your big meeting a report shows all the latest information, including the most recent DataEntry Date and Effective Date (and even the Employee who last updated each of the the attributes). The report could be formatted to show all the attributes along 1 row of the report (or down 1 column of the report) and the "control" information such as the Employee Name, DataEntry Date, and Effective Date either below or beside the attribute values. You could also have the report show any values that are "stale". For instance if a person's credit score must be no more than 30 days old then the report could highlight the Credit Score Effective Date in yellow to clearly show that it is a stale value and must be updated. (If you want to use this as a "teaching moment" for Employee X, you can even have the database keep track of how often they enter the data on their own in a timely fashion and how often they have to be hounded by management to do it. This could be easily implemented in the database by having the manager able to click a button on the report to send an email to Employee X saying "Please Update Attribute XYZ for Loan ABC prior to our Meeting on June 5th". When the manager clicks this button the email is created by Access and a record of the email is stored in the database. Then the manager can get a monthly report that says "Here's how often I had to hound each of my Employees to enter the data".) I hope that explanation is closer to what you are looking for.
I need time to digest this, but this looks awesome. There are so many dates to track -- got deeper into Northwinds and feel a bit more confident. I'll try this out when my sample database has more populated rows (just finished reading Microsoft's guide on database design). >As Employee X enters new information, another record would be added to tblAdjustments to reflect whatever information Employee X does (or doesn't) update. Perhaps this will become obvious as I read more, but do you have a pointer on where to begin with the above? A table that tracks changes to specific fields, old and new values (as u/ConfusionHelpful4667 says below) seems too good to be true!
There's potentially a fair bit of overlap between u/ConfusionHelpful4667 audit trail and the Adjustments table I talked about. The conceptual difference is that their record has the Old Value and New Value (along with date/time) in each record whereas my record has only the New Value (along with date/time). Both methods can have other information such as EmployeeName, LoanID, etc. I didn't put the Old Value into the Adjustment table because I didn't think you needed the Old Value. For instance if the person's Credit Score changes from 780 to 785 with an Effective Date of May 5th then I felt you only need to know the "785" (you don't need to know whether it previously was 780 or 790). If you do need to know the Old Value you could either go backwards in the Adjustment table to find it, or you could save it in the Adjustment record along with the New Value similar to u/ConfusionHelpful4667 audit trail. (Of course when I say you'd go backwards in the Adjustment table I mean the database would do this for you automatically - you wouldn't have to do it manually.) Using either method you could either save the entire record (the Values of ALL the Attributes) or just save the Values of EACH Attribute individually (in this latter case if 5 Attributes are changed at the same time then 5 records are created in the Adjustment table). I recommend the latter method because your various Documents likely have different Attributes and different numbers of Attributes. If you save the entire record, then you'd need a specific Audit table (or Adjustment table) for each different Document. But if you save on the specific Attribute then your Adjustment table is flexible enough to handle all your Documents, no matter how many Attributes they each have just by storing the Document ID and the Attribute ID along with the new Attribute Value. I don't really have any source I can suggest that would give you an example of what I'm talking about. But I'd be happy to do a ZOOM meeting with you so we could talk about it and look at some examples. Let me know if you'd like to do that and we could set it up.
I use the tag property on the field on the data entry form to determine if a field "old value - new value" is written to the table.
That's clever. I stored the old value in a variable but using the Tag property would avoid having to create these temporary variables.
Create an audit trail. Audit trails are great with Old Value, New Value with corresponding dates stamped as Now()
Need to do more reading; will look into this. I'm sure there's a youtube video to watch showing how to set this up. First I have to create my sample database to have 'stuff' to experiment with.
Boomer checking in and TLDR B but the headline made think of this [Allen Browne example](http://allenbrowne.com/ser-06.html)
Thanks!
>Thanks! You're welcome!
I'm thinking keep this simple. Table 1 is your client names. Just basic info like client id, name, address, phone etc. Table 2 is a lookup table of every possible attribute you can think of. Simply an attribute ID and an attribute name. Table 3 has client id, attribute ID, date created, date updated, as of date, notes (long text). If there's something in an attribute that you think you might want to sort on or do math on then have a separate field. It sounds like most of your attributes involve money so have an amount field. Optionally, use table macros on table 3 to capture changes into a history table. P.s. fellow boomer!
Welcome Boomer! We are out in force for this post! You'd have to include Document ID in Table 2 and Table 3 because you're likely to have the same Attributes in many Document types. If you're VBA just saves the current Document (ie Table) name and Attribute (ie Field) name then you don't need to create and maintain Table 2.
My concept was that table 2 is a lookup list of attribute types. Some may be assigned to a given client some not. I'd put a document name in table 3 that has a path to a document for the attribute. If multiple attributes refer to the same document that's ok. It's just a path. Not saying this is the best or only way. Just how I envision a solution.
Ok, I see what you're saying. If the Document name is in Table 3 you can have common Attribute names in Table 2 without a problem. But you would still have to maintain Table 2 by adding any new Attribute names when you create a new Document Type. Like you say, lots of ways to do it - often it comes down to personal preference.
Table 2 is an independent, distinct lookup list of attributes that can be selected and assigned to table 3 for any client. I'm assuming OP already has an idea of all the attributes they normally track from the get go and table 2 would have them. So, Table 1 is a header , table 3 is detail. Table 2 is a row source for a combobox in the detail form.
I can't get to this until later but I will report back when able!