Merging data from multiple Tables to one table

[Tempuser]

Hi!



I want to design a database for 14 users with individual access only to thier data.The catch is i want the data entered by all 14 users to reflect in one database.I tried to use update query but I succeeded in merging two tables only..

any help is highly appreciated.

Thank you.

[dcrake]

Why have they got individual tables? Can you not use a FE BE setup? or are the systems stand alone with no network communication?

David

[Tempuser]

Why have they got individual tables? Can you not use a FE BE setup? or are the systems stand alone with no network communication?

David

Thanx David,

but what I require is that when these 14 users enter thier data into thier respective tables all these entries must reflect in the 15th table

[dcrake]

I understand what you are saying, I'm just saying why? Surely if you had one table you could include the owner of the record as a FK. You must have this implemented in the 15th table as how do you know where it came from?

Again are all the 14 users networked or not?

David

[Tempuser]

I understand what you are saying, I'm just saying why? Surely if you had one table you could include the owner of the record as a FK. You must have this implemented in the 15th table as how do you know where it came from?

Again are all the 14 users networked or not?

David

Hi David , many thanks for your help

I have 14 users with the same data as mentioned below:
(Sr No
Account Handled by
Customer Name
Customer requirements
OrderDate
Customer contract start date
customer region) what I want is the individual user must have edit rights only to his records and readonly access to the remaining 13 users details.Can u help with some other solution how should I proceed.Your help in this regards is appreciable

[Tempuser]

[QUOTE=Tempuser;10286]Hi David , many thanks for your help

I have 14 users with the same data as mentioned below:
(Sr No
Account Handled by
Customer Name
Customer requirements
OrderDate
Customer contract start date
customer region) what I want is the individual user must have edit rights only to his records and readonly access to the remaining 13 users details.Can u help with some other solution how should I proceed.Your help in this regards is appreciable
all the users are in a network

[dcrake]

Do your users have to log into the application using a username and password?

If so, when the user brings a record to the screen the edit option should be set according to who is the owner of the record.

David

[Tempuser]

Thanx dcrake,

Users have to log into the application using a username and password.

I need ur help on how to proceed.

[dcrake]

Ok When you create the parent record or any other table that visislbe to the user then you need to have addtional fields in the table such as:

DateCreated
CreatedBy
LastUpdatedDate
LastUpdatedBy

In these fields you would record the username or the primary key form your users table. Then in your forms or in any queries that include the parent table filter them to be for that user only.

So for example:
Jon Smith logs in as JSmith in the users table his Primary Key = 5

TblUsers
UserId:5 - PK
UserName:JSmith
Password:***

TblSales

SalesId:1 - PK
CustomerID: 33 - FK
.....
CreatedBy:5
CreatedDate: 01/01/2009
UpdatedBy:5
UpdatedDate: 01/10/2009


Next Create a Public Variable in a standard module

Public LngUser As Long

Next create a Public function in the same standard module

 
Public Function MyCurrentUser() As Long
   MyCurrentUser = LngUser
End Function

Now when the user logs into the application and their username and password is verified you pass the users id to the LngUser variable

LngUser = DLookup("UserID","TblUsers","Username='" & Me.LoginName & "'")

This will remain available throughout the life of the session.

Now lets say they open a form that displays all the sales made by Jon Smith. The forms RecordSource on an underlying query Called QrySales.

If you then go to the design of the query and bring down the CreatedBy field and in the condition underneath the CreatedBy column enter

=MyCurrentUser()

The query will only display sales for Jon Smith (Pk = 5)

This can be repeated anywhere in the system wher e filtering is required.

There is a more consice example of this in the sample databases section.

Using Public Variables.

David