X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Need Help With Database Design, Access 2013    
post Oct 10 2017, 10:00 AM

Posts: 5
Joined: 25-May 17

Hi everyone

So i need some help with this current project I'm working on. Before getting into the technical side of it, I'll briefly describe, in plain English, the nature/business of this project. Presently working for a local community college and the point of this database is for the educational placement department to monitor and track agency information (e.g. address, city, email, insuranceexpiry, etc.). Different agencies and subsidiaries (child component of main agency) have agreements with this school, so basically what users need to know are, which agencies are involved with the school based on program codes and seeing if their contract is going to expire soon via insurance and other date fields. So originally, I was given this main agency table (see fieldnames.png attachment) with various fields.

After doing some data splitting in Excel, I realized that some agency information with subisidaries is different because all the information after the subsidiary field is information about the subsidiary (programcodes/notes, address, city, etc) and not actually agency information, so technically all those fields should be N/A in this agency table for those specific records. I have separated these records into another table called tblSubsidiaryInformation. It also occurred to me that some records have more than one program code value (e.g. 2321, 2910), and I think this could potentially cause problems down the road, so I created a separate program codes table called tblProgramCodes and readjusted the main agency table so that each agency row only has one program code value. Afterwards I was also told that some program codes are not presently in the main agency table but will eventually be added so I think creating this table was a good idea. So just to make sure I'm on the right track in terms of database design.

So now I have three tables:
- An Agency can have 1, 0 or no subsidiaries
- Subsidiaries belongs to one agency
- Both agencies and subsidiaries can have 1, 0 or multiple program codes

Prior to reconstructing this database design, I had an agency lookup form where users could type in an agency and the datasheet would filter based on whatever was typed in. At first everything was fine because this search bar filtered and users could look at reports based on date fields. But once I began trying to filter reports by program codes (fields with more than one value) and encountering problems, I knew the underlying structure needed to be redesigned. Originally the relationships between these tables were not properly connected (e.g. tables being linked together by text and not IDs). So basically I need help making the connections between the tables. I was told that I need to make two junction tables:

AgencySubID (PK Autonumber)
AgencyID (FK)
SubsidiaryID (FK)

ProgramAgySubID (PK autonumber)
ProgramCode (FK)
AgySubID (FK)

I'm not sure how to go about this, I made a query in an attempt to link Agency with Program Codes (see second attachment). It looks right but how do I "use it"? I go, okay it's made....now what?

I hope I articulated myself properly. And any sort of feedback is much appreciated!

PS if it helps I can attach a dummy database to this thread.
Attached File(s)
Attached File  fieldnames.PNG ( 3.68K )Number of downloads: 6
Attached File  apc.PNG ( 14.92K )Number of downloads: 5
Go to the top of the page
post Oct 12 2017, 09:54 AM

UtterAccess VIP
Posts: 12,497
Joined: 6-June 05
From: Dunbar,Scotland


It would help if you attach the Db

Hope this helps?


Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
post Oct 12 2017, 12:25 PM

UtterAccess Editor
Posts: 18,128
Joined: 29-March 05
From: Wisconsin


Some of your terminology is still a little unclear, but I'll try to repeat back to you what you're saying, to make sure I understand.

It sounds like these Agencies and Subsidiaries are both insurance related. At times it seems like they're equals in that you might have one kind of insurance through one agency, but another kind of insurance with a subsidiary rather than it's parent agency. Is this right? Because this is a critical problem for modeling your data.

I'm unclear what a "program code" is in relation to insurance (if that's indeed what we're dealing with). You mentioned that "some records have more than one program code value". This may be something we need to work with, rather than try to fix as you've described. It sounds like one subsidiary can provide more than one kind of insurance, but without more information it's just a guess. This is certainly do-able with a database, if necessary. It sounds like it may be a many-to-many relationship.

If you could clarify these things, it would help to figure out how to model the tables.

Hope this helps,


(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    20th February 2018 - 08:39 PM