Clear out data

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