Help with table structure please! (1 Viewer)

[alfredo245]

Hi Experts,

I have a quick question. I am building a Database for work (I work in a bank). So far, the database consist of the following tables:

[FONT=&quot]- [/FONT]tbl_Sales Rep
[FONT=&quot]- [/FONT]tbl_Client
[FONT=&quot]- [/FONT]tbl_Accounts (this could be considered “Products” table)
[FONT=&quot]- [/FONT]tbl_transactions (this could be considered the “orders” table)

Each client should have a Unique Sales Rep. However; there are instances when an account has 2 different Client IDs. For example, a joint saving account (acct id 0558) might look like this:


Client ID | Account ID

123 | 0558
185 | 0558


In those cases, both clients, Client 123 and 185 should have the same Sales Rep. assigned. So my question is; how can ensure that the user of the database would not assign two different Sales Rep IDs to each of those clients IDs when they have joint accounts like the one described above?


I have been watching some videos online and I think a junction table is the answer, but I am still unsure. Please advice. Thanks a lot!

 

[spikepl]

So if a couple, after going steady, gets married and makes one or both of the accounts joint, they fire one Rep?

 

[plog]

That means a sales rep doesn't get assigned to an Account, but to a Client. Therefore, the tbl_Client should have a field to hold who the sales rep is and not tbl_Accounts.

 

[JLCantara]

According to the bold characters in the OP, SalesRep should be linked to the account. On the other end, Spike's question is quite pertinent.
By the way, it's the first time I hear that a bank is using Access to manage accounts...

 

[alfredo245]

Ok I think I should’ve put more details.

I have 3 tables and all of them are related.

tbl_SalesRep
tbl_Client (linked to tbl_SalesRep)
tbl_Account (linked to tbl_Client)

Let’s say that at the beginning of the year I have the following data:

tbl_SalesRep has the following fields:

SalesRepID | SalesRep Name
010 | Peter
012 | Nolan
015 | Daniel

tbl_Client has the following fields:

ClientID | CName | SalesRepID_FK
5105 | Michael | 010
5986 | Tom | 012
5644 | Sarah | 015
8965 | Charles | 012

tbl_Account has the following fields:

AccountID | Account Description | ClientID_FK
698587 | Saving | 5105
687684 | Loan | 5986
568768 | Checking | 5644
478855 | Loan | 8965

On the following year, I download the data from an external system and I have 2 additional clients and 3 additional accounts.

Additional/New Client IDs:
[FONT=&quot]- [/FONT]5208 (Debbie)
[FONT=&quot]- [/FONT]5677 (Frank)

At this point, the user of the database needs to assign a SalesRepID to each of these clients. The tricky part is below:

Let’s say the account IDs associated with these clients are:
[FONT=&quot]- [/FONT]956852 (Client ID = 5208, Debbie) – New Account
[FONT=&quot]- [/FONT]865285 (Client ID = 5677, Frank) – New Account
[FONT=&quot]- [/FONT]478855 (Client ID = 5208, Debbie) – Existing Account

As observed, account # 478855 already exists and is also associated to ClientID 8965 (Charles) whom Sales Rep is 012 (Nolan). Under these circumstances, Debbie should also be associated with Sales Rep 012 (Nolan). As a result, the tables should ideally look like this:

tbl_SalesRep: Stays the same.

tbl_Client:

5105 | Michael | 010
5986 | Tom | 012
5644 | Sarah | 015
8965 | Charles | 012
5208 | Debbie | 012
5677 | Frank | 015

tbl_Account:


698587 | Saving | 5105
687684 | Loan | 5986
568768 | Checking | 5644
478855 | Loan | 8965
956852 | Term deposit | 5208
865285 | Checking | 5677
478855 | Loan | 5208

I am looking for a way to restrict the user to only be able to apply an specific SalesRepID to a new client every time when the new client shares an account ID that is associated with an existing client who already has an assigned SalesRep.

If there is no restriction for what I described above, the user could potentially assign a different SalesRep ID for each Client ID. This would create problems when grouping by SalesRep.

Thanks a lot for your help!

 

[alfredo245]

I am almost sure it has to do with a Many to Many relationship. But I don't know how to structure it.

 

[jdraw]

alfredo,
I agree with JL re Access managing accounts in a bank??
Where are you located?

 

[alfredo245]

Guys,

This is just a database that I am building for data analysis on a small project. It has nothing to do with data management of the company/or even data analytics, nor managing accounts. Can you please help me by replying to my question?

Thanks.

 

[JLCantara]

@Alfredo: the solution is quite simple but you will need some knowledge of VBA. Attached is a sample relationship; I do not pretend that it's a solution but a caneva used for the sql solution:

PARAMETERS
prmAccount Long;

SELECT
Accounts.Account, SaleReps.SaleRep

FROM
SaleReps INNER JOIN (Accounts INNER JOIN Clients ON Accounts.Account = Clients.Account) ON SaleReps.SaleRep = Clients.SaleRep

Now can you implement it?
WHERE (((Accounts.Account)=[prmAccount]));