How do I send data from one Form to two or more different Tables on Access 2010

Question

Hello,

I´d like to send data to two or more different tables depending on 2 fields of this form. Can I achieve that on access 2010?

My system is a database of problems that have to be reported to different managers. Each manager should only see and be able to respond on it´s own area problem.

One of the reason why I want more than one list is because a unified list of problems  will grow huge (more than 10000 itens) in less than 6 months. 

Thank you

Answer 1

Hi. You might think you're trying to avoid a problem by splitting the data into separate tables with duplicate structures, but you're actually creating problems for yourself by doing that. To the users, it doesn't matter if you're using one table or more. The user interface shouldn't be affected by that. But the burden will be on the developer if you use more than one table. For example, you wouldn't be here today asking this question if you only have one table. You can still prevent users from viewing records that don't belong to them by simply using a query that filters the data. Hope that helps...

Answer 2

I agree with the DB guy...  The size of the table shouldn't be a problem if you have used the right indexes. Artificially denormalizing the data could lead to problems of duplicate data and inconsistency. 

Forgive me if this is already obvious to you, but if you have a many-to-many relationship between managers and problems, you'd normally create a "join table" having problemID and managerID columns, with a unique compound index covering those two columns. If all problems are unique and specific to just a single manager, then you could just add a managerID column to the problems table.  Either way, in your forms and queries, you just filter or join the data as needed to make it specific to a particular manager.

I you are unsure about performance with thousands of items, you could always write a query or a little VB code to generate an arbitrarily large result set to use as a test table to check.