Clear out data

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

 

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

Duane, this afternoon, I pulled out my copy of Mosby's Manual and I started looking through all the medical tests. Then I looked at Wayback to find a list of the 7.000 diseases. I am blown away by all of the tests and diseases.

How would you recommend I get a handle this? So far, I have only done a small amount of research on a few diseases in my family, such as diabetes, heart disease, and kidney disease. Is that a good way to proceed -- ask relatives and ignore other diseases?

When I have a list of diseases, I can study relevant tests in Mosby's Manual of Diagnostic and Laboratory Tests. Mosby's is over a thousand pages. I'll be up all night

Thanks.

 

[DHookom]

Duane, this afternoon, I pulled out my copy of Mosby's Manual and I started looking through all the medical tests. Then I looked at Wayback to find a list of the 7.000 diseases. I am blown away by all of the tests and diseases.

How would you recommend I get a handle this? So far, I have only done a small amount of research on a few diseases in my family, such as diabetes, heart disease, and kidney disease. Is that a good way to proceed -- ask relatives and ignore other diseases?

When I have a list of diseases, I can study relevant tests in Mosby's Manual of Diagnostic and Laboratory Tests. Mosby's is over a thousand pages. I'll be up all night

Thanks.

I recommend you handle this by putting your intellectual talents into some other venture.

 

[DakotaRidge]

Duane, this afternoon, I pulled out my copy of Mosby's Manual and I started looking through all the medical tests. Then I looked at Wayback to find a list of the 7.000 diseases. I am blown away by all of the tests and diseases.

How would you recommend I get a handle this? So far, I have only done a small amount of research on a few diseases in my family, such as diabetes, heart disease, and kidney disease. Is that a good way to proceed -- ask relatives and ignore other diseases?

When I have a list of diseases, I can study relevant tests in Mosby's Manual of Diagnostic and Laboratory Tests. Mosby's is over a thousand pages. I'll be up all night

Thanks.

Duane, family members will be able to maintain standard data because those numbers will be stored in the database in a lookup table. Each record in the test standards table has a low and a high value for each test. Folks will be able to change the values as they age. There will also be flags so they can select their race and gender. Here's what the Liver panel looks like now. There will be similar panels for other diseases. What do you think? The talking face icon reads comments about each person's health aloud.

 

[GaP42]

Just as a side note - your reliance on Myers Briggs may be somewhat over confidence: to quote from wikipedia:

"The Myers–Briggs Type Indicator (MBTI) is a self-report questionnaire that makes pseudoscientific claims to categorize individuals into 16 distinct "psychological types" or "personality types".

And further:

The MBTI is widely regarded as "totally meaningless" by the scientific community.

Whether that has an impact on your design that is another matter. Such personality / psychological tests are often subject to revision as further knowledge develops, however Myers Briggs is well known for having a flawed methodology in its development.

And just as a further aside, as someone who has been involved in the health system and disease monitoring, the range of disease, the categorisations, the symptoms that pertain to each, the drugs that might be prescribed for each is a VERY complex field. You could spend a lifetime, full time in simply maintaining an accurate representation of this: two areas I have had experience with is the International Classification of Diseases (US uses ICD-9 - as a means of cost management, however when I left work we were on ICD 10 and moving to ICD 11) and SNOMED CT - the Systematized Nomenclature of Medicine: Clinical Terminology, and that does not include the linkage the drugs, commercial names and dosages. Good luck with that. I would not go near trying to implement, as seems to be your want with a barge pole - full listing of SNOMED CT on your access db as it will exceed to 2GB limit. Rather you need to be able to be flexible to add in sensibly only those items that are actually experienced by your "clients" - in a generic normalised schema where a disease is experienced by one or more people (for a duration or chronic) treated though various modalities/ procedures/ drugs (with different effectiveness/side-effects), with changing status of disease progression, and changing reported symptoms (that may or may not relate to that disease). At a simple level a client has one or more diseases. They may also undergo one or more treatments for a disease (a principally for the treatment of the specific disease experienced because some may treat more than one disease type) - so unlike what appears in your database - the drugs used by a client in the treatment of a specific disease can be identified with the specific dosage/ frequency applied for a specified period of time

 

[DakotaRidge]

Just as a side note - your reliance on Myers Briggs may be somewhat over confidence: to quote from wikipedia:

And further:

Whether that has an impact on your design that is another matter. Such personality / psychological tests are often subject to revision as further knowledge develops, however Myers Briggs is well known for having a flawed methodology in its development.

And just as a further aside, as someone who has been involved in the health system and disease monitoring, the range of disease, the categorisations, the symptoms that pertain to each, the drugs that might be prescribed for each is a VERY complex field. You could spend a lifetime, full time in simply maintaining an accurate representation of this: two areas I have had experience with is the International Classification of Diseases (US uses ICD-9 - as a means of cost management, however when I left work we were on ICD 10 and moving to ICD 11) and SNOMED CT - the Systematized Nomenclature of Medicine: Clinical Terminology, and that does not include the linkage the drugs, commercial names and dosages. Good luck with that. I would not go near trying to implement, as seems to be your want with a barge pole - full listing of SNOMED CT on your access db as it will exceed to 2GB limit. Rather you need to be able to be flexible to add in sensibly only those items that are actually experienced by your "clients" - in a generic normalised schema where a disease is experienced by one or more people (for a duration or chronic) treated though various modalities/ procedures/ drugs (with different effectiveness/side-effects), with changing status of disease progression, and changing reported symptoms (that may or may not relate to that disease). At a simple level a client has one or more diseases. They may also undergo one or more treatments for a disease (a principally for the treatment of the specific disease experienced because some may treat more than one disease type) - so unlike what appears in your database - the drugs used by a client in the treatment of a specific disease can be identified with the specific dosage/ frequency applied for a specified period of time

Thanks, GaP42. The purpose of my Personal Finance and Health database is only to help my family with these two needs. The database is not designed to be a commercial product that has data about the 7,000 diseases, all medicines, and all food products. That would be waaaaaaaaay over my head.

I purchased a copy of Mosby's a month or two ago from a Goodwill-like store. I may need to take a class in medicine to make good use of it. It's a thousand pages.

Yes, some psyches have attacked MBTI for years. I took a leadership workshop when I was in the federal government, and the consultants running the workshop used MBTI. I let it sit for about two decades, but when I was working on a proposal management database several years ago, I decided to use it to help proposal professionals understand members of their teams. I figured that it was better to use MBTI than nothing. I considered using The Big Five and the Golden Personality Profile, but I never found enough literature to understand those systems. The literature for MBTI, on the other hand, is extensive. I even called and talked to John Golden one day. I would gladly use Golden if there was enough literature for it. John was living in Coral Gables, Florida, when we talked. I am from Florida.

My goal is not to recommend treatments for diseases or illnesses. My goal is to make family members aware of the costs of not dealing with medical issues early. I figure that a person should have one or two physicals each year, go to a doctor when he/she notices an issue. and plan for more medical expenses in later life.

I have only been building health capabilities into my database for the past three months. Again, the main focus is on the cost. I pay my medical costs using a health insurance plan that I started decades ago when I was in the government. I am also on Medicare.

Because of my personality, via MBTI, there are medical things I will and will not do. I have no problem with vaccinations, but I will refuse any surgery, and I won't take blood samples for diabetes.

MBTI nails my personality to a T, which is why I like it. Only about 3% of the population has my personality. So when database developers disagree with the way that I develop databases, I know why. I am a visual thinker, I am an introvert, and I am not judgmental.

My database may have fields for dosages and frequency, but the focus is on test results. My doctor runs 46 tests on me. See my previous posts.

 

[DakotaRidge]

Thanks, GaP42. The purpose of my Personal Finance and Health database is only to help my family with these two needs. The database is not designed to be a commercial product that has data about the 7,000 diseases, all medicines, and all food products. That would be waaaaaaaaay over my head.


GaP42, I forgot to invite you to join in the conversation anytime I write anything stupid. Before January, I had almost no knowledge of diseases, personal finance, and even less knowledge of advanced database design. I was a geologist for 25 years and in the Army before that. So, hit me with both barrels. Thanks.

 

[BlueSpruce]

I am not ready to split the database. I have very little experience splitting databases...

It's okay to not split the db while you're developing it. However, I urge you to start experimenting with splitting it now because when the time comes for you to deploy the app to your users, you best provide them with separate front and backends. Otherwise, you're going to experience nightmares every time you update their applications. It's really not hard at all to split the db. Use the wizard. You have me and others here in AWF if you need any help.

I have 6980 more diseases to research. I am going to need more sleep or an army of helpers... 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.

I feel you intentionally take on these huge tasks to keep busy. There's no need to provide users with 6,980 diseases in a lookup table, nor research that many diseases. It's not practical. Let them enter their own diseases. You can provide some example diseases, like diabetes, in the template data you're going to distribute. Let your users build their own data and you periodically review what they have added so you can guide them and make adjustments that will provide a streamlined experience.