Clear out data

[gemma-the-husky]

Pete, when I say that the database will have twice as many tables/forms/reports next year, it means that I am constantly finding new information to add to the database. When I started back in January the only domains that I considered were financial ones. I read lots about financial systems such as Quicken, to understand things that I could put in the database. A friend suggested that I subscribe to Quicken but no, I needed graphics and speech in my system. I have a copy of Microsoft Money somewhere, but do you think I use it?

Then I started going to public libraries to check out books and magazines about personal finance. I found a goldmine in the Kiplinger Personal Finance magazine. Then I started subscribing to Kiplinger online. More good information.

Then I started asking family members for their budget templates in Excel. Later, I purchased textbooks on finance and accounting.

I still consider myself to be a dummy about finance. I worked for an insurance company twice. I passed the state exam twice.

Later, I realized the connection between personal finance and health. Family members started telling me that they have serious health issues, so I started reading the medical literature. Each article led me to create a new module in the database. Yes, I could have done more with normalization, but I discovered a new disease every week.

Then I got hit in the face with food recalls. There was a recall almost once a week, and then twice a week. I had already dealt with recalls of medicines.

Somewhere along the line, I decided to track pet care costs. That taught me how much a family can spend on cats and dogs. It's food, medicine, doctors, and surgeries.

You and others here probably develop applications for a business or government agency. They hire you to create a database that they have defined. That's not how I work. Who knows what I will discover and want to add to the database tomorrow?

I admit that the database is designed incorrectly. But show me an application with everything that I have in my system, changes from day to day, and, and, and.

Thanks for your interest.

Yes, but you don't need new tables and forms to add extra pieces of information (data, datum) to a person record. You add new fields to the person record, and redesign the person form to include that field. In some cases you might decide to model it in a different way, where the person's attributes are stored in a person's attributes table, with each individual attribute in a different row.

Either way, you end up with 2 or 3 tables, not hundreds.

This second way is perhaps less intuitive, but you generally don't even need to change the database design to include new attributes with this model, and you never end up with too many columns.

You certainly don't need a new table for every person in any event.

If you have a table for every person, then no wonder you are struggling to delete the same piece of information for multiple persons.

It's also why we are all struggling to help, because the best advice is probably to start over. Sorry.

 

[AccessBlaster]

I took the liberty to ask ChatGPT to look at the following picture and redesign the schema to N3 with joins.

The redesigned database as follows

MAIN TABLES (Entities)​

1. FamilyMember​

Field	Type	Notes
MemberID	AutoNumber	Primary Key
FirstName	Short Text
LastName	Short Text
DOB	Date/Time
Gender	Short Text	Optional
Relationship	Short Text	E.g., Spouse, Child, Parent

---

2. ExpenseCategory​

Field	Type	Notes
CategoryID	AutoNumber	Primary Key
CategoryName	Short Text	E.g., "Grocery", "Medical"

---

3. SubCategory​

Field	Type	Notes
SubCategoryID	AutoNumber	Primary Key
CategoryID	Number	FK to ExpenseCategory.CategoryID
SubCategoryName	Short Text	E.g., “CostCo”, “Physician Costs”

---

4. Expense​

Field	Type	Notes
ExpenseID	AutoNumber	Primary Key
MemberID	Number	FK to FamilyMember.MemberID
SubCategoryID	Number	FK to SubCategory.SubCategoryID
ExpenseDate	Date/Time
Amount	Currency
Notes	Memo	Optional

---

5. MedicalEvent​

Field	Type	Notes
EventID	AutoNumber	Primary Key
MemberID	Number	FK to FamilyMember.MemberID
SubCategoryID	Number	FK to SubCategory.SubCategoryID
EventDate	Date/Time
Provider	Short Text	Optional hospital/clinic
Notes	Memo

---

6. Medication​

Field	Type	Notes
MedicationID	AutoNumber	Primary Key
MemberID	Number	FK to FamilyMember.MemberID
Name	Short Text	E.g., Guaifenesin
Dose	Short Text	Optional
StartDate	Date/Time
EndDate	Date/Time	Optional
OTC	Yes/No	Over-the-counter flag
Prescription	Yes/No
Notes	Memo

---

7. FoodPreference​

Field	Type	Notes
PreferenceID	AutoNumber	Primary Key
MemberID	Number	FK to FamilyMember.MemberID
FoodType	Short Text	E.g., “Dairy”, “Raw Food”
Preference	Short Text	“Avoid”, “Encouraged”, “Neutral”
Reason	Short Text	Optional (“Kidney”, “Diabetes”)

---

8. Pet​

Field	Type	Notes
PetID	AutoNumber	Primary Key
MemberID	Number	FK to FamilyMember.MemberID
PetName	Short Text
Species	Short Text	Dog, Cat, etc.
Breed	Short Text	Optional

---

9. PetCare​

Field	Type	Notes
PetCareID	AutoNumber	Primary Key
PetID	Number	FK to Pet.PetID
Date	Date/Time
CareType	Short Text	E.g., “Vet Visit”, “Grooming”
Cost	Currency
Notes	Memo

 

[BlueSpruce]

I took the liberty to ask ChatGPT to look at the following picture and redesign the schema to N3 with joins.

View attachment 120817

The redesigned database as follows

MAIN TABLES (Entities)​

1. FamilyMember​

Field	Type	Notes
MemberID	AutoNumber	Primary Key
FirstName	Short Text
LastName	Short Text
DOB	Date/Time
Gender	Short Text	Optional
Relationship	Short Text	E.g., Spouse, Child, Parent

---

2. ExpenseCategory​

Field	Type	Notes
CategoryID	AutoNumber	Primary Key
CategoryName	Short Text	E.g., "Grocery", "Medical"

---

3. SubCategory​

Field	Type	Notes
SubCategoryID	AutoNumber	Primary Key
CategoryID	Number	FK to ExpenseCategory.CategoryID
SubCategoryName	Short Text	E.g., “CostCo”, “Physician Costs”

---

4. Expense​

Field	Type	Notes
ExpenseID	AutoNumber	Primary Key
MemberID	Number	FK to FamilyMember.MemberID
SubCategoryID	Number	FK to SubCategory.SubCategoryID
ExpenseDate	Date/Time
Amount	Currency
Notes	Memo	Optional

---

5. MedicalEvent​

Field	Type	Notes
EventID	AutoNumber	Primary Key
MemberID	Number	FK to FamilyMember.MemberID
SubCategoryID	Number	FK to SubCategory.SubCategoryID
EventDate	Date/Time
Provider	Short Text	Optional hospital/clinic
Notes	Memo

---

6. Medication​

Field	Type	Notes
MedicationID	AutoNumber	Primary Key
MemberID	Number	FK to FamilyMember.MemberID
Name	Short Text	E.g., Guaifenesin
Dose	Short Text	Optional
StartDate	Date/Time
EndDate	Date/Time	Optional
OTC	Yes/No	Over-the-counter flag
Prescription	Yes/No
Notes	Memo

---

7. FoodPreference​

Field	Type	Notes
PreferenceID	AutoNumber	Primary Key
MemberID	Number	FK to FamilyMember.MemberID
FoodType	Short Text	E.g., “Dairy”, “Raw Food”
Preference	Short Text	“Avoid”, “Encouraged”, “Neutral”
Reason	Short Text	Optional (“Kidney”, “Diabetes”)

---

8. Pet​

Field	Type	Notes
PetID	AutoNumber	Primary Key
MemberID	Number	FK to FamilyMember.MemberID
PetName	Short Text
Species	Short Text	Dog, Cat, etc.
Breed	Short Text	Optional

---

9. PetCare​

Field	Type	Notes
PetCareID	AutoNumber	Primary Key
PetID	Number	FK to Pet.PetID
Date	Date/Time
CareType	Short Text	E.g., “Vet Visit”, “Grooming”
Cost	Currency
Notes	Memo

Wow!.. I didn't realize ChatGPT had that capability. That's powerful!

 

[DakotaRidge]

Yes, but you don't need new tables and forms to add extra pieces of information (data, datum) to a person record. You add new fields to the person record, and redesign the person form to include that field. In some cases you might decide to model it in a different way, where the person's attributes are stored in a person's attributes table, with each individual attribute in a different row.

Either way, you end up with 2 or 3 tables, not hundreds.

This second way is perhaps less intuitive, but you generally don't even need to change the database design to include new attributes with this model, and you never end up with too many columns.

You certainly don't need a new table for every person in any event.

If you have a table for every person, then no wonder you are struggling to delete the same piece of information for multiple persons.

It's also why we are all struggling to help, because the best advice is probably to start over. Sorry.

Thank Dave,
I don't have a different table for each family member. I am not sure why you think I do. And I do not have a different table for each grocery store, food recall, utility company, disease, medicine, and physician. There is a command button on one form for Walmart stores because most of my family frequently shop at a Walmart in Florida, Colorado, and California. The data comes from one table. There aren't different tables for Walmart, King Soopers, Publix, etc.

When you see frmMember 1, frmMember 2, and frmMember 3 in my screenshots, they are for different forms because they have different layouts designed for different user personalities. The forms have different graphics. Some Food Recall forms, for example, have maps of the US, while others will have pictures of squirrels and tigers or images of the solar system. Some forms will have icons indicating speech. Those command buttons read information aloud. Some forms have three speech icons because they read different information. This is true for food recalls. The data come from the same table, the messages are different.

Some users will want the minimum amount of information to be shown on a form, whereas others will want to see everything. So I may have four or five forms for ENFJs, INTPs, ENTPs, and ISTJs in my family. These are personality types defined by the Myers-Briggs Type Indicator (MBTI).

 

[DHookom]

If you don't have certain tables, can you provide some clue as to what causes you to have hundreds and why they are growing. Do any of your tables have similar structures? Help us understand what you have created.

 

[gemma-the-husky]

@DakotaRidge
Given your layout in #16, I presume you have a different form for each expense type listed. If you add more expense types, presumably you have to re-design this form and create more forms for the new expense types.

Really you need a single table of all the different expenditure classes, maybe grouped by a single lookup type as appropriate, so 2 tables. Conceivably you might need some other tables.

You can select the expenditure class you want with a combo box, or maybe a list box, and then you never have to redesign your database at all.

So your form would show a combo box to let you pick your expense class (the 20 or so black headers), and then drill down into the detailed expenses you want to consider within the selected header, by a combo box, or maybe a list box. So all of the detailed line items are hidden away until you need them, and you can add new line items by adding a new record to the expense category table, linked to the expense class. So a user could add an expense called "MRI scans" within the "Medical Bills" class, and you don't have to make any changes at all. The "MRI Scans£ category works like any other medical expense category.

Maybe some expense types need columns that are peculiar to those expenses, but that can be managed more easily than by proliferating tables and forms for each "special" category.

I don't use a personal expense tracker, but I'm sure the ones you can buy use a more generic and consistent approach that doesn't require them to release a new version every few weeks. Have you compared what you do with some of those.

For example, how on earth have you singled out "colonoscopy" as meriting special attention?

 

[DakotaRidge]

If you don't have certain tables, can you provide some clue as to what causes you to have hundreds and why they are growing. Do any of your tables have similar structures? Help us understand what you have created.

Hi Duane,

I have so many tables because my database does many, many things. It has two major domains: personal finance and health. Finance addresses income and expenditures. The expense domains include utilities, insurance, groceries, home repairs, etc. The grocery domain considers questions such as how much a family spends in a month at Walmart versus Safeway versus King Soopers? The Utilities domain considers the amount that a family spends on water, electricity, and gas. In addition to expenses, the utilities domain also considers the consumption. How many gallons of gas did the family use this month? How many kilowatts of electricity did the family use? For these, why did the amount change from month to another? Did the family water the lawn more in June than in December? Why? For phone costs, which service is more expensive? Is it T-Mobile or AT&T? Then should the family consolidate and use only one telco?

In the health domain, the database seeks to help family members understand the diseases that each person has. Do they eat bad foods regularly? What are bad foods according to dieticians? What do family members' medical test results show about their next medical issue? Will a person have kidney problems if he doesn't stop drinking alcohol? Which person needs to do more exercise? Most tables in the health domain have a field for each family member.

The database addresses each person's health separately. These are main form/subform constructs. The subforms are continuous forms. The subforms store data for each event/expense with a person's name, health issue(s), event dates, and cost to treat. Aggregate information will tell folks the frequency, total cost, and actions taken to prevent a problem from occurring again or worsening.

The database will track ER visits month by month. I have one family member who visits the ER every week. The staff has a room set aside for him. They probably have a GPS tracker in his car to tell them when he is on his way

In recent weeks, food recalls have been front and center. What foods were recalled yesterday? Which recalls were Class 1? Where in the country was the product shipped? How many people died from the product? Sometimes I think that I should stop eating food

Because I initially built the database for personal finance, almost every table in the health domain has a field for cost. The database tracks the cost of doctors' visits, prescription meds, health equipment, and over-the-counter meds. It will aggregate medical costs over time, by doctor, and by pharmacist.

The database uses the five W's for everything. I add new fields to tables when I discover that I forgot a question to address. That happens from time to time because I am always reading books, magazine articles, or website posts about finance and health.

I hope this helps, Duane. As I mentioned before, I am not a financial consultant or a physician. I read a little, and then I build a capability into the database. I am sure it has lots of flaws.

I am also sure that no one has ever developed a database like mine. How could they? No one knows my family's medical history, where they live, and what they eat. They don't know which individuals are on medicare, and which ones are on a company health plan.

Most developers have built applications for use by extroverts, and some have built them for introverts. But I think that most developers do not consider personality when they create a database. For them, normalization and technical issues are most important. Being introduced to MBTI in the federal government, I consider personality first and foremost. I consider reports to be more important than forms. Reports are how I share information with others. I use forms to enter data in the system. The forms will show the person what data is missing so he/she can make a decision about what to do next. You won't see spreadsheet-like tables in my database. The forms show context data for each person, disease, store, expense, etc.

The medical history of my family goes back generations. Members live from Florida to California. Some members have strict diets and others eat every that they see. Some are Black, some are White, some are Hispanic, and some are Asian. There are men and women. All of these differences need to be built into this database.

Thanks for helping me think through all of this. I haven't created a navigation diagram yet, I am afraid that Access may not be able to show all the linkages. My copy of Visio is ancient.

 

[DakotaRidge]

@DakotaRidge
Given your layout in #16, I presume you have a different form for each expense type listed. If you add more expense types, presumably you have to re-design this form and create more forms for the new expense types.

Really you need a single table of all the different expenditure classes, maybe grouped by a single lookup type as appropriate, so 2 tables. Conceivably you might need some other tables.

You can select the expenditure class you want with a combo box, or maybe a list box, and then you never have to redesign your database at all.

So your form would show a combo box to let you pick your expense class (the 20 or so black headers), and then drill down into the detailed expenses you want to consider within the selected header, by a combo box, or maybe a list box. So all of the detailed line items are hidden away until you need them, and you can add new line items by adding a new record to the expense category table, linked to the expense class. So a user could add an expense called "MRI scans" within the "Medical Bills" class, and you don't have to make any changes at all. The "MRI Scans£ category works like any other medical expense category.

Maybe some expense types need columns that are peculiar to those expenses, but that can be managed more easily than by proliferating tables and forms for each "special" category.

I don't use a personal expense tracker, but I'm sure the ones you can buy use a more generic and consistent approach that doesn't require them to release a new version every few weeks. Have you compared what you do with some of those.

For example, how on earth have you singled out "colonoscopy" as meriting special attention?

Dave, I would say that that is true. The database already has several navigation forms. I call one the Master, but it is several months old. I have run out of space to put more buttons on it. I am waiting for large monitor support.

One navigation form is for Finance, and one is for Health. One is a mix of Finance and Health. I am thinking about creating one for Food. When a person opens the database, he/she will tell it what is of most interest at the time.

Currently, I do not have subdomains for airline flights or cat care. I would have to think hard about what data to have in the Airline domain because I haven't been on a plane since Covid hit. The fields would be for baggage costs, carry-on bags, routes to and from the airport, toll road expenses, meals on board, rental car at destination, where to stay, and other things. None of these fields are currently in the database.

For the cat care domain, the database would store the names of each cat, their ages, genders, veterinarian's name, vet costs, food likes and dislikes, and their medications. Some of those fields could be the same as in the dog care tables/forms/reports. For each animal, all I would need to do is assign it a species. However, I would probably need special fields for cats versus dogs. I would do that because I like to keep data separate.

My forms would also need to aggregate data for dog meds, foods, and vets. I would have similar forms for cat care with aggregated data for them. I do aggregations in forms and reports, not in queries. I want the calculations to be simple so my users can change them themselves. I will teach folks about the sum, maximum, minimum, and count functions. That's all they need to know. They don't need to know about SQL or about opening a query wizard. They will never need to leave the form.

The diseases that cats suffer from may be different from the ones that dogs have, so I don't want a master list with pet diseases. I could use MVFs for animal diseases.

The other thing about my navigation map is that it has one button for each operation/task. I don't want my family members having to decide which buttons to click if they do not relate to them. So I may have ten Master navigation forms in the database.

I call the form in #16 my master navigation map. Each version of the database will have a Master, so I plan to work with each family member to understand what he/she wants to do with the database. I will then remove buttons from the Master and create forms for each person. For example, if a person suffers from diabetes but not have cancer, he won't see buttons for cancer in his navigation form. The diabetes buttons will open forms that that person needs to manage their health. My Master form will be in the .Accdb file but it won't be front and center. There are too many buttons on it.

If a person comes down with a new disease, all he will need to do is copy buttons about that disease to his master navigation form. Again, I want family members to use simple portals to get to where they need to go.

The database will open to the Master navigation form. That form can open secondary navigation forms, I haven't designed them yet.

Here's a quick-and-dirty chart that shows some of the linkages between subdomains. The chart does not show all the subdomains. Forms will open related reports. Each form and report will read aloud information about that domain. Images on forms will also be specific to that domain.

Thanks again. This information will help me develop my User Manual.

 

[DakotaRidge]

Dave, I would say that that is true. The database already has several navigation forms. I call one the Master, but it is several months old. I have run out of space to put more buttons on it. I am waiting for large monitor support.

One navigation form is for Finance, and one is for Health. One is a mix of Finance and Health. I am thinking about creating one for Food. When a person opens the database, he/she will tell it what is of most interest at the time.

Currently, I do not have subdomains for airline flights or cat care. I would have to think hard about what data to have in the Airline domain because I haven't been on a plane since Covid hit. The fields would be for baggage costs, carry-on bags, routes to and from the airport, toll road expenses, meals on board, rental car at destination, where to stay, and other things. None of these fields are currently in the database.

For the cat care domain, the database would store the names of each cat, their ages, genders, veterinarian's name, vet costs, food likes and dislikes, and their medications. Some of those fields could be the same as in the dog care tables/forms/reports. For each animal, all I would need to do is assign it a species. However, I would probably need special fields for cats versus dogs. I would do that because I like to keep data separate.

My forms would also need to aggregate data for dog meds, foods, and vets. I would have similar forms for cat care with aggregated data for them. I do aggregations in forms and reports, not in queries. I want the calculations to be simple so my users can change them themselves. I will teach folks about the sum, maximum, minimum, and count functions. That's all they need to know. They don't need to know about SQL or about opening a query wizard. They will never need to leave the form.

The diseases that cats suffer from may be different from the ones that dogs have, so I don't want a master list with pet diseases. I could use MVFs for animal diseases.

The other thing about my navigation map is that it has one button for each operation/task. I don't want my family members having to decide which buttons to click if they do not relate to them. So I may have ten Master navigation forms in the database.

I call the form in #16 my master navigation map. Each version of the database will have a Master, so I plan to work with each family member to understand what he/she wants to do with the database. I will then remove buttons from the Master and create forms for each person. For example, if a person suffers from diabetes but not have cancer, he won't see buttons for cancer in his navigation form. The diabetes buttons will open forms that that person needs to manage their health. My Master form will be in the .Accdb file but it won't be front and center. There are too many buttons on it.

If a person comes down with a new disease, all he will need to do is copy buttons about that disease to his master navigation form. Again, I want family members to use simple portals to get to where they need to go.

The database will open to the Master navigation form. That form can open secondary navigation forms, I haven't designed them yet.

Here's a quick-and-dirty chart that shows some of the linkages between subdomains. The chart does not show all the subdomains. Forms will open related reports. Each form and report will read aloud information about that domain. Images on forms will also be specific to that domain.

Thanks again. This information will help me develop my User Manual.

View attachment 120818

Folks, I should also say how I get ideas for my finance and health database.

I find one or two ideas every day on MSN. These ideas are commonly in slide decks with information about a specific topic, such as good foods to eat or foods to avoid.

MSN articles commonly have ten to sometimes 25 slides. I turn each slide into a record in my database. I don't like having more than 10 to 20 records in a table. Each record may have 10 to 20 fields.

I learned to think this way from Clayton Christiansen. Clayton wrote books about Disruptive Innovation when he was alive. His main lesson was that people can only deal with a limited number of things at once. If you give folks more than about ten things, they can't think about them all.

Before reading Christensen, I would put 20 items in my lists. After Clayton, I limited the number to 10. Because I have so many forms in my databases now, I will need to limit the information people see.

Some of the lookup tables that I use with MVFs have lots of items. Before I can share the database with my family, I will need to separate those items into logical groups. So, a form with 120 items in an MVF list could become 12 different forms. Folks on AW are always saying that I have too many forms. When they come back in six months, they will find that the number of forms has grown significantly. Just yesterday, I created a table/form to tell people what foods not to eat in the morning hours. The list only has five items now, but other sources may help me populate the list. The first item on the list is bacon, I love bacon. Each table like this has a field for the source of the information. I paste URLs into them.

I suppose this approach contrasts with what database experts were taught decades ago. What can I say? Christensen's ideas make sense to me.

 

[DHookom]

I would record expenses in a single table with fields that store the person, category, date, quantity, cost, comments, and maybe a few other columns. There is no way I would create separate tables for each type of utility.

Have you shared your ideas with any family members? There is no way I would consider rekeying data that is readily available on the web.

 

[DakotaRidge]

@DakotaRidge
Given your layout in #16, I presume you have a different form for each expense type listed. If you add more expense types, presumably you have to re-design this form and create more forms for the new expense types.

Really you need a single table of all the different expenditure classes, maybe grouped by a single lookup type as appropriate, so 2 tables. Conceivably you might need some other tables.

You can select the expenditure class you want with a combo box, or maybe a list box, and then you never have to redesign your database at all.

So your form would show a combo box to let you pick your expense class (the 20 or so black headers), and then drill down into the detailed expenses you want to consider within the selected header, by a combo box, or maybe a list box. So all of the detailed line items are hidden away until you need them, and you can add new line items by adding a new record to the expense category table, linked to the expense class. So a user could add an expense called "MRI scans" within the "Medical Bills" class, and you don't have to make any changes at all. The "MRI Scans£ category works like any other medical expense category.

Maybe some expense types need columns that are peculiar to those expenses, but that can be managed more easily than by proliferating tables and forms for each "special" category.

I don't use a personal expense tracker, but I'm sure the ones you can buy use a more generic and consistent approach that doesn't require them to release a new version every few weeks. Have you compared what you do with some of those.

For example, how on earth have you singled out "colonoscopy" as meriting special attention?

When I added colonoscopy to the database back in February, I had made two or three trips to gastro doctors with a family member. I wanted to capture the preparation, medicines, effort, and costs in my database.

 

[DakotaRidge]

I would record expenses in a single table with fields that store the person, category, date, quantity, cost, comments, and maybe a few other columns. There is no way I would create separate tables for each type of utility.

Have you shared your ideas with any family members? There is no way I would consider rekeying data that is readily available on the web.

Yes. Duane. I send one or two emails almost every day to family members to describe features in the database. Some folks get it and some don't. I can tell that folks are paying attention because they send me URLs that I can use to populate tables in the database.

The only data that a person needs to key in is their personal data. For example, how much did you spend at Walmart or another grocery or hardware store this week?

Money experts like Dave Ramsey recommend that you track expenses in detail for at least a month. Every expense, income from all sources, and other information needs to be entered into a system.

Before I started working on the database, I did not know that my average grocery spend each week was $160. Since I created the database, I have reduced my household expenses because now I follow Ramsey and Kiplinger. My grocery and eating out costs are lower.

If you don't want to key your data into a database, hire the 12-year old kid next door. Dump a stack of store receipts in front of her and go back later. Then run your analytics. I keep as much of my data off the Net by paying cash. Car purchase - cash. Wine purchases - cash. Library fines - cash.

Also Duane, your forms and reports may not have graphics in them. All of mine do.

 

[DakotaRidge]

I would record expenses in a single table with fields that store the person, category, date, quantity, cost, comments, and maybe a few other columns. There is no way I would create separate tables for each type of utility.

Have you shared your ideas with any family members? There is no way I would consider rekeying data that is readily available on the web.

Duane, how would you record expenses related to doctor visits if you also record medical test results for each member of your family? Here's part of the medical test result dashboard in my database.

The dashboard uses conditional formatting to indicate if a person's test results are in or out of range compared to a healthy person of the same age, gender, and race. Two buttons in the upper left corner hide and unhide the standard ranges.

The four buttons for Diseases open forms with detailed information about those conditions. The dashboard is designed to show if each family member is healthy or not, and which diseases to address by eating the right foods and taking the right medicines. That information is also stored in the database. Doctors conduct these tests twice a year. The data for each member of a family are stored in one Access table that is linked to other tables that have information about family members. The table is also linked to tables that store standard data for the different segments of the population. Ignore the large numbers for now, I am still working on them.

 

[DHookom]

Duane, how would you record expenses related to doctor visits if you also record medical test results for each member of your family? Here's part of the medical test result dashboard in my database.

The dashboard uses conditional formatting to indicate if a person's test results are in or out of range compared to a healthy person of the same age, gender, and race. Two buttons in the upper left corner hide and unhide the standard ranges.

The four buttons for Diseases open forms with detailed information about those conditions. The dashboard is designed to show if each family member is healthy or not, and which diseases to address by eating the right foods and taking the right medicines. That information is also stored in the database. Doctors conduct these tests twice a year. The data for each member of a family are stored in one Access table that is linked to other tables that have information about family members. The table is also linked to tables that store standard data for the different segments of the population. Ignore the large numbers for now, I am still working on them.

View attachment 120842

The visit record can have a field for the ExpenseID.

Honestly, I think you are wasting your time attempting to build an unmaintainable application. Healthy person ranges change over time. The latest and best disease information can be provided with a link to a web page like Mayo Clinic or WebMD. There are over 7,000 diseases. Plus, you are playing doctor which could be dangerous.

I don’t think you have accepted hardly any advice from the experts in this forum. Apparently you are very intelligent but this application is a rabbit hole.

 

[DakotaRidge]

The visit record can have a field for the ExpenseID.

Honestly, I think you are wasting your time attempting to build an unmaintainable application. Healthy person ranges change over time. The latest and best disease information can be provided with a link to a web page like Mayo Clinic or WebMD. There are over 7,000 diseases. Plus, you are playing doctor which could be dangerous.

I don’t think you have accepted hardly any advice from the experts in this forum. Apparently you are very intelligent but this application is a rabbit hole.

 

[DakotaRidge]

Thanks, Duane. I have a Cost field in the med records table. I have cost fields throughout the database for everything that I can think of. I also have fields for sources of information for Mayo, Cleveland, CDC, FDA, and for each MSN article that a family member may use.

The Health section of the database is designed so each person can enter their own information into the database. After your comment, standard data will be date-stamped and sourced. Each person's medical data is sourced to his/her primary doctor and their specialized doctors.

Actually, the only advice that I haven't accepted is splitting and removing MVFs. You may also say that I haven't accepted reducing the number of tables and forms. Those are design constraints, they are mine.

7.000 diseases, I need to get back to work! But remember, I am building this database only for my family. If they don't tell me that they have a disease then I won't do the research to add it to the system.

 

[DHookom]

My previous post still applies. Good luck.

 

[DakotaRidge]

I would record expenses in a single table with fields that store the person, category, date, quantity, cost, comments, and maybe a few other columns. There is no way I would create separate tables for each type of utility.

Have you shared your ideas with any family members? There is no way I would consider rekeying data that is readily available on the web.

Duane, I forgot to say that more than half of my family members don't use the Internet. It will be a real challenge for me to get them on my system. I know this for a fact and I accept the challenge.

 

[BlueSpruce]

the only advice that I haven't accepted is splitting and removing MVFs. You may also say that I haven't accepted reducing the number of tables and forms. Those are design constraints, they are mine.

Keeping the MVF's is fine, however, not splitting your app into front and backend is going to comeback to haunt you every time you want to provide updates to your users. You would have to remote into each user's PC and update their app so you don't overwrite their data with your copy of the updated app, or you would have to send them an accdb that only contains the new forms, reports, and queries, and they would have to import those new objects into their app. You would also have to locally alter their tables to add the new fields to their apps. The other alternative is for them to send you a copy of their apps for you to make the changes and return it back to them. All the above can be avoided if you split the app into FE BE. You can write queries to add new fields to their tables, they just have to run the queries on their copy of the BE.

7.000 diseases, I need to get back to work! But remember, I am building this database only for my family. If they don't tell me that they have a disease then I won't do the research to add it to the system.

Your users should be able to add their own diseases in the lookup table without you having to add new fields, queries, tables, or forms. If you continue building this app with the denormalized design, you'll quickly bloat the db with thousands of objects. I urge you to reconsider the advice others and I have repeatedly given you throughout time.

 

[CJ_London]

more than half of my family members don't use the Internet.

I will be interested to know how you will manage your provision of information accessed by a hyperlink and how you expect them to update the data around product recalls etc.