Having multiple Master/Chield Fields linked in one overview

[Lord-Vivec]

Dear all,

I have been building my first ever database which is basically a case tracking database to be used by court monitors. It's a redesign of the "Issues" database. The "Contact Details" form in this database has the option to see the "Assigned Issues" sub form, which I remodelled so it shows the cases the Judges/Prosecutors/Defendants/Monitors (basically four different versions of the original "Contact Details" for) have been assigned to. This is working fine if only one Judge/Prosecutor/Defendant would be assigned per case.

The problem I am facing is that the original database did not foresee the possibility of having more than one individual working on an issue, whereas the cases in this database of course feature multiple judges and prosecutors and defendants. In the case table I have used a structure where you can pick Judge1, Judge2, Judge3, Defendant1, Defendant2 per case, all linked to their own tables. In the form however, I don't want to use separate tabs for the Judge1, Judge2, Judge3 input; when a Judge's "Contact Details" form, "Assigned Issues" tab is opened, it should show all cases assigned to this judge, regardless of whether the entry is in the cases table's Judge1, Judge2, or Judge3 field. I however cannot link the Master Field "ID" to the multiple Child Fields "Judge1, Judge2, Judge3," etc, and the only work-around I can think of is to create a different tab for each Child Field. See a screenshot of the Issues DB attached for illustration. Anyone knows a work-around for this? Thanks!

[June7]

Never more than 3 judges or 2 defendants?

Perhaps use VBA to set the Child Links property.

[Lord-Vivec]

Never more than 3 judges or 2 defendants?

Perhaps use VBA to set the Child Links property.

Up to 9 defendants and 6 different judges. I could bypass this restriction through VBA?

[orange]

Review the info at some of these links. Work through 1 or 2 of the tutorials.

Since this is your first database, get familiar with some concepts entities/relationships/normalization...
Write a description of the business you are trying to support with this database --it will help you clarify your thoughts.
Build a model with pencil and paper; create some sample data and scenarios, then test your model.
Reconcile every anomaly; adjust whatever is causing the issue and retest.
Bounce your model and scenarios with colleagues to ensure something hasn't been overlooked.
Once the model is working, you have a blueprint for your database.

Too many people think Access is going to design and build the database--- it doesn't work like that.

Think about these:
- you're going to build a new shopping centre --do you start excavating and constructing, or do you get the facts--building codes, permits, artists concept of proposed building.... plans, tasks...
-you're going to build a new airplane --(797)-- do you start by welding/riveting/gluing some physical aluminum pieces together, or do you do the analysis, market study, feasibility, design, review and accept a design, update a build schedule, get approval and resources.....


Building a database to support a business is not a whole lot different. It's a project and could be critical to the viability of the business. It isn't something you build by trial and error.

Good luck.

[June7]

Bing: Access vba master child link
https://stackoverflow.com/questions/...form-in-access

Really should reconsider the db structure. Someday you will run into a case having more judges and/or defendants than you have fields for.

[Lord-Vivec]

Thank you for the helpful links and the tutorials! Will certainly look into restructuring; the thing is that there's a tight deadline for an alpha version, so I will probably release it with limited functionality and then see whether its manageable to use junction tables instead for the next versions. It's a learning process so really appreciate the links!