Database with multiple entries for a single date

[cparaske]

Hi,

We have two facilities that receive four different types of shipments. I wanted to set up a database to track these shipments. Is there a way for me to set up tables such that the person entering the information can leverage the same date and location field for each entry type? For example, we may only receive two types of shipments today and four tomorrow. I want to avoid having the clerk enter the date and location two times today and four times tomorrow for each data entry.

Here's an example of what I mean above:
Date Location Type Qty
5/16 1 A 10
5/16 1 B 1
5/17 1 A 1,000
5/17 1 B 100
5/17 1 C 1
5/17 1 D 11

I'd like for the clerk to select from a fixed number of locations (1 and 2) and a fixed number of types (A, B, C, or D) and provide the date and quantity received.

Any help will be greatly appreciated!

Thanks,
cparaske

 

[EternalMyrtle]

Assuming the date you need is the current date, the date (and time) can easily be automatically generated using the Now() function. No input needed from the user.

To do this, just create a date field in your table and set the default value to Now(). If you do this, you don't even need to insert the field into a form. Or you can place the field on your form and lock it so the user can see the date.

You can select the location and shipment type using a lookup table or (if there are only two options) just entering it into the row source (lookup tab).

The clerks should be entering data into forms not tables.

I hope this helps.

 

[cparaske]

Thanks, EternalMyrtle. I am planning on having a form with a sub-form for data entry but wanted to make sure that the database is designed to support that functionality. I will develop the tables as you suggest and try to figure out the entry form from there. I'll probably be posting on the Forms side of the site next

 

[EternalMyrtle]

Just remember that if there is any chance that users will need to enter a past or future date, the Now() function will not be the best choice (one example I can think of is if the clerk forgets to enter the shipment one day and wants to enter it the following day or if a shipment comes in after working hours). In this case, you would have to use the date picker and the user would have to enter the date.

I am still not 100% clear on your goals but it sounds like you might want to set up a one to many relationship between dates and shipments (which is not the solution I provided above). If that is the case, you need two tables one for dates and one for shipments (and probably a third for locations).

tblShipmentDates
ID (PK autonumber)
ShipmentDate
ShipmentID (FK from tblShipments)
LocationID (FK from tblLOcations)
Quantity


tblShipments
ID (PK autonumber)
Shipment Type (A, B, C or whatever)



You could set it up so the date is the main form and the shipment types are entered into a subform.

Depending on your data and your goals, you could create the unique index on tblDatesPK and ShipmentFK (so that you cannot have two of the same shipment types on the same day) or include the LocationFK field (if two of the same shipment types could be delivered to a different location on the same day).

How you solve it really depends on your goals and why you are tracking this data. Does this make sense?

 

[EternalMyrtle]

Honestly, since you only have 4 shipment types and two locations, you could probably set this up in one table using the row source lookup for Shipments and Locations.

That would probably be easiest but perhaps not the absolute best design in the long-run. Since it is pretty simple, it wouldn't be too hard to change later, however.