I'm back.
Here's where we get theoretical and practical at the same time.
Purists would look at what I told you and agree with most of it. The part where I said you could put doctor ID and patient ID in the Vaccination table as well as the Visit ID - that would cause hesitation. I also said the Visit ID made it unnecessary to keep the doctor and patient ID in the vaccination table.
A purist would say they HAD to be separate. A pragmatist would say that as long as you NEVER EVER change the doctor and patient ID in the vaccination record, it is OK to have them stored redundantly. It would be OK to copy the date of the visit to the vaccination entry. They MUST be immutable once stored. (If they aren't, you're just piddling around with a mish-mosh of a thing that looks like a practical database.)
Why would you wish to store (or not store) redundant data? Well, here is where the practical and theoretical sides of the house sometimes go their separate ways. To find out who had vaccinations, you need to build what is called a JOIN query linking all visits with all vaccinations. The vaccination table joins with the vaccine table and the visit table joins with the patient table. The result would be a query in which you had a list of patient names (from the patient table), dates (from the visit table), and vaccines (from the join of the vaccine to vaccination to visit tables.) OR you could just include the patient ID in the vaccination table and a copy of the visit date, so that you have only three tables involved, not four. Join the patient ID to the vaccination to the vaccine and get a list of patients.
The above paragraph discusses how you would use these tables. The first thing to notice is that to find who did what to whom when and with what is that you query the VISIT table as the center of your database. You then join the visit to other things through other junction tables until you can build a query that, when opened, looks awfully much like that spreadsheet I started talking about earlier. That is a characteristic of normalized databases. You have base tables for the elementary entities, but it is the JUNCTION tables that show you the meat of your real business. In your case, visits and the actions that can occur during the visits. The vaccines that are used, the consumables expended, the drugs prescribed - these things have their own basic entity tables and the junction tables that, in essence, list the relevant items for each visit.
How do you know if, say, NO injections or vaccinations occurred for a given visit? This is where you learn about domain aggregates. You can use DCount to find how many vaccinations occured for patient John Smith in July of 2006 because you know John Smith's ID code. Depending on the pragmatic or purist view, you can search for his code in the vaccination table or in the JOIN of the vaccination and visit tables. The difference is one query.
The purists will tell you that because the query can be written that would provide you links to everything you needed, you should not include the redundant ID codes copied from the visit table. And they'd be right.
The pragmatists would tell you that this query slows things down a bit because it takes time to organize the join. You can save time at the cost of a little extra space by copying the patient ID and other relevant data to the vaccination record - or other relevant records, as needed. And they'd be right.
Both will tell you that once you make the copy, you cannot change it. And they'd both be right.
I cannot tell you which way to go because each of us has his/her own style. But I can tell you that you should decide on a style and stick to it. If you don't, you really ARE doomed.
Now some further practical advice. Earlier I mentioned that you must have your PK based on an index. But you can have up to 10 indices on a table. There are reasons to choose or not choose a particular field as a place to put an index. Sometimes all you need is a sort within a query, not an actual index on which to base a report order. Sometimes the order of a report's detail records is not the same as the PK of the base table. So here's the rule I usually apply.
There is always an index on the PK and there will be a PK when the table in question is on the "one" side of a one/many a relationship. There will be secondary indices when a very common action involves a search of the field that has this secondary index. However, ...
The smaller the table, the less likely you are to need a secondary index. The less often you touch the table, the less likely you are to need a secondary index. The bigger the field that would be indexed, the less often you should make it an index in the first place. In the ideal case, a secondary index is used on short, frequently-searched fields in long tables.
The balancing act is that the more indices you have, the more expensive it is for Access to maintain them. In tables with relationships, you cannot avoid having one index. But the question is, how many more indices do you need and how much will it cost you to maintain them? See, every time you add a record, delete a record, or change the value of an indexed field, you must change EVERY INDEX on that table to reflect the new table contents.
Something you probably will have to consider is billing. A patient will incur charges for a visit, for vaccinations, for consumable supplies, etc. etc. You treat charges as another basic entity linked to a visit. A charge can be itemized by considering the elementary things that contribute to it. And where do you start? The Visits table already provides the link to every other basic element. The visit itself incurs a charge. Vaccinations, consumables, etc. all LINK to the visit so can be used to group items that relate to the visit.
This leads to the next topic, which I urge you to look up in the Help files and also to search in this forum... A UNION query can be used to define your charges at the individual line-item level, which can be rolled up based on the visit ID to provide a visit charge. I'll leave that research to you for now.
What I just gave you is a full pile of information and ideas that will take a while to digest. But I think if you can pick your way through it, you will see how this sort of thing works.
I really do relate to the idea of being so lost that you don't know whether you are coming or going. I'm having that little headache myself right now with security based on digital certificates and trust chains. Since I'm not up on some of the detailed certificate lingo, I have to pick my way through it slowly. Oh, I've studied it enough that I'm beginning to get the basic ideas, but the technical understanding is slow to come. It will come for me - as it will for you - based on study and meditation.
)
