Clear out data

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

 

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

 

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

 

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

 

[DakotaRidge]

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.

A scan suggests that I have more than 140 lookup tables.

Sorry, I haven't looked at your mini-app yet. I had other subprojects in the queue. I created a hundred more forms last week. I will try my best to get to it soon. My research takes time, and I had to get up yesterday at 3:30 am to go the ER.

 

[DakotaRidge]

If you decide to add "help" that is accessible by ? buttons, Create the help in a table so if you need to describe blood pleasure in more than one place, you only need ONE help entry for it. You can create text abbreviations or you can use numbers that are hardcoded. Something must connect each ? button with a help entry.

Pat, that's a great idea. I also use text-to-speech to communicate with users. There's usually one Speak icon on every form. The Recall Map form has three Speak icons because I got carried away on it.

Most of the forms and reports will not need entries help after I explain them once or twice to family members. And I am not the person they want describing A1C or hemoglobin tests to them.

 

[DakotaRidge]

What planet do you live on? I can't think of a single food recall that has ever affected me personally and I've been cooking for the family for 60+ years.

Pat, today go to the Newsweek website and check food recalls. No, go to the CDC and FDA. Better yet register for recall announcements at all of these sites. I am seeing a recall announcement almost every day.

If you live in certain areas and you watch what you eat, you may never have a problem with contaminated food. The likelihood of you eating food contaminated by listeria or e-Coli is very small, I grant you that. I am a very cautious person. Since Covid hit in 2020, I have not traveled more than 10 miles from my house. It may be another year before I go further. I had five shots, and I am not against wearing a mask even now.

 

[DakotaRidge]

Some research should take priority. I'm not saying that you should immediately go back and remove all your unnecessary lookup tables. You can leave them alone until such time as you have to do maintenance on a form that uses one of them. Then convert only that single table. But start IMMEDIATELY using the mini-app for all new lookups and save yourself from having to create all those new tables and forms. Baby steps. Eventually, you may want to fix all your earlier poor choices but there is no reason to stress about doing them now since that will interfere with moving on. Learn as you go. Adopt new ideas for new parts of the app.

I know we all emphasize consistency. Consistency is your friend. The more consistent your methods and coding style are, the less "thinking" and "decision making" you have to perform on the fly every time you start with a new task. If I come up with a method that is superior to the one I was using, I switch to the new method and then if appropriate, modify the old code when I have to maintain it. I almost never just go back and change old code UNLESS it is an efficiency problem. If a query runs for 10 minutes and I come up with a better method, I will take the time to replace the old version with the new, every place it is used because the payback is worth the effort. Obviously, the the new version fixes an error, that is also worth changing. AND keep in mind, that as you get better at this, it is easier for you to figure out when/how to reuse code/queries. So, if I fix old code that is slow, chances are excellent, I can fix it once and have it impact 20 procedures if the code is generic. If the code is not generic, I might take the time to make it generic now!

Pat, I don't code. I have a little code in my Personal Finance and Health database, but I rarely use it. I use macros instead. I am forced to use code for text-to-speech, but that's about it. Code is for developers, and I am not one.

I am a visual thinker. All of my forms have graphics, and most of them have speech. That means that I probably have 500 images in my forms collection. When a form does not have a graphic, it has checkboxes or boxes for conditional formatting. One form has about 45 boxes with conditional formatting.

Yes, consistency is required. All of my forms use the same colors, fonts, and so they have the same look-and-feel. I do add fields to tables and objects to forms when I discover that I need something new. It took me a few weeks to find a recall announcement that included Class 1, 2, and 3. I added three Yes/No fields to the table, and I placed boxes and labels on the recall form. I had no idea that the government had a classification scheme like that. Now if an announcement says Class 1, I know that the recall is for a serious situation. Class 1 recalls may warn about the possibility of death or a long stay in the hospital. The keyword here is MAY.

 

[DakotaRidge]

A larger monitor is not the solution. It is not necessary to see every option for every group on a single screen as you showed with the picture on #16. It is that picture that makes your app look worse than it is. It is like you are trying to put an entire table of contents on a single form. Why would that every be necessary? Would you ever do that with a book of any size? If something is small enough in scope, then that type of view might be usable but not for many things. A TOC that you see on a web app might be better. It expands as you click. The nice thing about it is that you NEVER need to hard code forms. Each expansion opens a "list". The data for the "list" is obtained from a table. The table has sorting and grouping fields so if you want to move something around, you change the sorting/grouping values AND, you can even organize the TOC that a section might show up in multiple locations if that makes sense.

Hard coding is your enemy. It forces you to create forms that are totally unnecessary and it forces you to make layout/coding changes that you would never have to make if the app were properly data driven.

Thanks, Pat. I am always looking for better ways to design forms and reports. I created the screenshot in #16 mainly for me. I won't expose it to family members because it has too many buttons. I know that. Over the past few months, I created several other navigation panes. I have shown some of them to family members, but they know none of those forms will work down the road.

When I have looked at the panes created by developers, I never see any of them working for me. With hundreds or thousands of forms, and hundreds or thousands of reports in a database, I need customized navigation panes.

I created a fancy pane three weeks ago. It has about 10 colorful shields. I place command buttons on them to open the forms. The forms are main forms with subforms. The subforms are continuous forms. Continuous forms are how I add normalization to the database. For example, the Grocery domain has a subform set to continuous. The subform lets me track my weekly grocery expenses. Some weeks, I go to Walmart twice. I may go to the Walmart west of my house or the one to the north. To distinguish them, I have a field in the form for location/address that uses a lookup table. It is not an MVF. Some here may ask why have a location field? Well, I want to know how far I drove to shop at stores during the year. That may not be important to other people, but it is to me. I also know most of the staff at one Walmart, they are my friends.

Except for pages on graphics on Bing, I have never seen any navigation panes. Several years ago, I got help from Lorenz Holscher in Germany. He took some of my forms apart and told me how to make them look professional by using an attractive font. He told me when to left justify and when to center text in boxes.

I also learned not to put borders around textboxes. Now I hate textboxes with borders, but I see them all the time. All of my textboxes use the Arial typeface, never some other typeface. That's interesting because I spent 25 years in the government, where Courier was it for all documents.

 

[DakotaRidge]

That is also a huge mistake. Macros are significantly harder to create and logically confusing compared to code. Code (at least VBA) mimics English and the logic you use when thinking about how to do a task. You just don't know that because you have handicapped yourself to use only macros. The mini-app for table maintenance has very little code.

Code is for people who are not masochists. You are crippling yourself by using macros rather than code. You are also forcing yourself to think illogically because macros are not logical. You are preventing yourself from reusing code because nobody reuses macros. They are too clumsy and hard to reuse.

With macros that open forms and reports, I create them from scratch in a few seconds. It's so easy. Then if I need to reuse them, it is just Copy and Paste. I copy macros from one form to another, and from one database to another without changing anything. They can be difficult to create, but I stay away from those uses.

The one piece of code that I know I need in many of my forms/reports and in any new Accdb file is for text-to-speech. The interesting thing about that code is that it started life as a Word macro. I discovered it about two decades ago, I converted it to Access VBA, and I have been using it in Access ever since. I even used it once in an Excel workbook. Sorry, I had a weak moment because no one on my team was using Access.

 

[Isaac]

I have not traveled more than 10 miles from my house. It may be another year before I go further. I had five shots, and I am not against wearing a mask even now

Do you also wear a condom when lying in bed alone?

 

[DakotaRidge]

Then you actually know very little about COVID. But we've covered that in other threads so we won't go into it here. You have crippled yourself because you allowed the lame-stream media to terrify you. Remember the mantra for the media - "if it bleeds, it leads". They live and die by this. So, their objective is always to exaggerate scarry stuff so they can terrify you into more clicks. You are only click bait to them. Statistically - the early models were waaaaaaaaaaaaaay off target and no one backed off or apologized for the early mistakes. The elderly and the ill were in jeopardy of dying from COVID but the young and health were in significantly less risk. In fact, children are at far more risk from the vaccine than they are from the disease itself. The early death counts were significantly inflated because of the rules posted by the CDC for how COVID deaths should be counted in addition to the federal payments for anyone treating anyone even potentially infected with COVID. These two "rules" greatly inflated the actual death count and much of that has been cleaned up in the ensuing years. For at least the first year, we had no valid test to determine if you were infected. Therefore, if you had even minor symptoms or could have had COVID and you died, you were reported as a death FROM COVID. So, we have many traffic accidents listed as death FROM COVID. During the first three years of COVID, few people died of the flu. Hmmmm. Strange.

Interesting, I was getting ready to go back out in public last month when I had a talk with my neighbor. He told me that one of his friends attended an event in the city recently. The person came down with Covid and died. Another friend told me someone else he knows came down with the disease. I don't know if that person died or not. I have a friend in California who has had Covid twice. His cognitive abilities have been seriously impacted.

I have had no Covid symptoms since it hit, but I stay away from people as much as possible. A couple of weeks ago, a worker at Walmart coughed twice in the area where I was shopping. I left the store without doing all of my shopping that day.

Last week, I was very pleased to see an elderly couple wearing masks at Walmart. I felt like thanking them for being good people.

I know that some people are more susceptible to diseases than others. I am glad that Covid stayed away from me, and I have done everything that I can to keep it away from others.

I have the same philosophy with food recalls. I tell family members anytime there is a recall in their state.

 

[DakotaRidge]

What planet do you live on? I can't think of a single food recall that has ever affected me personally and I've been cooking for the family for 60+ years.

Pat, I tell my family members that I am from the dark side of the moon. One of my friend's ashes are up there. I would love for NASA to bury me near him. The smartest person that I have ever worked around.