Apologies for the long post. I'll be as succinct as possible. I am a rank beginner.

Designing a database to track shipments from a Library to a company that
digitizes print material. We will be uploading the digital files into a Content
Management system after we receive the files from the scanning company. Steps in the process include;

1. Receiving a selection list from the Librarian
2. Recording the items to be shipped (each item is barcoded)
3. Creating a shipping manifest w/a unique ID number for each box shipped
4. Recording the Shipment information in the company's special spreadsheet
5. Tracking the shipment, including the Date shipped and Date received at the scanning company, the Tracking number and shipping receipt number
6. Recording the date received back from the scanning company
7. Recording Return Status; Re-assembled, Checked-in, Re-shelved, Uploaded

That's the first step in the whole process and does not include recording all the digital files and the projects and Collections they will eventually reside in.

Here's my current line of thinking:
Tables:
Selection (for items from selection list)
Manifest (to create the shipping manifest?)
Box Number (to record the box number of boxes; i.e. 1 of 4)
Shipping (all items shipped and returned)
(am not working on the rest of the DB now, that will come later)

First I have some questions about Relationships, so please bear with me :)
Each Item will be on only one Manifest
Each Manifest will have many Items

Each Manifest can be in only one Box
Each Box can have only one Manifest

Each Shipment can have many Manifests
Each Shipment can have many Boxes

Each Manifest can be recorded in a Special spreadsheet
Each Special Spreadsheet can have many Manifests

I see all these as one to many, except for the Manifest-Box relationship. Is that correct?

Questions:
1. I want to generate a random number for each box along with the Ship date to identify each box shipped, but don't know exactly how to do that. Should include data from the Selection, Manifest and Box Number tables so I know what Items were in what box and when they were shipped.

2. I need to figure out how to record what Items were in each specific box, i.e. box 1 of 4, etc.

3. I will be entering all the data into a Form linked to the Selection table. All I need for the printed Manifest is the Item ID and the Call Number from the Selection table. How would I get the data from the Selection table into the Manifest table so I can print a report? I was thinking about using an Append query to update the Manifest table, but not sure if that's the way to go.

4. Will Exporting the data into their Special spreadsheet work? I'll need to use it multiple times.

I appreciate any assistance that comes my way.

Thanks,
John

Welcome to the forum!

Your basic reasoning sounds to be correct. I would recommend laying out the table structure first (tables and relationships). Once you are satisfied with that then I would worry about forms and how the user interacts with the data.

From what you posted, I see the following structure

I use the following field name prefixes:
pk=primary key, (I have an autonumber primary key in each table)
fk=foreign key, long number integer datatype
txt=text field
dte=date/time field
long=long number integer field; not a key field
sp=single precision number field
dp=double precision number field
log=logical/boolean field (yes/no)



tblShipments
-pkShipID primary key, autonumber
-fkCompanyID foreign key to tblCompany (company to which the package is shipped)

tblCompany
-pkCompanyID primary key, autonumber
-txtCoName
-txtCoAddress
etc.

Since there are many tracking events associated with a shipment: when it is shipped, when it is received etc. that describes a one-to-many relationship

tblShipTrack
-pkShipTrackID primary key, autonumber
-fkShipID foreign key to tblShipments
-dteTrack (date of the tracking event)
-fkEventID foreign key to tblEvents
-txtEventNo (tracking or receipt number depending on fkEventID)

tblEvents (just holds records such as shipped, received etc.)
-pkEventID primary key, autonumber
-txtEvent

Since a shipment contains many boxes and each box only contains 1 manifest, then you can say that a shipment has many manifests

tblManifests
-pkManifestID primary key, autonumber
-fkShipID foreign key to tblShipments
-longBoxNo
-fkSpecialSpreadsheetID foreign key to tblSpecialSpreadsheet


tblSpecialSpreadsheet
-pkSpecialSpreadsheetID primary key, autonumber
-txtFileName (?)


Each manifest/box has many items.

tblManifestItems
-pkManifestItemID primary key, autonumber
-fkManifestID foreign key to tblManifest
-txtBarCode
-txtItemDesc
etc.


This assumes that each item is unique and that it is not shipped at any other time. If the items are not unique, then I would recommend a separate items table and then use a foreign key in the tblManifestItems to reference the item.

I was not clear on these two points:


6. Recording the date received back from the scanning company
7. Recording Return Status; Re-assembled, Checked-in, Re-shelved, Uploaded


What exactly are you receiving back? All of the items within a manifest or just individual items? In other words, are you tracking what happens to each specific item?

Thanks for your reply. That got the old brain thinking :)


There is no need to track the company shipping info. For each shipment I do need to record at least the following info:

Item ID (Barcode number)
Publication Date
Call Number
Language
I like the idea of a separate table for Tracking events. I also need to print a Manifest report for each box that ships that includes the afore-mentioned fields.

As for your question, we are receiving back the same items we shipped. I am working at a Library that is in the process of Digitizing thier collection. We are shipping the material to a scanning company. They do the scanning and ship the originals back to the Library. I need to know what we shipped is what we get back.

I also have to figure out how to track the number of boxes per shipment.

Thanks for you help,
John

I also have to figure out how to track the number of boxes per shipment.


Tracking the number of boxes is easy with the structure I proposed--a simple counting query will do it

There is no need to track the company shipping info. For each shipment I do need to record at least the following info:
Item ID (Barcode number)
Publication Date
Call Number
Language


This looks like it needs to be part of the item information not the shipment information. Thus it should be in tblManifestItems


As for your question, we are receiving back the same items we shipped

I understand that but how do you receive them back? Are the items returned in the same manifest/box in which you shipped them out or do you treat each item separately after it is returned. This impacts how the remaining tables need to be structured.

We are using large plastic shipping containers. The items we ship will be returned in the same container ready for checking back into the Library catalog.

What is your process after you receive them back and what data do you want to capture at that point?

Basically we need to record the return date, if it needs re-assembling, check it into the catalog. We'll also be receiving several digital files for each item and need to record that data.

I've been struggling with how to set the ship date for a set of items. I don't think it's a good idea of having a ship date field next to each item. I was thinking of a table for items being processed and then appending that data to the manifest table for setting the ship date for the group of items, printing the report, and exporting to excel.

Does that sound like it's a good way to go?

I've been struggling with how to set the ship date for a set of items. I don't think it's a good idea of having a ship date field next to each item.

In the structure I proposed, all items that are shipped together are linked to the shipment tracking information which includes the ship date (see tblShipTrack: dteTrack). All events and associated dates related to the shipment are tracked in tblShipTrack.

Now once you receive things back, if there are various files etc. related to specific items, then those should be captured in other related tables. Hence, my question as to what you do with items once they are returned and whether you still track them via the original manifest or whether you track each item individually.

The materials should be shipped back in the same containers, so I would be checking them in with the same manifest that was shipped with them originally.

The materials should be shipped back in the same containers, so I would be checking them in with the same manifest that was shipped with them originally.

What you indicate would be handled with the structure I previously presented. What happens next?

I'm trying to understand your proposed structure as far as Tracking and Events go. The tblShipTrack has a field txtEventNo for the Tracking or Receipt number. Wouldn't there need to be 2 fields for both numbers?

Also, your structure does not have any where to record the actual items shipped. There needs to be at least a table that has the following fields:
BibID, Pub date, Call number, Copy, and Language.

Thanks for all your help.

According to your proposal, are the tblShipTrack and the tblEvents related? One Ship Track has many Events?

The tblShipTrack has a field txtEventNo for the Tracking or Receipt number. Wouldn't there need to be 2 fields for both numbers?


No, you would not need 2 fields. The txtEventNo is just a number, which type depends on the event itself (fkEventID). If the event is to receive the items back, then the txtEventNo is the receipt number. If the event is to ship, then the txtEventNo is the tracking number.

Also, your structure does not have any where to record the actual items shipped. There needs to be at least a table that has the following fields:
BibID, Pub date, Call number, Copy, and Language.


It does, I just did not spell out the details:


tblManifestItems
-pkManifestItemID primary key, autonumber
-fkManifestID foreign key to tblManifest
-txtBarCode
-txtItemDesc
etc.


This assumes that each item is unique and that it is not shipped at any other time. If the items are not unique, then I would recommend a separate items table and then use a foreign key in the tblManifestItems to reference the item.


Now as mentioned above, it might be better to have a table that holds all of the details of each item and then use a foreign key in the tblManifestItems. In fact, I think that is the better approach.

tblItems
-pkItemID primary key, autonumber
-BibID
-Pubdate
-txtbarcode
-Callnumber
-Copy
-fkLanguageID foreign key to tblLanguages

tblLanguages (a table to hold all possible languages)
-pkLanguageID primary key, autonumber
-txtLanguage

With this new structure tblManifestItems now becomes:

tblManifestItems
-pkManifestItemID primary key, autonumber
-fkManifestID foreign key to tblManifest
-fkItemID foreign key to tblItems


You may be able to further normalize tblItems. I am guessing that BibID, callnumber and the barcode fields are all numbers. This suggests that an item can have many numbers associated with it which is another one-to-many relationship. You might also have an ISBN number as well. So you have many numbers of different types (BibID, barcode, callnumber, ISBN etc.)

tblItemNumbers
-pkItemNumberID primary key, autonumber
-fkItemID foreign key to tblItems
-fkItemNumberTypeID foreign key to a table that holds the various types of numbers)
-txtItemNumber (a generic field to hold the number of the type indicated)

According to your proposal, are the tblShipTrack and the tblEvents related? One Ship Track has many Events?


Yes.

tblEvents is just a generic listing of all possible events (ship, receipt etc.)

The table tblShipTrack brings the shipment (tblShipments) and the particular events (tblEvents) related to that shipment together.

Thanks for that claification. There will be both a Tracking Number and a Shipping Receipt number for every shipment, so I think another field is needed.

The BibID/Barcode number are the same and are unique. The call number is also unique. These publications do not have any Standard numbers assoicated with them; ISBN, ISSN, etc.

Yes.

tblEvents is just a generic listing of all possible events (ship, receipt etc.)

The table tblShipTrack brings the shipment (tblShipments) and the particular events (tblEvents) related to that shipment together.

So is the tblEvents used as a lookup table for the tblShipTrack?

Apologies, just trying to get a handle on all this.

There will be both a Tracking Number and a Shipping Receipt number for every shipment, so I think another field is needed.


No, not 2 fields, but rather 2 records in tblShipTrack: 1 record to ship with its shipping/tracking number and a second record for receipt with its receipt number. The number goes in the same field txtEventNo (but in 2 different records)

The BibID/Barcode number are the same and are unique. The call number is also unique. These publications do not have any Standard numbers assoicated with them; ISBN, ISSN, etc.

Since you still have multiple numbers, the normalized approach is to have separate, but related records, each record would hold a particular number whether or not it is unique (I was incorrect in my earlier explanation on the number uniqueness, my apologies)

Sorry, I posted before I saw your next post.

So is the tblEvents used as a lookup table for the tblShipTrack?


Yes, you would use tblEvents as a lookup when you are entering data for tblShipTrack via a form. As a caution, although Access has the capability of having lookups (combo/list boxes) at the table level, doing so can cause problems as detailed here (http://access.mvps.org/access/lookupfields.htm). So it is best to use lookups on forms (not in the tables themselves). You would bind the lookup to the control on the form that is tied to the applicable field (in this case fkEventID). Forms come later, the table structure is the most important thing at this point.

So all Lookups should be in a separate table and not ones you type in?

Thanks for setting me straight.

I have always used separate tables for lookup values. They give you the most flexibility as your requirements change over time. You just add new records as needed; no need to go into the table design and you avoid the lookup issued already mentioned.

You're welcome

Hope you don't mind if I keep bugging you with questions.

I am using DMax to increment the Manifest Number field on the Manifest form by 1 for each new shipment. I have the Manifest table in a One to Many relation with the Items table; many items in one manifest.

How would you suggest I do this process? I'm entering the items into the Item table and placing them in the box until it's full. That gives me the Manifest that I then need to generate the Manifest number for and print a report with fields from both the Items and the Manifest tables.

I was thinking of a subform that would open when the user is ready to print the Manifest, but aren't those used to show the Many side of a One to Many relation?

Thanks again.

Forms are used for data entry and/or editting, so you would use a subform based on table tblManifestItems within the manifest main form. That way you can add all the items to the same manifest. You will need the DMAX()+1 in the before insert event of the manifest main form if I remember correctly. You may want to have a navigation form prior to your manifest form with two buttons: 1 to enter a new manifest and another to edit an existing manifest. You would use the same manifest form, just open it differently when the button is clicked (data entry property would vary).

If you want to print a listing of items tied to a manifest you will need to create an Access report. You can add a button to your manifest form to print the report for the specific manifest record currently viewable in the form.

I'm kind of sketchy on how the Shipping tables need to be related.

One Tracking to many Shipments
One Tracking to many Events
One shipment to many Events
Is that correct? I have the Shipment table in a One to Many with the Tacking table. I have the Events table in a one to many with the Tracking table.

Can the Shipment table 'talk' through the Tracking table to the Events table?

Sorry for the weird description.

Thanks, John

A shipment can have many tracking events.

An event can be associated with many tracking events.

A shipment can have many boxes/manifests.

A box/manifest can have many items.

Can the Shipment table 'talk' through the Tracking table to the Events table?

Yes, since it contains keys from both the shipment table and the events table

tblShipTrack
-pkShipTrackID primary key, autonumber
-fkShipID foreign key to tblShipments
-dteTrack (date of the tracking event)
-fkEventID foreign key to tblEvents
-txtEventNo (tracking or receipt number depending on fkEventID)

So it a One to Many between the Shipment and Tracking tables and a one to many between the Tracking and Events tables?

Thanks for clearing this up. I might even graduate into the Beginner Access class ;)

So it a One to Many between the Shipment and Tracking tables and a one to many between the Tracking and Events tables?

Correct, the tracking table is a junction table in database jargon and is a way to capture a many-to-many relationship. If you look at just shipments and events (ignore the tracking table for a moment)--a shipment has many events (ship, receive etc.) therefore, a one-to-many relationship, and the same event can occur for many shipments another one-to-many but going the other direction. When you have 2 one-to-many relationships between the same two entities (shipment and events), you need a junction table to capture that many-to-many relationship which is why the tracking table is needed.

Of course! Now the light goes on :)

Thanks so much for your help.

You're welcome.

Uncovering the relationships in the data and transcribing that into a table structure is the key to a successful database application, and it is usually the hardest part to grasp for someone just starting out with a relational database.

I'm glad you are taking the time to understand the relationships!

Is it OK to use the Foreign Keys from the Shipment and Events tables as the Primary Key in the Tacking table?

I realize how important setting up a solid structure is and want to get this right from the start.

TIA

Is it OK to use the Foreign Keys from the Shipment and Events tables as the Primary Key in the Tacking table?

What you are describing would be a composite primary key consisting of both of the foreign keys. This composite key would replace the autonumber primary key I showed. Using composite keys can be done. The drawback is that if the table where the composite key is used is also the one side of another one-to-many relationship (as it is in your case), the table on the many side would have to have 3 fields to make the composite key in order to keep the key unique. This can get kind of messy as you cascade through the table structure.

I personally do not use composite primary keys for that very reason, but I know other DB designers do use composite primary keys. I was doing some searching and saw arguments for and against composite keys, so you will have to make the call on that one.

jzwp22, I want to thank you for all the help you've sent my way. I really appreciate it.

The requirements have changed and I need a bit more help steering me in the correct direction.

Should I continue with this post or start a new one?

You're welcome.

If you want to get some fresh perspectives from others on the forum for the new requirements feel free to start a new post. You can always reference this post by putting in a link to give the contributors the background if they need it.