odd customer - product - order model 2

[JuniorData]

I've been tasked with fixing re-designing an access tool for tracking purchases that are allocated among different locations. I have:

BUDGETS
office (PK)
region
budgetAmount (for the year)

PURCHASES
purchID (PK)
office  (FK)
date
po_number
description

DIRTRIBUTION
distID  (PK)
purchID (FK)
unitPrice
quantity 
allocationShare (price X quantity)
office (FK)

I'm having trouble fitting this into the customer-order-order detail model. What's odd is an office makes a purchase, but the cost can come from that offices budget only or spread out among two or more offices.
Currently there is a relationship drawn between budget.office and purchaseDetail and also one drawn between budget.office and distribution so that you can look up an office from both the purchase table and the distribution table. This seems odd to me.
How can I design the tables/relationships so that forms and reports will behave as expected? That is, a main form where you select the office, enter one or more PO in one subform and the distribution of that purchase in another (or sub form in a sub form?)

Thanks for any suggestions, I want to be sure I get off on the right foot here.

 

[johnherman]

This model seems OK. You have the concatenated FK's of purchID and office ID to handle the split across offices.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[dhookom]

If the purchases are one to many to offices, then I would get rid of the office field from the purchases table. You might leave it in if you need to track the purchasing office. If this is the case, I would change the field name to "PurchasingOffice" to avoid confusion.

Duane
Hook'D on Access
MS Access MVP

 

[JuniorData]

Thank you both, I'll carry on with this model. Duane - changing the name does make it easier to understand, thanks for the suggestion.

 

[PHV]

I wonder why the BUDGETS table have no date column ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

In addition to the "no date", it seems the allocationShare might be storing a calculated value of UnitPrice * Quantity. If this is the case, I would remove the AllocationShare field.


Duane
Hook'D on Access
MS Access MVP