Clear out data (3 Viewers)

[Uncle Gizmo]

You could replace those collums with subforms:-

A Look at the Multiple Checklist Code - Nifty Access

 

[DakotaRidge]

Nothing you have provided, including latest post, counters viewpoint that this db is likely not optimized. And unless you share it for review, we can hardly be swayed. However, since it seems you are set on continuing with this design, I wish you luck.

Did you at least consider recommendation for splitting? Certainly should if you plan on maintaining and providing other users with updates.

Hi June7.

 

[DakotaRidge]

You show us a switchboard or dispatcher form that you can use to select reports or forms or both. Doesn't matter what they pull up, but it DOES matter what those things in turn pull up for you and from what those things pull something up. From your display in post #17, if those items are reports that track or forms that operate by filtering a category table, that could be perfectly OK. Which is why I'm not pointing fingers at the moment.

However, if those lower-level options represent a form or report based on separate and distinct tables for each thing you are tracking, ...

a) Your Grocery Bills selection is DEFINITELY denormalized if you have tables for each grocery. That should be 1 and only one table for that with the grocery name or ID or code as a field.
b) Your Insurance Bills represents another likely denormalized area that would need serious normalization.
c) I suspect the Diseases category has the same susceptibility.

Like I said, you showed us something that could be quite reasonable. But you didn't show us enough for us to know the difference between totally normal and totally outrageous. So with a little more insight, we might be able to smooth out that "itch" that each of us feels in the presence of a vaguely suspicious project description.

Thanks Doc_Man, the forms will allow my family members to enter the data that they will need to save money and protect their health. I am sure of that because I have stored information from some of the best minds in the world and several government agencies in the database already.

I am building the database for the way that I think and work Only 3% of the population has my personality according to the Myers-Briggs Personality Type literature.

All I need now is a way for family members to delete records with sensitive data. So far, no one here has offered me any ideas to do that. I code as little as possible, and I will need to convert code into macros for this database. I am sorry but I don't understand how normalization affects record deletions.

I am developing the application using an Agile development process. That means that I expect to change the structure in the future. Before I can do that, I need to add all of the functionality that family members tell me they need.

 

[DakotaRidge]

Yeah, when building a house just slop some concrete in a hole, throw in some rebar and build on top of it. In a year or two--after you've done the important things like paint the 4th bedroom the proper color of eggshell and chosen the right cabinet fixtures for the third floor bathroom you can always go back and fix the lesser issues like the foundation.

Plog, this is like building an entire city for people who have lived in the desert all of their lives. The first thing that I usually do is paste two pictures into a form, and then I add text for the form to read aloud. A family member tells me what the form will say.

 

[DakotaRidge]

I do not know what world you are in, but I can guarantee that everyone that knows anything about databases does not visit that world.
Being proud of the excessive amount of non-normalized data tables is like walking into a hoarders home and they are proud that somewhere laying on the floor is every piece of junk mail they have ever received. When you say in the next year you will have more tables, is a definite sign that the database is designed incorrectly. Databases do not grow in tables as data grows. They grow in records.

But good luck on this. Sounds horribly painful.

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

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

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

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

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

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

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

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

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

Thanks for your interest.

 

[arnelgp]

You can also do the Deleting or records from another database.
open form frmTableList from the demo and select the external db.
then select which tables to delete records from (note the Listbox
will Not Contain linked Excel/text file and the DB to open is not
encrypted with password).

the demo was made in A2021.

 

[DakotaRidge]

Looks to me that you could support that information with 10 or 15 tables/child tables (20 max). What are the other 430 doing?

Pete, just because there are several forms and reports for grocery purchases, that does not mean that there are multiple tables for grocery stores. This is a situation where I created a form for the Walmart to the west of my house, the King Soopers to the north, and the Safeway to the south, plus the Costco down the street. They are different kinds of stores and I shop them in different ways. Some deliver groceries and some don't. So a form doesn't necessarily mean what you think. The different forms have different pictures and they calculate total costs differently.

 

[DakotaRidge]

You can also do the Deleting or records from another database.
open form frmTableList from the demo and select the external db.
then select which tables to delete records from (note the Listbox
will Not Contain linked Excel/text file and the DB to open is not
encrypted with password).

the demo was made in A2021.

Thanks arnelgp. That could come in handy with a different group of users. I am sure none of my family will ever use two databases. Thanks for sharing.

 

[AccessBlaster]

This is a fascinating read. It reminds me of the mistakes I made early on with my first databases. Those were frustrating times, trying to apply best practices to unorthodox designs. Man, that was tough.

 

[arnelgp]

If you have A2021 or 365 you can copy the form on the demo to your db.
run the form to delete records from your table.

there is a little table on the demo, table1. see it's content and run the form to delete it's record.

Edit: what happened to your Map thread, did you got it solved or have chosen another path?