DataBASE Design - Assign Customers (1 Viewer)

MatthewB

Member
Local time
Today, 00:48
Joined
Mar 30, 2022
Messages
85
I have 5 managers and 40 buildings, What is a good way to create building portfolios, but then also make it so these building can be move between manager if necessary, swapped or assigned., so the portfolios change. I want to make this front end so each manager only sees the buildings in their portfolio.

Thanks
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:48
Joined
Feb 19, 2013
Messages
16,553
you need 3 tables as a minimum

tblManagers
ManagerPK
ManagerName
...

tblBuildings
BuildingPK
BuildingName
...
...

tblManagedBy
ManagedByPK
ManagerFK
BuiildingFK
ManFromDate
ManToDate
....

Join the FK's to the relevant PK's

You can determine who is the current manager (ManToDate will be null or >today)

Or who was managing it on a specific date - useful perhaps when covering for holiday

Technically you don't need the ManTo date - (in which case current manager will be max Max(ManFromDate) < today) unless the buildings can cease to be managed. It is effectively covered by the next manager ManFromDate field.

To do it properly you may also need employed from/to dates for managers and undermanagement from/to for buildings

And perhaps you have to cater for joint management? If so, table design would not change, but the rules would
 

bob fitz

AWF VIP
Local time
Today, 07:48
Joined
May 23, 2011
Messages
4,717
I don't understand what
I have 5 managers and 40 buildings, What is a good way to create building portfolios, but then also make it so these building can be move between manager if necessary, swapped or assigned., so the portfolios change. I want to make this front end so each manager only sees the buildings in their portfolio.

Thanks
has to do with the thread title
 

MatthewB

Member
Local time
Today, 00:48
Joined
Mar 30, 2022
Messages
85
I don't understand what

has to do with the thread title
Bob
As a novice to Access, it is not clear to me how better to tag this question - being brief and to the point. In my opinion there is a general problem with user forums on this front, such that searching the forum is ineffective. So yes, what is the best way to put the question so that others can make use of it? Why don't you make a recommendation rather than make a comment that is perhaps as useless as my title. regards
 

MatthewB

Member
Local time
Today, 00:48
Joined
Mar 30, 2022
Messages
85
you need 3 tables as a minimum

tblManagers
ManagerPK
ManagerName
...

tblBuildings
BuildingPK
BuildingName
...
...

tblManagedBy
ManagedByPK
ManagerFK
BuiildingFK
ManFromDate
ManToDate
....

Join the FK's to the relevant PK's

You can determine who is the current manager (ManToDate will be null or >today)

Or who was managing it on a specific date - useful perhaps when covering for holiday

Technically you don't need the ManTo date - (in which case current manager will be max Max(ManFromDate) < today) unless the buildings can cease to be managed. It is effectively covered by the next manager ManFromDate field.

To do it properly you may also need employed from/to dates for managers and undermanagement from/to for buildings

And perhaps you have to cater for joint management? If so, table design would not change, but the rules would
Thanks for the reply. I will work on your approach.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:48
Joined
Feb 28, 2001
Messages
27,001
@MatthewB - I perfectly understand your reply to Bob Fitz's comment. So that you understand it from our side, ... We have often been burned by what appears to be a simple request turning into a long drawn-out exercise in pulling teeth from a reluctant patient. Fortunately, your explanation WAS better than many we have seen from new members.

The discussions of tables that you got in response are definitely a good starting point and I won't add to that. I want to address another thing you said.

I want to make this front end so each manager only sees the buildings in their portfolio.

To control your data's visibility, tables aren't enough. Depending on just HOW much control you want, you will be working towards hiding the back-end structure from those who use the DB. This usually means that you NEVER show your users anything but forms and reports.

In my opinion there is a general problem with user forums on this front, such that searching the forum is ineffective.

The general problem is knowing what to ask. I don't disagree with you. But I can help there, too.

You find out about your topics by searching the forum for "Securing a Database." In greater detail, this will include topics such as "Hiding the Ribbon" and "Hiding the Navigation Panel" but will ALSO include "Switchboard forms" and "Dispatcher forms." You will need to understand how to use a "form filter" or "report filter."

Part of this level of complexity is because in order to limit what someone sees, you can NEVER EVER directly show them a table, query, macro, or any VBA code. When users see those levels of structures, you have lost all control, period. Showing only forms or reports (forms in FORM view, reports in REPORT or PREVIEW view) means YOU can restrict which data elements they see, which PARTS of each table - both with respect to which fields AND with respect to which records.

Finally, your life will be made simpler if you understand "Database Normalization" fairly well. Don't worry, it's not THAT long a topic. But I'll give you a tip or two there, too. You can search this forum for "Normalization" because this IS a database forum. But if you do a general web search, you should search for "Database Normalization" because other disciplines (math, chemistry, psychology, international diplomacy) also use the word "Normalization." IF you use the general web, start with articles originating from .EDU domains - not because they do a better job, but because they are less likely to distract you with ads and are less likely to be discussing a proprietary product that you don't have anyway. But once you are comfortable with the basics, .COM domains will have good articles, too. Just sometimes more cluttered.
 

MatthewB

Member
Local time
Today, 00:48
Joined
Mar 30, 2022
Messages
85
@MatthewB - I perfectly understand your reply to Bob Fitz's comment. So that you understand it from our side, ... We have often been burned by what appears to be a simple request turning into a long drawn-out exercise in pulling teeth from a reluctant patient. Fortunately, your explanation WAS better than many we have seen from new members.

The discussions of tables that you got in response are definitely a good starting point and I won't add to that. I want to address another thing you said.



To control your data's visibility, tables aren't enough. Depending on just HOW much control you want, you will be working towards hiding the back-end structure from those who use the DB. This usually means that you NEVER show your users anything but forms and reports.



The general problem is knowing what to ask. I don't disagree with you. But I can help there, too.

You find out about your topics by searching the forum for "Securing a Database." In greater detail, this will include topics such as "Hiding the Ribbon" and "Hiding the Navigation Panel" but will ALSO include "Switchboard forms" and "Dispatcher forms." You will need to understand how to use a "form filter" or "report filter."

Part of this level of complexity is because in order to limit what someone sees, you can NEVER EVER directly show them a table, query, macro, or any VBA code. When users see those levels of structures, you have lost all control, period. Showing only forms or reports (forms in FORM view, reports in REPORT or PREVIEW view) means YOU can restrict which data elements they see, which PARTS of each table - both with respect to which fields AND with respect to which records.

Finally, your life will be made simpler if you understand "Database Normalization" fairly well. Don't worry, it's not THAT long a topic. But I'll give you a tip or two there, too. You can search this forum for "Normalization" because this IS a database forum. But if you do a general web search, you should search for "Database Normalization" because other disciplines (math, chemistry, psychology, international diplomacy) also use the word "Normalization." IF you use the general web, start with articles originating from .EDU domains - not because they do a better job, but because they are less likely to distract you with ads and are less likely to be discussing a proprietary product that you don't have anyway. But once you are comfortable with the basics, .COM domains will have good articles, too. Just sometimes more cluttered.
Thanks for your instructive message on searching access forums.
 

bob fitz

AWF VIP
Local time
Today, 07:48
Joined
May 23, 2011
Messages
4,717
Bob
As a novice to Access, it is not clear to me how better to tag this question - being brief and to the point. In my opinion there is a general problem with user forums on this front, such that searching the forum is ineffective. So yes, what is the best way to put the question so that others can make use of it? Why don't you make a recommendation rather than make a comment that is perhaps as useless as my title. regards
Well as it transpires, your title was indeed “useless”. My comment, however, was made through a lack of understanding. A need for clarity. Perhaps you would have been less offended if I had phrased it as a question. Perhaps something like, “How does the text of your post relate to the title that you used?”. Unfortunately, I did not.

Fortunately, for you, there are other longstanding members here that are clearly more gifted than I am, since they needed no explanation of your nonsense. Some of them will even tolerate a degree of rudeness.

It is also fortunate for you that this forum gives members the ability to ignore posts from other particular members. As you seem to be so easily upset by my attempt to help in this this thread and I wouldn’t wish for it to happen again, perhaps you would be wise to add me to your “ignore” list.

Perhaps I shall reciprocate. Good luck with your project.
 
Last edited:

Users who are viewing this thread

Top Bottom