But a lot of this starts with analysing the data correctly, and constructing a well normalised data schema. It's not that an inexperienced Access user has a way to do something. It's more that he is sure that the shortcut he is using produces the correct result. The underlying issue is that "apparent" solutions can conceal problems within the database. For instance if your system contains 1000 sales orders, you want to be sure that a report of a section of those orders showing 250 orders WOULD show 1000 orders if you selected every record - and that you haven't lost some along the way because the data structure left some orphan records. The more data you have the more vital it is to make sure there are no data leaks. You need all the order dates to be sensible for instance. No nulls, no bad/out of range date values, as well as all records linked to companies and no orphans. I find a lot of my Access development is defensive. Even as the developer I use the forms, and not just use tables directly, because I want the protection of all the validity and sense checks I built into those forms.
Dave, you are a lucky guy to have data when you start work on a database. I have always had to conceive of an application and compile the data. When I got started on my finance and health database in January, I only had a vague idea for what I wanted. I mentioned that to a user group, and George suggested that I subscribe to Quicken. Knowing that I don't like COTS, I poo-pooed that idea. But I went to the library and checked out "Quicken for Dummies." The book showed me that Quicken isn't for me. There are no images and no speech in it. I must have both.
So I purchased an accounting textbook and later a copy of one of Dave Ramsey's books. Then I discovered the Kiplinger Personal Finance magazine at the library, and it inspired me to create several tables/forms/reports.
A couple of months later, a relative told me that he was suffering from a major disease. That sent me off into the personal health world. I justified the shift because dealing with any health problem costs money. There are doctors and dentists to pay, and meds to purchase. So. every module in the health domains has a field for cost. Every disease or illness is assigned to a family member. So. each module is a one-to-many relation in several ways. I use lookup tables multiple times, so I consider the design to be many-to-many-to-many. But I wouldn't call the design DKNF.
A couple of months ago, I had my annual medical exam. My report had 46 tests that I built into the database. Because I need graphics to work with data, I created pseudo-pie charts for the four main diseases: diabetes, liver disease, heart disease, and kidney disease. The charts use checkboxes to tell a person if their test scores are in or out of range for their age, race, and gender. I haven't gotten around to cancer yet, just too much information about cancer to get my head around.
A few weeks ago, I learned about the food recalls announced by the CDC and USDA. There is a recall every few days. I have encoded these data into the database. I found a map in a Kiplinger article, and I digitized it in PowerPoint. I used the map for several forms, and then I had the idea to move labels and checkboxes in the form. I learned a little about animation in Access five years ago at DevCon. When I ran into a problem, I asked developers on AW about animation.
Recall data keeps coming in from the CDC, USDA, and MSN. The challenge is that these organizations publish different data, they are not consistent. That means that no matter what the tables are designed to do, there are always missing data in a recall announcement. Sometimes an announcement lacks the states impacted by a recall, other times there may not be a name for the bacterium. I only care about recalled foods in the five states where my family members live.
It gets even more challenging because the database needs to store detailed information about diseases, medicines, and nutrients. I am not a physician nor a nutritionist so I am always learning something new. When I get detailed data into the database, I then need to add graphics and speech.
I would love to have had data for the application when I started creating it. In a way, it is good that I didn't. I love doing research and compiling disparate data. Don't show me a page of data for anything, I can't handle it. Show me graphics, no charts, graphics.
