Table/Query Help?!?

[yt0077]

Hi there,

I haven't really used access since my university days and am having a bit of trouble.

We are a small civil engineering company and we hire a lot of the machinery that we use on sites as well as purchase materials etc.

The problem I'm having, is I need to keep track of the deliveries we are getting and can't figure out how to set up the delivery table.

Here is a list of the tables I have so far with the fields:

CLIENTS
Client ID
Client Name
Client Address... etc.
SUPPLIERS
Supplier ID
Supplier Name
Supllier Depo
Supllier Address... etc.
CONTRACTS, (The different Jobs that we get)
Contract ID
Client ID
Job Description
Start Date
Close Date
Notes
HIRED EQUIPMENT, (This is a table of the items hired)
Hired Equpment ID
Order Number
Supplier Contract Number
Item - (Picked from Items for Hire Table)
Description/Type
Size
Size Units
Fleet Number, (Serial number)
Single Item cost
Cost Per - (Drop down lift including daily, weekly, etc)
Quantity
Fuel Type


Delivery ID
Pick Up ID
Notes
Materials Purchases
Material Purchase ID
Order Number
Supplier Contract Number
Material - (Picked from Materials table)
Description/Type
Length
Width/Diameter
Height
Size Units
Quantity
Quantity Units
Single Item cost
Delivery ID
Pick Up ID
Notes
LOCATIONS, (Jobs may have things delivered to different locations)
Location ID
Contract ID
Address... etc.
DELIVERIES
Delivery ID
Order ID - (Might have more than 1 order in a single delivery)
Delivery Receipt Number
Delivery Company
Delivered Items - (I want to be able to choose from items that match the order ID. Sometimes not all the ordered items are delivered at the same time)
Delivery Date
Delivered From - (Chosen from locations for that contract)
Delivered To - (Chosen from locations for that contract)
Fuel on Delivery
Delivery Charge
Notes
PICK UPS
Pick up Date
Pick up From - (Chosen from locations for that contract)
Delivered To - (Chosen from locations for that contract)
Fuel on Pick up
Pick up Charge
Fuel Charge
Notes
ORDERS
Order ID
Order Type - (Hire or Materials)
Contract ID
Supplier - (Look up from supplier table)
Order Date
Ordered by - (Look up from staff table)
Ordered for - (Look up from staff table)
Notes
INVOICES, (Invoices from suppliers)
Invoice ID
Supplier ID
Supplier Invoice number
Order ID
Invocie Date
Invocie Amount
Notes
STAFF
Staff ID
Staff Name
ITEMS FOR HIRE
Item ID
Item
Item Catagorey
MATERIALS
Material ID
Material
Material Catagorey

The area I'm stuggling with is highlighted in red above. Is there a way I can get check boxes for the delivered items that just apply to the relevant orders.

If you can help with this, I'd really appreciate it... also if you can think of anything else that might help, I'd appreciate it.

yt0077

[NTC]

If there can be more than 1 delivery record for a Material Purchase (which I think is what you say) then there must be the field: 'Material Purchase ID' in the
Deliveries table in order to be cross referencing (aka foreign key) and fundamentally MaterialPurchaseeliveries is a 1:Many relationship.

There would not be a DeliveryID in the Materials Purchasing table because of the same reason.

That's the fundamental. Whether or not a checklist is feasible or the best way to display, enter and complete info can really be situational to the user experience. For instance the user's starting point is a Project - where they may want to view deliveries would be totally different than the user with a starting point of accepting a delivery that maybe has materials from several projects and needs an efficient way to enter this data. So while the fundamental table relationships must be set - one can have a variety of UIs.

Hope this helps a little.