Two Tables

[DevAccess]

Hello I have below two tables
Main table:
ID(PK,autonumber) => First Name => Last Name=> Emaild ID==> Address ==>
Usertable :
ID (PK, autonumber) => FIrst Name ==> Last Name=> User ID => Password=> Role=>


Now my query is that when data is being imported into Main Table it should import data automatically into usertable as well, My code would only import into Main table not into usertable. therefore auto population of usertable of firstname and lastname is possible ?

Also when I delete the record from maintable pertaining to user it should delete as well from usetable.

Please advise how to do this.

Thanks

 

[plog]

You don't. That's not how databases work. You don't store redundant data, instead this data should exist in the same table.

Perhaps you can explain the context of this system better. How often do you import data? From where does the imported data come? Where\how does the userid\password\role fields get populated?

 

[DevAccess]

I would like to have Login screen wherein user would be login with UserID and password from usertable but whenever data is being populated from excel to main table the same record should be created with first name and last name in the user table as well so eventually admin will only created UserID for existing user or new users if any.

This way he dont create any spelling mistake or while creating same user in the usertable.

The goal here is that we need to have login screen with userID and password that is being stored in usetable and when record is being deleted from main table it should also get deleted from usertable as well.

 

[plog]

I would like to have Login screen wherein user would be login with UserID and password from usertable but whenever data is being populated from excel

That made me more confused than anything. I have no knowledge of your system, so when you reference excel I have no idea what you are talking about.

So, go back to my first post and answer my questions. Explain why you have 2 datasources, explain about the import process, explain everything that exists.

 

[DevAccess]

You please dont worry about Importing and how it is done that is working pefectly.

My concern is that everyuser which is being imported should have uSerID and passowrd so they can login into the system and thats why I would like to have usertable which would eventually will hold all first name and last name data which is there in main table.

Eventually I would like to have database in such a way that each user have role called Normal user and super user/admin in user table. now if user has normal user they can edit only his/her record if user is super user/admin he /she edit all the records in the database and also can manage the users which is user table.

So having first name and last name same as main table in user table would reduce the chance of spelling mistake and would be enforce consistency by removing from main table it would delete the recrd from user table automatically would ease of maintaining data.

Hope this clarify.

I appriciate your all your help.

 

[plog]

Again, that's not how databases are to work. Data shouldn't be moved around tables, nor should tables be 'synchronized'.

If you are unwilling to explain your current process then I will not be able to help you achieve a solution.

 

[DevAccess]

Again, that's not how databases are to work. Data shouldn't be moved around tables, nor should tables be 'synchronized'.

If you are unwilling to explain your current process then I will not be able to help you achieve a solution.

I will explain you the situation as below.

We would like to have a database in such a way that each user should be able to his/her record only super user can edit all records.

I have table with 16 users in excel with details below information.

First Name => Last Name=> Emaild ID==> Address ==> and new more fields.....

So thought of imporing this into main table with ID as PK.


Importing to access from excel spreadsheet works but since both have same fields on each side and I have written VBA code to import or could have used existing access functionality of import but this is not an really issue.

Now since every user can edit only his/her records I thought of having different table called user details which would also hold first name and last with password and role.

if role is super user/admin user would have few more button which can see all the records on the form and can save them but if user is normal user/user then he would only can see his data and he can not see other buttons which admin can see.

So on login form I am setting up tempvars with role and user name combining first name and last name [ all from user table not main table ] so same can be used in query to filter the data of main table and normal user can edit their own data only so therefore if the data between not synchronized there could be possibility of errors.

Super user can edit all the data from main table as well user table while normal user can edit /update his only record from main table.

Why I need the user table is to filter the data and get the role to hide the button which should be available to admin so password and roles are needed.

Hope I clarify now, I again appreciate your help.

 

[plog]

Why do you store data in Excel? You should just have a User table in Access.

 

[Galaxiom]

User table should not record the names if they are already held in the main table. The record in the main table should be referred to from the User table.

Uer table fields should be:
MainID (FK)
UserID
Password
RoleID

 

[DevAccess]

User table should not record the names if they are already held in the main table. The record in the main table should be referred to from the User table.

Uer table fields should be:
MainID (FK)
UserID
Password
RoleID

How come MainID as FK, one record in main table should relate to one record in user table is not it ?

 

[The_Doc_Man]

You should MERGE those tables into a single table and if needed later, UPDATE the missing fields that you would have kept in the "other" table. Pick one table to keep and add the other table's extra fields.

From your description, you are creating an unnecessary one-to-one relationship between the two tables. There are a few (VERY FEW) reasons that you might employ a one-to-one relationship. This doesn't appear to be one of them.

If you merge those two tables to one, there is nothing to synchronize. Everything is in one place. One place to fix spelling errors. One-stop-shopping for all your user ID needs. End of problem.

Access is all about more efficiently using the data you have. Putting it in multiple places is the wrong answer 99.99997% of the time. (Remaining 0.00003% is fudge factor for the true odd-ball case - which I don't see in what you described.)