Table Structure

[mugman17]

Ok, I am stumped. I am developing a system where it can track prices for natural gas locations. Each employee is assigned a location to enter in prices (up to 5 locations per employee), once a location is assigned, no one else can be assigned that location.

Here are my tables right now.

tblLocation
LocationID
LocationName

tblPrice
PriceID
LocationID
EmployeeID
Price

tblEmployee
EmployeeID
Employee
LocationID


Also I need to create a form that opens just the locations that the particular employee can enter prices for, and somehow get the price for the previous day for that particular location.

Make any sense?

 

[Fizzio]

An employee can have more than one location but with your current structure, each employee can have only one location. I would use a link table ie

tblLocation
LocationID (pk)
LocationName

tblEmployee
EmployeeID (pk)
Employee

tblEmployeesLocations
EmployeeID (fk)
LocationID (pk)(fk)

tblPrice
PriceID (pk)
LocationID (fk)
EmployeeID (fk)
Price
DateofPriceChange

with this structure you can have a nested subform structure.

Main =Employees
Sub1 = Locations
Sub2 = Prices

does this help?

 

[mugman17]

I'll give it a shot, stupid question though, what is FK?

 

[stardusty]

fk

foreign key

One or more table fields that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related ¾ the data in the foreign key and primary key fields must match. For example, the Products table in the Northwind sample database contains the foreign key SupplierID, which refers to the SupplierID primary key of the Suppliers table. Using this relationship, the Products table displays a supplier name from the Suppliers table for each product.

see also:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=45002&highlight=foreign+key

Brent