Multiple entries tied to one day/location combination

[cparaske]

Hi,

I am trying to develop a DB that tracks the number of packages we receive by type every day. The current database we have has the following fields on a single table:
Date
Location
TypeA_Qty
TypeB_Qty
TypeC_Qty
TypeD_Qty

In the current structure, the person enters the date and location once and then types in amounts based on the Type and whether we received anything of that type on a given day. The data entry happens on a form that contains the same fields as the table.

I would like to normalize the data and create a Locations Table as well as a Types table. This should allow me to quantify totals received (SUM(Qty)) easily versus what I do today (SUM(TypeA_Qty+TypeB_Qty, etc) as well as having control over types and locations without having to add new fields to the database table.

What I am struggling with is creating a form that allows the data entry person to enter the Date and Location once. From there, the person would select a Type and enter a quantity for up to four different Types, if necessary. I've seen sub-forms that provide this functionality but I don't know how to set one up nor do I know how to set up the underlying table (if one is needed) to capture the information.

Any help will be greatly appreciated.

Thanks,
cparaske

 

[boblarson]

Okay, so here's a quick structure which might help. You didn't give really detailed specifics so this is basically just off the top of my head.

tlkpTypes
TypeID - Autonumber (PK)
TypeDescription

tlkpLocations
LocationID - Autonumber (PK)
LocationDescription

tblLocationsTypesHeader
LocationTypeHeaderID - Autonumber (PK)
LocationID - Long Integer (FK)
DateOfService (don't use Access reserved words for object or field names and DATE is one)

tblLocationsTypesDetails
LocationTypesHeaderID - Long Integer (FK)
TypeID - Long Integer (FK)
Qty - Long Integer (I assume whole numbers but if not change the data type)

Okay, so now what you would have is a form which has tblLocationTypesHeader as its record source. That would be the main form. Then a subform based solely on tblLocationTypesDetails table as its record source and the master/child links set to LocationTypesHeaderID.

The lookup tables (tlkpLocations and tlkpTypes) would have their own individual forms to be able to add/edit the available lookup options.

Does that help?

 

[cparaske]

boblarson, that was EXACTLY what I wanted to accomplish. I created the tables and when I ran the form wizard on the header table, the form automatically added the subform to capture the data exactly as required.

I tested it and it works beautifully. I figured it was simple but I am no expert.

I can't thank you enough!