And if you use a Walmart CC at Walmart or Sams, their printed receipts as well as the monthly statements list the details of each item you purchased. Their website provides downloading that detail to Excel files which can then be imported into Access.
But you are expending ever so much energy on investigating medical data. Why not spend that time on learning Access instead?
You probably never asked a builder about site preparation or a proper foundation either. Both are required if you expect to build a second story. Or you can simply say, they don't interest me so I don't care about them. Pretty short sighted if you ask me. You also didn't learn the lesson I tried to teach you about multiple BE's. Once you accept that splitting is the proper "site preparation" for a database that will be used by others, you will see - but only if your eyes are open - that the 2G limit for ACE databases suddenly disappears. Now your database can be as large as it needs to be although an individual table is still limited to 2G. However, there are ways around that one also. Not good ways, but ways. I did a couple of contracts for Reader's Digest in the 90's (in fact, that was where I first used Access). Their master file was huge. Their solution was to take the 60 million row database and split it into 10 parts. They split the transaction file the same way and ran 10 "jobs" each day to process the transactions rather than 1.
You're welcome. I won't wish you good luck. You'll need far more than luck to complete this project. Without any site preparation or a proper foundation, you'll need a miracle. But at least we won't be stressing you any more
How can you merge all those forms if the data is not normalized? He has several fields for items in same categories, and tables with repeated fields in other similar tables.
I did not say there was not normalization problem I said it was not as much of a problem.
A lot of the forms were built on queries that selected hard coded criteria. So, even though there was one table to hold all grocery stores (not quite normalized), there were separate forms for each named store so Walmart had its own form for reasons that were never actually explained.
How can you merge all those forms if the data is not normalized? He has several fields for items in same categories
I should've said "How can you merge all those forms without first normalizing the data?"
The data is not as unnormalized as it could be but the form proliferation makes it look worse than it is.
I'm pretty sure he has repeated fields scattered across several tables, and specific fields, like WalmartItemName, MedicationName1, Name2, etc.
Well, I know he still receives emails of our posts. He'll be back...
No. Zero. DakotaRidge was proud of the thousands of objects he created in his application. I think he thinks that makes him smart or something and instead of learning about Access and database design, he was studying medicine.
From what I have seen, there is 1 and only one! You are looking at it.
@DakotaRidge's rationale is to provide his users with hardwired forms, customized for a single specific purpose, e.g. a form for food bought at Wamart, another form for recalled food, another form for recalled medications, and so on. He doesn't want a generic form for all cases, all vendors, all item categories because he thinks his end users are going to get confused selecting values from cascading combo boxes. Those require vba code and he doesn't do code, so he relies on limited macros and hardwired forms as a workaround because that's all he knows how to work with. Same reason he uses mvf's, because users can select multiple values from one dropdown list. He prioritizes the UI and UX not knowing he can merge all those hardwired forms and use parameterized queries that leverage normalized tables. He wants to provide for all possible use case scenarios, so that combined with his hardwired forms yields thousands of tables, hardcoded queries, and so forth. go figure... I have seen other people's apps designed like this, including his proposals mgmt app that surpassed 2GB quite a while ago. I cannot visualize how long it would take for me to bloat a db to 2GB. I guess it can happen quickly if you embed images in 4,000+ forms, but just building that many forms would take me an eternity! How does one efficiently navigate to a specific form with so many of them? Stacked submenus? Treeview menus? There are very few valid reasons for building denormalized apps. A Decision Support System performs better with denormalized aggregate tables, and some normalized apps that have too many joined tables that requires hairy stacked queries, so you partially denormalize some data to improve performance and simplify queries.
When he gets to the point where he has an entire data warehouse built that warrants ETL jobs loading denormalized data for reporting purposes, he is welcome to let us know. That's about where it's appropriate.
