Clear out data (5 Viewers)

[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.

 

[DakotaRidge]

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.

Everyone,

I don't plan to be involved in maintaining the database once I send it out to my family members. It will be their job to add their personal financial and health data to the system. This is like most COTS were in the day. Think of MS Money, not Quicken.

I consider all of that data to be sensitive information. A person may send their database to another family member, a son or daughter, but that will be their decision. This is why I asked AW experts about removing personal data.

I consider the maintenance of an Access database to involve fixing code and problems created by MS. This is why I don't use code. There is code in the database, but very little of it is used. The database uses macros to do most things, like opening forms and reports. I figure that I can explain how to create or change a macro over the phone. I may need to create screenshots and email them to folks from time to time.

Also, the database uses very little SQL, and what it does use was created with the query wizard. I use filters instead of queries. Most times, they are all that I need to use.

Calculations are done on forms using simple aggregate functions that look like the ones in Excel. I am hoping that family members know a little about Excel. I can explain how to create a calculation in a form over the phone as long as it involves simple functions such as Count, Minimum, Maximum, and Sum. I can even tell someone how to count checkboxes over the phone. The checkboxes on forms in the database usually have different names, so using them is easy, but it can take time. Most people in my family have lots of time on our hands, we are all over 70 and retired. One of us is in his 90s.

That brings up the topic of ease of use. I am designing the database with lots of graphics and speech for that reason.

 

[DakotaRidge]

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.

CJ, I expect that some but not all family members will copy blocks of text from the web into the database. This is why many of the textboxes are large enough for extra words and sometimes a second sentence. When I expect a lot of text to be pasted into a form, I use Long Text fields.

Information about food recalls is commonly a full page. It depends on the source of the information. To create a complete record, the person may need to use the CDC and the FDA websites after using MSN or Newsweek.

If a person copies the entire announcement into the database he/she won't be able to do certain searches. But with a little extra effort, the person can paste the symptoms of a disease into the correct box. And it is not required that all of the textboxes be used for a recall. I would suggest that a person always parse out the information for Class 1 recalls, but not always for Class 2 and 3 recalls.

To track the states where a product was distributed, the person won't need to type in state names. All he/she will do is check boxes on the recall map. That will be even easier if a person only wants to show when a product was distributed to their state. That will be the case if a person doesn't have relatives out of state. With no out-of-state relatives, the person really won't need the recall map form. That form can be deleted from the database. The person would use another form to enter information about recalls.

I have used as many click-to-enter Access controls as I can. Date fields use the pop-up calendars.

The Recall Map has 50 checkboxes for the states. Other checkboxes are for the types of illnesses, whether store receipts are required for returns, and on and on.

Many of the lists are MVFs, so they can reduce typing. The names of common illnesses and diseases use MVFs. The names of family members use MVFs.

Checkboxes, MVFs, calendars, and other objects are used throughout the database for finance and health. Some lookup tables are used in MVFs in both the finance and health domains. This is why there are currently 465 tables in the database. There are more tables than forms (455) because many tables are used multiple times.

If you develop a database with hundreds or thousands of objects, use as many click objects as you can. They will make it easier for your users. Which objects to use in your forms may not come to mind during design, so be open to adding them later. You will need the same objects in your reports, but they will frequently be in different places. Most times, I create several versions of forms and later copy specific ones into reports. I often consider reports to be more important than forms because they are distributed and used for decision-making purposes.

HTH.

Thanks for the comment.

 

[Pat Hartman]

One need that I will have is to have public information in a table. When I distribute the database, it will have several records that will show a person how to populate the table. I need those records because some people may not understand what a report needs to do its job correctly. Some fields will be easy to understand. like Medicine Cost.

This is what documentation and validation are all about. You can add little question marks next to fields that explain the rules and use when the user clicks on them EVERY form that does data entry needs validation at a minimum to ensure all required fields have been entered. You can do some validation using rules a the table level but you can generate better error messages if you do the validation in the form.

 

[DakotaRidge]

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.

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.

Family members will be able to add other diseases and items to the database.

Every MVF in my database uses a lookup table to edit data and that includes all of the MVFs in the Diseases subdomains.

Each lookup table has a form for editing the information. Family will be able to add new diseases for people and pets.

Family members do not need more fields in the master table for diseases. There are currently 25 fields in the surgery subdomain for heart disease.

I am not ready to split the database. I have very little experience splitting databases, and it looks like I have 6980 more diseases to research. I am going to need more sleep or an army of helpers.

From time to time, I ask family members about the diseases that they suffer from. I have researched the most common ones and added information to the database. There are so many diseases, which is why I purchased Mosby's a few months ago. One family member suggested that I get the Physician's Desk Reference. I really don't want to have to go to med school. I am too old.

 

[Pat Hartman]

@DakotaRidge, I kno you're going to distribute improvements to this app as time moves on, so spliting the app into front and backend is crucial. Otherwise, you would have to distribute accdb's that only contain the objects you have modified, and the users would have to import those modded objects into their accdb's in order to prevent their data from being overwritten by your updated version that has both the mods and your data within the same accdb. Make life easier for yourself and users by splitting the app.

It goes well beyond this. Adding new columns to an existing table is fairly common after the app has been distributed. Say you have 5 users, each with their own personal data. The data and the application objects are all in a single .accdb. Exactly what is your plan? Are the users going to stop using their app for a short time so they can send it to you and you can update all the GUI objects that need updating and you can MANUALLY handle fixing the table changes without loosing/changing the existing data? I tried to explain this to you earlier but got no response. Maybe now you will think about this.

You have made a nightmare of perpetual maintenance for yourself. You have taken responsibility for the validity of your user data. Did you really intend to do that? Because you have done it by using your non-professional methods. People who do this for a living understand the maintenance problem and have planned for it and not created a database that required manual maintenance for each and every copy of the application that you distribute. Your users don't know anything about programming and they shouldn't. This is something you really need to hide from them because THEY simply cannot be trusted to do it themselves manually. We really aren't lecturing you to hear ourselves speak. Our concern is for you and the can of worms you have created.

 

[Pat Hartman]

Every MVF in my database uses a lookup table to edit data and that includes all of the MVFs in the Diseases subdomains.

And how many lookup tables do you have? And did you bother to look at the mini-app I provided a link to so that the maintenance of lookup tables can be managed simply without hundreds of tables and forms and queries to manage those tables.

 

[DakotaRidge]

This is what documentation and validation are all about. You can add little question marks next to fields that explain the rules and use when the user clicks on them EVERY form that does data entry needs validation at a minimum to ensure all required fields have been entered. You can do some validation using rules a the table level but you can generate better error messages if you do the validation in the form.

This is what documentation and validation are all about. You can add little question marks next to fields that explain the rules and use when the user clicks on them EVERY form that does data entry needs validation at a minimum to ensure all required fields have been entered. You can do some validation using rules a the table level but you can generate better error messages if you do the validation in the form.

Thanks Pat. With a thousand forms/reports in my database, I am thinking about creating a detailed users manual. I suppose each form/report will need two pages to describe it. I hope to start working on the document when I finish building the database.

 

[Pat Hartman]

I am not ready to split the database. I have very little experience splitting databases, and it looks like I have 6980 more diseases to research. I am going to need more sleep or an army of helpers.

Not if you instead learn how to normalize your data so that each disease doesn't require its own table. Do you think a hospital application includes a table for each separate disease? Let me help you out - it doesn't!!!!!!!!!!

Also during the time it will take you to research and hard code fields for the 6980 "missing" diseases, how many new diseases might be discovered? What happens if a user is diagnosed with one of the undefined diseases before you get around to creating the tables for it?