6 linked tables. View All, and change data

[puforee]

In my DB I have 6 linked tables based on user unique ID (BEMS). These tables are set up do be able to show user/management hierarchy. This tends to change many times over a year. I want to be able to show all 6 tables on a form and be able to change any one of them. But, my query will not allow changes. Here is my query

SELECT [Manager_1-11].MGR1_BEMS, [Manager_1-11].Name1, [Manager_1-11].[Accounting Dept1], [Manager_2-11].MGR2_BEMS, [Manager_2-11].Name2, [Manager_2-11].[Accounting Dept2], [Manager_3-11].MGR3_BEMS, [Manager_3-11].Name3, [Manager_3-11].[Accounting Dept3], Manager_41.MGR4_BEMS, Manager_41.Name4, Manager_41.[Accounting Dept4], Manager_51.MGR5_BEMS, Manager_51.Name5, Manager_51.[Accounting Dept5], Manager_61.MGR6_BEMS, Manager_61.Name6, Manager_61.[Accounting Dept6]
FROM (((([Manager_1-11] LEFT JOIN [Manager_2-11] ON [Manager_1-11].[Managers Bems2] = [Manager_2-11].MGR2_BEMS) LEFT JOIN [Manager_3-11] ON [Manager_2-11].[Managers Bems3] = [Manager_3-11].MGR3_BEMS) LEFT JOIN Manager_41 ON [Manager_3-11].[Managers Bems4] = Manager_41.MGR4_BEMS) LEFT JOIN Manager_51 ON Manager_41.[Managers Bems5] = Manager_51.MGR5_BEMS) LEFT JOIN Manager_61 ON Manager_51.[Managers Bems6] = Manager_61.MGR6_BEMS;

Is there any way I can show all tables as they are linked, like the query above, and still be able to change data in one or more of them.
Example: Manager_3-11 table has a manager that needs to be changed. To keep it simple the new manger will report to the same manger. But, his lower level mangers will have to have their data changed to reflect the new managers unique ID (BEMS). So, I need to delete the old manager and add the new and his ID and then copy the ID into the lower managers table.

Can you help? Is this set up too complex. Is there an easier way to store all the managers information and their levels? Like maybe in one table?

Thanks
John

 

[Andrzejek]

If you have a table ([tt][Manager_1-11][/tt]) with fields like:[tt]
Name1
Name2
Name3
Accounting Dept1
Accounting Dept2
Accounting Dept3[/tt]
Then you do not have normalized DB

" Is this set up too complex" I don't know about "complex", it is just wrong (IMHO)
"maybe in one table" - I don't know if you can do it in one table, but surely not like you have now. (Again, just my opinion)


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I expect this is too complex to create an updateable query. Nearly all of the BEMs would need to be primary keys in their respective tables.

I would attempt to use subforms for editing and possibly some code. Are all of your "Manager" sources tables or queries? Most hierarchy structures use a single employee table with the EmployeeID and a ManagerID.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

Thanks for the responses. So Andy, table [Manager_1-11] shows first level managers Name, BEMS (ID), Account and 2nd level mangers BEMS(ID). These same fields are used for each table, like Manager_2-11 table has second level 2 manger Name, BEMS, Account # and 3rd level managers BEMS, etc. And, for Duane, all the BEMS(IDs) are primary keys.

Right now the Query is a Select query.

Hopefully this helps and hopefully you can provide further help.

Duane, I thought about the sub-forms also. Do you think this would be the best way to go?

Thanks to both,

John

 

[Andrzejek]

These same fields are used for each table" - that's just my point. If you have tables with the same fields, that is not normalized way of keeping the data.


---- Andy

There is a great need for a sarcasm font.

 

[puforee]

I think I am missing your point Andy. Note: field names below are approximate

The first table has these fields: MGR_1Bems, MRG1_Name, MG1R_Account, and Manager2_BEMS
The second table has these fields MGR_2Bems, MGR2_Name, MGR2_Account, and Manager3_Bems.
etc

The only data repeated in more than one table is the Managers BEMS is the Primary key for the connections.

How would you have it normalized?

Be patient please,

John

 

[Andrzejek]

It is hard to give a definite answer when the names of fields change from your OP to the last one, and on top, they are "approximate" ... :-(

All what I know is: when you have tables with very similar names, and tables with fields with numbers 1, 2, 3, 4, ..., there is a better way to set it up. My rule is: the data can be in one and only one place in your DB. I.e. cannot be repeated in multiple places.


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Did you try adding the tables one at a time to see when they no longer become updateable?

I would expect a normalized table like:

[pre]tblEmployees
========================
empEmpID PrimaryKey
empFirstName
empLastName
empHireDate
empMgrEmpID stores the tblEmployees.empEmpID of the manager
emp... other fields

[/pre]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Darrylles]

puforee,

I would strongly suggest (if you are going to continue using databases) that you research NORMALISATION. Try this Link

What Andrzejek is ssying is:
A database table DEFINES an object - it's properties / attributes (that's why table names should never be plural).


E.g.

tblManager
========================
pk PrimaryKey
first_name string
last_name string
hire_date date

You would store many managers in this table, but let's say you store 3.
For that, you would add 3, separate manager records.

What you seem to be doing is this:

tblManager
========================
pk PrimaryKey
first_name_1 string
last_name_1 string
hire_date_1 date
first_name_2 string
last_name_2 string
hire_date_2 date
first_name_3 string
last_name_3 string
hire_date_3 date

And storing ONE record for all 3 managers.
This is a no-no in relational database design.

ATB,

Darrylle

 

[Darrylles]

puforee,

I would strongly suggest (if you are going to continue using databases) that you research NORMALISATION. Try this Link

What Andrzejek is saying is:
A database table DEFINES an object - it's properties / attributes (that's why table names should never be plural).

E.g.

tblManager
========================
pk        PrimaryKey
fname     string 
sname     string
hdate     date

You would store many managers in this table, but let's say you store 3.
For that, you would add 3, separate manager records....

pk           1           2              3
fname        Fred        Jim            Tom
sname        Bloggs      Smith          Jones
hdate        13/01/99    17/05/85       23/09/15

What you seem to be doing is this:

tblManager
========================
pk         PrimaryKey
fname_1    string 
sname_1    string
hdate_1    date
fname_2    string 
sname_2    string
hdate_2    date
fname_3    string 
sname_3    string
hdate_3    date

And storing ONE record for all 3 managers, like this...

pk              1
first_name_1    Fred
last_name_1     Bloggs
hire_date_1     13/02/99
first_name_2    Jim 
last_name_2     Smith
hire_date_2     17/05/85
first_name_3    Tom
last_name_3     Jones
hire_date_3     23/09/15

This is a no-no in relational database design.

ATB,

Darrylle