I'm hoping someone can get me back on track with this db design.

This is a database to record and track documents and CDs at my company. I have separate tables for documents (objects) and CDs. Both objects and CDs are revised from time to time and this is recorded using related revision tables for both of these. The contents of a specific revision CD are held in tblCDContents which joins together the CD Revision table and the Object revision table. So far so good. It's highly normalised which I'm told is a good thing but it does make the tables themselves difficult to read.

Now to the problem. In addition to basic tracking, we want to define what documentation should be shipped at a specific "event". In operational terms, this could be when we ship new product, ship a spare board, supply an upgrade...... What is posted depends on the circumstance and can be none or more CDs plus none or more objects. (Obviously we would always send something but it could be any mixture > 1). It's OK to link the "what we ship" to the base documents and CDs for two reasons: 1) staff can only access the latest anyway - 2) I don't want to be forced to update this section every time a revised CD or document appears.

I have tried a few different ideas, including the one you'll find in the attachment. That has a table where the "event" is defined, linked to a table with columns for the event, the CD, the object. That just doesn't feel right - I seem to end up with partially filled records in this last table (tblShipContents) and I can't query correctly.

Sorry this is such a long winded post but I'm hoping someone has enough patience to help out!

Many thanks

Malcolm R

I'll help with general advice. Your first goal is the decision to ship. Can you quantify this? (Not here, but can you define criteria for shipping?) Once you have decided it is time to ship, how do you decide WHAT to ship?

The reason you need to answer these questions is an Old Programmer's Rule: If you cannot do it on paper, you cannot do it in Access. (I.e. until you understand the data flow "on paper" you aren't ready to start programming anything.)

Once you have decided how to do it on paper, your next question is to make your co-workers think you are crazy. Talk to yourself as you trace the steps on paper. Every time you say "Look at the XYZ so I can decide when to ZYX..." - MAKE A NOTE. You will need to store that XYZ data in the database in order to support the ZYX proces. That is because of the second Old Programmer's Rule: Access won't tell you anything you didn't tell it first. (Meaning, if you don't provide the data necessary to support a decision, action, or report, forget about the decision, action, or report.)

At some point you will be ready to define what you are trying to do in Access terms. So it is then time to define the elements of the business model you are about to build. You need to decide your business entities. Obvious things like CD's and documents etc. Where you ship them is another potential entity. The actually shipping content list for a given shipment is probably an entity. You will know what requires separate definition only if you really understand normalization rules. If you don't feel comfortable with that topic now, DON'T START the entity identification. If you feel that you really do know about normalization, go ahead.

The whole key to this is to be methodical and to understand that this kind of thing takes time up front to get it right. Otherwise, you'll be retro-fitting the world every time even a little change is required. And you'll hate every bit of it because of the "cartoon snowball down the hill" effect. NOTHING will get easier later if you were careless early in the project start-up.

You attached some documents but I am at a U.S. Department of Defense site that doesn't allow downloads from foreign sites. And of course, this forum is a UK site. I can't look at your presentation, so all I really can do is offer general advice.

Thanks for the advice Doc Man - much appreciated. I'll work on capturing the data as you suggest and come back to Access after Christmas.

This is a database to record and track documents and CDs at my company. I have separate tables for documents (objects) and CDs. Both objects and CDs are revised from time to time and this is recorded using related revision tables for both of these. The contents of a specific revision CD are held in tblCDContents which joins together the CD Revision table and the Object revision table. So far so good. It's highly normalised which I'm told is a good thing but it does make the tables themselves difficult to read.

Now to the problem. In addition to basic tracking, we want to define what documentation should be shipped at a specific "event". In operational terms, this could be when we ship new product, ship a spare board, supply an upgrade...... What is posted depends on the circumstance and can be none or more CDs plus none or more objects. (Obviously we would always send something but it could be any mixture > 1). It's OK to link the "what we ship" to the base documents and CDs for two reasons: 1) staff can only access the latest anyway - 2) I don't want to be forced to update this section every time a revised CD or document appears.

I have tried a few different ideas, including the one you'll find in the attachment. That has a table where the "event" is defined, linked to a table with columns for the event, the CD, the object. That just doesn't feel right - I seem to end up with partially filled records in this last table (tblShipContents) and I can't query correctly.



just a couple of thoughts. It shouldnt matter that the data in the table is hard to read - since you shouldnt be trying to read data in the tables. If your normalised tables are using numeric foreign keys as cross-references, then this is as it should be - rather than use tables directly, construct a query to unite the disparate tables, so you can see what data you have.

however, when you say - you cant query the data correctly, this maybe indicates there still is a normalisation problem.

As DocMan says, you need to analyse your data and system very carefully - but in simple terms, you may just need a table for events, and a table linking the event to the relevant documents, and a table linking the event to the relevant CDs


ie
Documents table
CDs table
Events table
Events-Documents 1 event = many documents
Events-CDs 1 event = many CDs

then just select an event, and the system knows which documents/CDs to pick

Thanks Dave - I think you may have hit the nail on the head there! From your list above, I have the top 3 tables, but I have tables 4 & 5 combined. I'll redesign as you suggest - it makes much more sense.
Malcolm

Malcolm,

One of the myriad reasons that you can't update a query (or a form that goes through that query) is a simple rule. Access cannot update two records at once from a single action. If your form includes anything ambiguous about what record to update, you will find that Access doesn't want to do the update.

Now, in a form based directly on a table (not always considered good), there should never be any such ambiguity. But if there is a JOIN query involved, you can only do that update if enough information exists to uniquely identify the contributing records, ONE EACH, from the tables that underly the JOIN query.

Stated another way, you CAN update through a JOIN query but the identity of the contributing table records MUST be unequivocal. Otherwise, no joy.

I won't say that's the first place to look. But it surely is early in the list.

Another "can't update" "gotcha" is if you have a JOIN query that somehow omits data for a field that is NOT optional in one of the underlying tables and no default exists.

Thanks again guys - it's so good to visit a forum and get such genuine help and advice.