Reference table that self populates forms (1 Viewer)

rondon

Member
Local time
Tomorrow, 09:26
Joined
May 16, 2020
Messages
53
I would like make a reference table tblRefTable (for maintenance changes) that identifies Sectors and the Agencies assigned to them. The table will have a 1 to many relationship:
1 Sector may have various Agencies.

I would then like to be able to use tblRefTable to auto populate a Sector field based on the Agency selected i.e.

When the user enters a Agency in the form, it refers back to tblRefTable and selects the associated Sector, and uses this value to auto populates the Sector field in the same record row of the data form.

For example tblRefTable =

Sector "Red" = Agencies "A" "B" "C"
Sector Blue = Agencies "D" "E"
Sector Green = Agencies "F" "G"

I understand I could hard code this, but Agencies are often added to Sectors or deleted from them. I thought the logic of creating the reference table would prevent updating any coding if there was a change. My thoughts are, that I would then only need to code on the Agency after update event. (I will need assistance with this code) And all maintenance (changes) between Sectors and Agency would be controlled through the tblRefTable.

Is this the best way to approach this.

Thanks Ron
 

June7

AWF VIP
Local time
Today, 14:26
Joined
Mar 9, 2014
Messages
5,423
Each agency can only be in one Sector?

Don't really need to save Sector. Sector can always be retrieved in query joining tables.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:26
Joined
Feb 19, 2013
Messages
16,553
suggest you need your table to look like

tblAgencies
Agency...Sector
A...............Red
D...............Blue
B...............Red
etc

if you have a lot of sectors - perhaps put the Sector descriptions in a separate table

tblAgencies
Agency...SectorFK
A...............1
D...............2
B................1

tblSectors
SectorPK..SectorDesc
1..................Red
2..................Blue

all maintenance (changes) between Sectors and Agency would be controlled through the tblRefTable.
Can't say whether this is the best way to approach the problem without having a full understanding of your business process - so when you come to ask about it, describe the process and provide a screenshot of your tables and relationships
 

rondon

Member
Local time
Tomorrow, 09:26
Joined
May 16, 2020
Messages
53
suggest you need your table to look like

tblAgencies
Agency...Sector
A...............Red
D...............Blue
B...............Red
etc

if you have a lot of sectors - perhaps put the Sector descriptions in a separate table

tblAgencies
Agency...SectorFK
A...............1
D...............2
B................1

tblSectors
SectorPK..SectorDesc
1..................Red
2..................Blue

Can't say whether this is the best way to approach the problem without having a full understanding of your business process - so when you come to ask about it, describe the process and provide a screenshot of your tables and relationships

Please find a cut down version of my DB
Cut down version of DB.PNG
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:26
Joined
Feb 19, 2013
Messages
16,553
Please explain your business process - what it does and how this part of the process fits in.

what does 'once an agency is entered' mean? The record is created? an agency is assigned to a report? something else?

what does 'self populate' mean? What rules are applied to say what sector should be applied to an agency.

what is an agency?

what is a sector?
 

rondon

Member
Local time
Tomorrow, 09:26
Joined
May 16, 2020
Messages
53
Please explain your business process - what it does and how this part of the process fits in.

what does 'once an agency is entered' mean? The record is created? an agency is assigned to a report? something else?

what does 'self populate' mean? What rules are applied to say what sector should be applied to an agency.

what is an agency?

what is a sector?


I am not sure I can make it much clearer, an agency is a subgroup of a sector. I would like to create a table that is maintainable by the user, where the user can assign different agency to sectors.

A audit is undertaken this creates an Audit ReportID for example Report 2020_1

Within the audit 2020_1 recommendations based on the report are made, there may be many recommendation for each report, hence the 1 to many relationship between the ReportID and RecID

The audit may be across different Agencies that belong to Sectors. When an agency is entered I would like the DB to auto populate the Sector field based on the association in the maintainable reference table, this prevents hard coding every time a new agency is added. I would like to code in the after update event of the AgencyName field, that identifies the string in AgencyName - then looks at the reference table and identifies the Sector and populates that value in the Sector field.

If we look at the form above the field AgencyName is selected from, a dropdown box, I would then like the Sector field in the form to auto populate with the Sector that agency belongs to. Currently the user selects the Sector from a dropdown box.

For example if Sector Red contains Agency "a" "b" if either a or b were selected by the user in the AgencyName field then the field Sector would populate with Red

I hope this helps
Thanks Ron
 

rondon

Member
Local time
Tomorrow, 09:26
Joined
May 16, 2020
Messages
53
Each agency can only be in one Sector?

Don't really need to save Sector. Sector can always be retrieved in query joining tables.

Can you please explain this to me a little more
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:26
Joined
Feb 19, 2013
Messages
16,553
what is an agency? detective? employment?
what is a sector? geographical area? industry?

For example if Sector Red contains Agency "a" "b" if either a or b were selected by the user in the AgencyName field then the field Sector would populate with Red
why not just populate the agency record with the sector, then you can look it up any time you need
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:26
Joined
Feb 28, 2001
Messages
27,001
Watch out because you might need special care. Your dependency arrows are mixed with respect to tblFunction. If that tblAgencyAssigned is going to contribute, you will have tons of fun with doing that in one query. Might need a layered query to handle how you want to manage that relationship when dealing with other tables.

As to "auto populate" I have to presume that the things being populated are the not primary bindings on the form. The place to do that is to put some code in the OnCurrent event and let that pick up stuff from the tables with your descriptive material.
 

June7

AWF VIP
Local time
Today, 14:26
Joined
Mar 9, 2014
Messages
5,423
If you want a lookup table for Agencies that associates each agency with a sector, then do that. Save AgencyID into data table (tblAgencyAssigned) and retrieve Sector by query joining on agency identifier key fields. Again, no need to duplicate Sector to another table.

Why do you have RecID in tblAgencyAssigned as well as tblJunction? Should tblAgencyAssigned be the junction table?
 

Users who are viewing this thread

Top Bottom