Solved Best ERD Design Approach (1 Viewer)

SHANEMAC51

Active member
Local time
Today, 17:27
Joined
Jan 28, 2022
Messages
294
Thanks for replying
Can you please upload your DB so that I can understand your explanation?
I have it with a (simple) code for navigation, I didn't bother with macros
 

Attachments

  • SARA56mm.accdb
    724 KB · Views: 36

SHANEMAC51

Active member
Local time
Today, 17:27
Joined
Jan 28, 2022
Messages
294
Thanks for replying
Can you please upload your DB so that I can understand your explanation?
Code:
Option Compare Database
Option Explicit
Dim s1, s2, mtype As Long, mtypek As Long
Private Sub zcode_Click()
mtype = Nz(Me.zcode, 1)
s1 = "zkuda=0 or zkuda=" & mtype & " or zcode=" & mtype
Me.Filter = s1
Me.FilterOn = True
End Sub

Private Sub zkuda_Click()
mtypek = Nz(Me.zkuda, mtype)
s1 = "zcode=" & mtypek & " or zkuda=" & mtypek
Me.Filter = s1
Me.FilterOn = True
End Sub

Private Sub zName_Click()
Debug.Print Me.Name
DoCmd.OpenForm "0000m1", acNormal, , "zcode=" & Me.zcode
End Sub
 

SHANEMAC51

Active member
Local time
Today, 17:27
Joined
Jan 28, 2022
Messages
294
Спасибо!
и, конечно же, фильтрация/поиск по части значения или интервала (для дат) в любой комбинации полей фильтрации
T.[ztype], T.[zName], T.[zEmail], T.[zPhone], T.[zFax]
A.AssignStartDate, A.AssignEndDate, A.AssignextEndedDate, A.MainJob, A.AssignedJob
например
  • -секретари + 2022
  • -меры по защите данных
  • -неотвеченные/запланированные звонки
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:27
Joined
May 7, 2009
Messages
16,779
The database uploaded by @SHANEMAC51 still has Lookup fields in tables.
whether there is lookup field or not, that is not an issue.
the OP can't use VBA.
 

mike60smart

Registered User.
Local time
Today, 15:27
Joined
Aug 6, 2017
Messages
1,235
Sorry but I tend to disagree as Lookups at table level are not recommended.

What has a Lookup field got to do with VBA?
 

SHANEMAC51

Active member
Local time
Today, 17:27
Joined
Jan 28, 2022
Messages
294
The database uploaded by @SHANEMAC51 still has Lookup fields in tables.
in the example, I did not set out to remove substitutions or arrange everything with macros - my task was to show an approach to solving the problem through the subordination TREE, combining 5 tables into one + navigation through the levels forward and backward (this is served by clicking on the first two fields in the record)

to search for any of the 10 fields that the FILTER suggests, of course it is more convenient to write code, but I think that this can be solved with a macro
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:27
Joined
May 7, 2009
Messages
16,779
table level are not recommended.
who said, microsoft?
how long have you been using access?
if you have been using access for half of your existence,
there is no reason not to "grow up" with it.
 

mike60smart

Registered User.
Local time
Today, 15:27
Joined
Aug 6, 2017
Messages
1,235
Sorry Arnelgp but I am just a novice in developing database's but the majority of Guru's on the various Forums all tend to say that Lookups at table level is not recommended.
This site is an example
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:27
Joined
May 7, 2009
Messages
16,779
sorry too, but it is only a "recommendation"?
if you embrace their recommendation, fine.
it is not a Law or an ISO standard.
 

Sarah.M

Member
Local time
Today, 17:27
Joined
Oct 28, 2021
Messages
303
Sorry but I tend to disagree as Lookups at table level are not recommended.

What has a Lookup field got to do with VBA?
Hi, there is no problem with lookup fields as long as I know how to deal with it
In this thread my goal is making best ERD design approach
 

mike60smart

Registered User.
Local time
Today, 15:27
Joined
Aug 6, 2017
Messages
1,235
Hi
The form that opens allows you to navigate between each Management Name which then displays a list of all Departments associated

Does this help?
 

Attachments

  • 60.zip
    45.5 KB · Views: 32

Sarah.M

Member
Local time
Today, 17:27
Joined
Oct 28, 2021
Messages
303
Hi
The form that opens allows you to navigate between each Management Name which then displays a list of all Departments associated

Does this help?
I really like your Form, nested subforms, Colors and Macros,
but I am struggling with tblPositions
Do I need to associate the tblPosition with each table? or there is different way to do it?
for example I i link tblPosition with Section that is means GM should have a section but GM he is not related to any section, so that I link it directly to tblPostion
sample attached

1651965095268.png
 

Attachments

  • 61.accdb
    660 KB · Views: 32

Users who are viewing this thread

Top Bottom