Many-to-Many Madness

[brookierook]

Yes, it's the Purchase Orders table, Cost Centre Budgets, and the clients table. I was thinking maybe I should add a cost centre column in the suppliers table.... but not sure how best to work this.

[Bob Fitz]

What does the figure in field "CostCentreBudget" represent?
What does the figure in field "ApprovedBudget" represent?
What does the figure in field "Amount1" represent?
What does the figure in field "Amount2" represent?
What does the figure in field "Amount3" represent?

[brookierook]

"CostCentreBudget" - Market Research has a budget (usually a few million Euros), this budget is divided into cost centres and for each cost centre there are clients.. i.e. GM and TC fall under OCS147 and Europe Segmentation and AMEA Segmentation fall under BRA130.

What does the figure in field "ApprovedBudget" represent? Approved budget is the budget for a project. Projects are done for clients from their perspective budgets.

What does the figure in field "Amount1" represent? Every project will have at least one purchase order. Amount 1 is the value of Purchase order 1.

What does the figure in field "Amount2" represent? Every great once in a while we will have a second purchase order for a country and this is the amount of the 2nd PO

What does the figure in field "Amount3" represent? Very rarely we have a 3rd PO for a project.. but this is the amount for the 3rd PO.

[Bob Fitz]

OK. Quote from your earlier post:
So they just like to see a live running report of sorts..... saying how much they have left for each client and how much they have spent already.

1) So what we need to calculate is the total of "ApprovedBudget", deducted from "CostCentreBudget" for each CostCentre ?
2) Do we also need to calculate the the total of "Amount1" + "Amount2" + "Amount3" and deduct that from "ApprovedBudget" ?

[brookierook]

Ok.... here is what I'm thinking....

At the moment I don't have a column that displays budgets for each Client (as i've only just thought of this). There should be a place to put 'current cost' for each client next to their budget. I don't think I have set it up that way. PO's should be subtracted from the client budget as well as the approved budget for that project. And maybe a calculated field for 'current cost' that displays the sum of all PO's (amount1, amount2, amount3) for that client. Is all of that possible or am I wanting too much from Access?

[Bob Fitz]

Take a look at the attached file.
I have made the following changes to the file that you posted. The changes may not be exactly what you need or want, but should at least give you some ideas about how you may achieve your goals.
Added PK to Purchase Order table.
Created new table PurchOrdDet.
Created 1 to many relationship between these 2 tables.
Moved PO number and PO Amount 1,2 and 3 to the new PurchOrdDet table.
Lookup fields at the table level should be avoided, so I have changed fields “CostCentreID” and “ProjectID” in table “PurchaseOrders” to a textboxes. See: http://access.mvps.org/access/lookupfields.htm

Created a new form for Purchase Orders called frmPurchaseOrders and another new form called frmPurchOrdDet which are both used as subforms on another new form called frmFLS2013ProjectForm which is used as a subform on another new form called frmClients.
frmPurchaseOrders is linked to frmFLS2013ProjectForm and frmPurchOrdDet is linked to frmPurchaseOrders via a textbox on frmFLS2013ProjectForm.

I’ve just read through this before posting and the last paragraph sounds as clear as mud so please post back if you need any explanations or have any other questions.

[brookierook]

Bob! Old friend, how are you? Just wondering if I can borrow that wondrous brain of yours for a question about queries / reporting and duplicates? If you have the time... please?

Brooke

[Bob Fitz]

Brooke
As always, I shall be happy to help, if I can .
What, exactly, are your problems?

[brookierook]

Hi Bob.

So, when I am querying, it gets a little funny with my subforms. There are a couple of examples in the attached DB. I would like to get rid of the duplicates that appear when I run the queries (especially on the methodology query). When I add countries to it, I get 40 or so results back... can we start there?FLS Projects 2013.zip

[Bob Fitz]

Hi Brooke

Have just tried to look at your db but I can not open it in that format. Can you post a copy in A2003 mdb format please.

[brookierook]

Doh! I completely forgot that. There are a few things on my db that are not compatible with 2003. Trying to get rid of them so I can save it as 2003.. but can't figure out what they are!!!

[Bob Fitz]

If it's of any help, the following link compares the various versions. http://www.opengatesw.net/ms-access-...Comparison.htm

[brookierook]

Ok, so I finally figured it out! Methodology 2 is the problem (as it was a tick box). Which really is irritating because I need to query that! Not sure of the best way to go about it.FLS Projects 2013 (2).zip

[Bob Fitz]

Brooke,
The db attached to your last post is not in A2003 mdb format.

[brookierook]

Ok, I am losing it! Here you go.FLS Projects 2013 (3).zip