Multiple tables for multiple users?

[FishCop]

Hello,

Firstly, apologies if this is in the wrong place - never used a forum before...

So - I am trying to make an db that multiple users can input data into at the same time. My thinking has been to make a table for each user, split the database - a back end db for each user, get each user to enter there name from a combo box or whatever, write code to get an input form to point at the right table. I think i can do all that but...I have no idea what to do with the data from all the different tables!

I wanted to simply query them all together but i think my relationships have gone a bit funny because when i sum the records i get huge values that are not correct. I have dealt with this before but grouping and that but it doesn't seem to be working.

Basically my db has three layers
1. A fishery table - (species, opening date etc)
One to Many
2. A fishery open date table (linked via ID field from above table)
One to many
3. A landings weight table - there is a landings weight table for each user (around 6 of us) - (linked to Open date table via Open date ID)

So for each fisheryID there are several OpenDateID, and for each OpenDateID there are several LandingsWeightID.

I want to - for example - figure out how much has been landed for one fisheryID. Easy with one landings weight table but i have 6 landings weight tables (only 3 in my test db actually). When i try to sum landings weight for a fisheryID i large and incorrect number - as if its multiplying the actual figure many times.

This is turning into a very long thread so apologies for that - i guess my first question is should i be using this multiple tables method at all and if so, does anybody know how to use the data form multiple tables in this way?

Any help would be greatly appreciated, hope to hear from you soon.

All the best

Luke

 

[pbaldy]

From the sound of it, I would have a single table with a field for the user. That would let you easily sum a fishery, a user, certain users, certain fisheries, etc.

 

[FishCop]

Thanks for that Paul,

I agree i have made this very difficult for myself - one of the things i need to be able to do though is set it up so more than one user can input data at once i.e. so it doesnt stop them from entering the db because someone else has it open already. Can i split a single table so different users can write onto that table at the same time (from different laptops/different front ends)?

 

[pbaldy]

Multiple people entering data shouldn't be a problem. You'd have issues with people trying to edit the same record at the same time. You don't split the table, just the db. Make sure the Default Open Mode setting in Access Options/Advanced is Shared.

 

[FishCop]

Awesome... so i could have multiple front ends and just one table and they could all still enter data into it at the same time? Just checked that setting and its set to shared.

Cheers Paul!

 

[pbaldy]

Happy to help!