Information from form, to table, copied to ANOTHER table

[Marie-Lynn]

Hello,

I have several forms that specific users will enter specific pieces of
information on. Where user A enters information set A on form A and user B
enters information sset B on form B, so on and so on, and then table E is a
compilation of all of the user's tables.

How do I get table A and table B, so on and so on, to write to table E? Is
there a way to do this updating automatically, or does it take user input
(outside of the initial users input)?

 

[Arvin Meyer [MVP]]

Is there any reason why users A, B, C, etc. aren't writing to table E to
begin with?

 

[John W. Vinson]

Hello,

I have several forms that specific users will enter specific pieces of
information on. Where user A enters information set A on form A and user B
enters information sset B on form B, so on and so on, and then table E is a
compilation of all of the user's tables.

How do I get table A and table B, so on and so on, to write to table E? Is
there a way to do this updating automatically, or does it take user input
(outside of the initial users input)?

You DON'T.

Relational databases use the "Grandmother's Pantry Principle": "A place - ONE
place! - for everything, everything in its place". Data should NOT be copied
from one table to another table except in very limited specific cases, and
redundancy should be avoided. What you're asking is "how can I make my
database structure redundant, duplicated, and overly complex"; and I'd suggest
"unasking" the question!

You should be able to create a Query joining tables A, B, C and D; and/or a
Form with Subforms; and/or a Report perhaps with Subreports. It is NOT
necessary nor is it good design to jam all sorts of disparate data into a
single table.

 

[Marie-Lynn]

Woah, woah, woah!

Everyone is getting mad at me for redundent tables, copied information,
misuse and abuse of Access but I have a REASON! Maybe you might have a
better SOLUNTION...? Oh Access Gurus, stop slapping this green, elementary
designer about!!! (smile)

My reason/s for having redundent tables are:
a) So that user A is only able to browse table A, and so forth with the
other users, whereas Managers would be able to browse all data in a
consolidated format. (security)
b) So there is easy consolidation of databases that are in seperate
locations. (compatibility between current/future databases)
c) I'm sure there are other reasons, but I can't think of them right now.

ML

 

[Arvin Meyer [MVP]]

So, the way to handle this is to use either Access User-Level Security, or
more simply a function to find the current user. In the first case it will
be the Access user, in the second the Windows logged in user:

http://www.mvps.org/access/api/api0008.htm

Now build a small table of users with their login names, and use that as a
source for a new column in your big table. Base all the forms on queries
with the username as a criteria.

The managers will have a form based on the same query without any criteria.

Now, you've implemented the perfect solution and all data goes in a single
table. Your reason becomes moot. And BTW, we don't get made at anyone and
only rarely slap a Microsoft programmer from time to time.

 

[Marie-Lynn]

That seems like a reasonable solution. I shall try it out, though it will
take me some time to learn how. Thank you!