×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

6 linked tables. View All, and change data

6 linked tables. View All, and change data

6 linked tables. View All, and change data

(OP)
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

CODE -->

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

RE: 6 linked tables. View All, and change data

If you have a table ([Manager_1-11]) with fields like:
Name1
Name2
Name3
Accounting Dept1
Accounting Dept2
Accounting Dept3

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.

RE: 6 linked tables. View All, and change data

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

RE: 6 linked tables. View All, and change data

(OP)
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

RE: 6 linked tables. View All, and change data

"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.

RE: 6 linked tables. View All, and change data

(OP)
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

RE: 6 linked tables. View All, and change data

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" ... sad

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.

RE: 6 linked tables. View All, and change data

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:

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

 

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

RE: 6 linked tables. View All, and change data

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



RE: 6 linked tables. View All, and change data

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.

CODE

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....

CODE

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:

CODE

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...

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close