Merging of Tables in One New Table

[Shaun]

I am having 5 tables I want to merge the data in one table. For example the
fields of the table is given below for your reference.

Field 1 - Customer Id
Field 2 - Error Done By(Employee Id)
Field 3 - Error Comment
Field 4 - Error Tracked By (Employee Id)

Like that I am having fields in table. Each table consist the same Fields.
Like this 5 tables created and five forms are created.

I have assigned user level security for these 5 tables. For example if one
person is using the first table he can able to access the first table and
first form only. He should not be able to view what the other persons have
entered. So like that I have allocated five table & five forms to five
persons. Now I want to merge all the five table details in one new table
that will be used by the Head who is chief for that team.

So anyone please tell me how to merge the five tables in one new table. If
any one is updating any record in that five table then it should be updated
in the New consolidation table which is used by the Head (chief).

So please provide a solution for this.

Thank you,

Shaun

 

[Wayne-I-M]

Best not to as what happens if a customer has more than one erorr.

You need a customer table and erorr table and (I think) employee table

Table 1
CustomerID
Plus other details

Table 2
ErorrID
CustomerID
EmployeeID (tracked by)
ErrorComment
ErrorDate
Plus other details

Table 3
EmployeeID
Plus other details

 

[Shaun]

Dear Wayne,

Thanks for your reply.

But if i create the table like what you have mentioned below then all the
persons can able to view the details which person is done more mistake. I
want to make the person to view only the error detail which is updated by
that person only. They should not be able to view other persons updated
details. So Please provide any solution for this.

Thanks.

shaun

 

[Wayne-I-M]

There are a number of methods to do this.

One - veyr simple and NOT secure (if thery know anything about access)

Add a new field to each table, then filter the form(s) with a query using
Application.CurrentUser

Which every method you use your going to have to add some form of user level
security or at least user recognition (like a log in name) so your
application knows who they are.

 

[Wayne-I-M]

Soory I pressed enter to soon

Main thing I would say (some people may have other ideas) is that you should
not create a table for each user - thats a mad method.

You need to know the user and then filter the record they can look at

User level securtiy would be the best option

Search google for
access user level security
and you will get lots of answers

 

[Shaun]

Dear Wayne,

Thanks for your valuable reply.

Shaun