How do i structure a 'multi company' database

[shenty]

OK guys i'm struggling. I would class myself as somewhere between rookie and amateur at doing access databases but this current task i have set myself is making me wonder whether i'm more the former !!

I'm trying to setup a database that tracks the purchase of animal feeds for a farm. The structure of this i understand reasonably well and have used the Northwind Sample DB as a base for my new venture.

But...i am trying to go 1 step further by making this database a 'multi' farm affair.

So far i have created a form that loads at startup asking which farm is to be the active farm. This form saves a number to a table 'tblActiveFarm'. Nothing wrong with that.

I have also created the form 'frmFeedOrders' that starts by adding a new record to the table 'tblFeedOrders' which stores the active farm number. In the same form i have setup a products listbox that when double clicked should add a new purchase of 'feed' to the subform.

However, no matter what i have tried with relationships, primary/foreign keys i just can't seem to understand how to structure the database.

The error i seem to be getting most often (when double clicking the products listbox) is "You can't go to the specified record" (i notice when debugging that it happens with the DoCmd.GoToRecord,,acNew command).

I did think at one point i had succeeded using the Northwind DB as a structure. (ie OrderTable, ProductTable & OrderDetails table) but it would not let me have a duplicate product entry in the listbox

I am aiming for the subform to show ALL feed purchases AND allow me to add NEW feed purchases for the currently 'active farm'.

I have attached my cutdown DB and really would appreciate any assistance. I don't expect anyone to do it for me but if anyone could explain how to set it up (with regards primary/foreign keys) or tell me which fields need to be included in which table i would be hugely grateful.

 

[John Big Booty]

Why not add an additional table to store the details of your various farms.

Then add an additional filed to your order header table, to store the farm ID, this you could populate via a combo on your order header form.

 

[shenty]

Hi

I already have a table storing the farms details. 'tblFarm' - i did consider briefly doing it this way but this database is going to be developed to include a lot more than just the feed purchased. It will also record minerals, field crops etc and eventually will get incorporated into my animal database. There are currently about 6 different databases for the 3 different farms. I think if a field was added to each area to pick which farm there would be too much of error risk.

I really need to pick which farm is active as soon as the DB loads and avoid having to enter this information again.

But i do appreciate your input and look forward to hearing any other suggestions.

Many thanks

 

[shenty]

OK i have managed to get it working in a fashion. But i still can't get a new record line to appear in the subform.

frmFeedOrder is the main form for adding feed purchases. Could anyone tell me why it won't add products to the subform ?

Thanks in advance.

(New DB attached).

 

[NigelShaw]

Hello mate,

why cant you do this in a single database? it looks nothing more that a standard option. if you want the look of the option to choose what farm to use, why not have the form just select the record instead of something else.

i would think a simple one to many relationship is whats needed i.e.

one farm to many options. one option to many other options

at least keeping it simple, you could do global updates like grain price increases or anything else that would link them together. by tracking the ID of the farm itself, you could easily separate the data. for example-

what i tend to do is have a field in every table that will receive the farmID. the use queries based on the ID to gather information.

Nigel

 

[shenty]

thanks all for your help - i think its working now