Return fields from multiple records in a single textbox

[TonyD1016]

Good afternoon everyone,

I'm working on a new project that tracks court letter updates.

I have two tables currently, one that holds client information (Name, Address, Attorney contact info), and a second that holds the court information (Docket number, Judge, court date, etc.)

I've set up the Court info table to have each docket number as a new record, and have the ClientID as a foreign key to the client table in a one to many relatonship.
My query returns the most recent information for each client by looking at the court date, and also indexes the judges name to display a client twice if they have a pending case with more than one judge.

My question is, given the two indexes of ClientID and Judge, is it possible to display all dockets numbers in the table that match those two criteria in one textbox in a report? For instance if there are six docket numbers in a table for Client A-- four for JudgeA and two for Judge B-- can I have the report display:

ClientA | JudgeA | Docket 1,2,3,4
ClientA | JudgeB | Docket 1,2

 

[Micron]

Bad idea to use multi value fields (e.g. on that contains 1,2,3,4) unless you really have to, and that is very seldom the case. Access isn't Excel and you should forget whatever you know about Excel when it comes to designing relational db's or you will often struggle trying to get info in and out. Yes, I know M$oft designed Access to allow them, but IMO that is all about selling a product by making it as easy as possible to get started with it. Every entity should have its own table but without knowing the process to be supported, it's not possible to give focused answers about much of it. So a direct answer would be 'yes you can but no, you should not'. You should have 6 records, one for each docket, and based on your post, docket would be the parent entity. Again, that depends on what the db is supposed to support.

 

[JonXL]

You will need to use something like this concatrelated custom function.

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

I've used it many times. It works well but can be hard on performance. If the performance gets too rough, we'll maybe have to think of something else.

 

[jackd]

Further to other comments, I think you may want to revisit your table(s) and relationships. Depending on your requirements yo may need more tables, but you know your requirement and environment better than any reader.
ConcatRelated may be an option for responding to questions/queries, but suggest you normalize your tables to support the "business and processes".

 

[TonyD1016]

Thank you all for your replies everyone.

I was unaware that Access did not have a simple way to associate a specific field in a table among different records

The specific need at hand is to have a way of displaying the contact information for each individual ClientID in one report alongside all of the docket numbers associated with that ID in the related to that client.

Is this something that just cannot be done?

If I can ask, how might you all approach this issue with regards to table structure?

 

[Micron]

I was unaware that Access did not have a simple way to associate a specific field in a table among different records

As I read that, it most certainly does. Your OP implies that you want to store what should be in several records in one record. You can do that, it's just not advisable. Seems that in the end you want a report so you'd want to build a query that gives you the records you want and base the report on that. Don't worry about grouping and sorting in the query as a report has its own methods and will over-ride what you do in the query. As for table structure, research "database normalization" - tons of articles, white papers and videos on the subject. Seasoned developers often start out with pencil and paper, believe it or not, because it's easier to visualize before creating tables. Consider posting a pic of your relationships window for feedback, or start a new thread for feedback where you explain the business and your proposed design. I was just going to post a link on normalization to get you started on it, but decided to throw in everything in case you'd benefit from the major concepts that you should be aware of.

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Important for success:
Naming conventions - General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

 

[TonyD1016]

Thank you for the links Micron. I had read through most of those articles (or very similar ones), and I believe I have set things up according to those practices.
This is the relationship as it currently stands:

The query I constructed pulls the docket numbers from the Court info table based on the Client ID and the Assigned Judge.
I would have liked to have the Docket number as a Primary Key but unfortunately since different district and state level courts use their own systems for assigning docket numbers it is theoretically possible for two different cases from two different courts to end up with the same docket number. Hence the need to sort things by client and judge.

So to my original post: if I have a query that sorts each client alphabetically and pulls all the docket numbers that match the Client ID as individual records, and I want to use that as the source for a report, what method could I use to display each of those docket number in a single textbox alongside their respective client information?

 

[Micron]

Looks close and maybe good enough for your needs. If I may, I'll first post some suggestions/observations:

You have repeating fields (e.g. State1 and State2) and that means normalization isn't complete. Not sure how a client can live in 2 states at the same time but if there's a need for that, it would be better to break that out. Perhaps have tblAddresses where ClientID is a fk field. One thing I learned at AccessForums.net was to use pk and fk suffixes in one manner or another, so that you always know which field name represents fk vs pk fields. I would use ClientIDpk and ClientIDfk f'rinstance.

Also, joined pk and fk fields usually should have the same name (the suffix notwithstanding) whereas you've joined JudgeID to AssignedJudge. The name tblCourtInfo suggests information about the courthouse, which suggests that CourntInfo belongs in there, but the fields suggest it is about a trial that takes place. In either case I don't think RecordID tells you anything about the attribute (field) of the entity (table).

I would have liked to have the Docket number as a Primary Key but unfortunately since different district and state level courts use their own systems for assigning docket numbers it is theoretically possible for two different cases from two different courts to end up with the same docket number.

That suggests you're using real data as primary and foreign key values. If that's the case you're better off not doing that.It is often done, but it's not best practice. Use an autonumber type for pk's and that number becomes the fk field value. Then it won't matter if 10 courthouses have the same docket number.

It would seem to me that the answer to your last question was given in post 3? I'm still not understanding why you want to do that though, unless it's just to condense information within the report. Perhaps if you step away from using real data as PK values (assuming that you are) the need to concatenate will go away?

 

[TonyD1016]

RecordID is an autonumber. It's just the name I use whenever I have that as the primary key. The CourtInfo table is related to the specific client court information (trial dates and notes), but it made more sense at the time to attach the specific court to the presiding judge. AssignedJudge is an oversight from my first pass before I broke the judge information out into it's own table. I just haven't gone back and corrected that field name yet.

Yes, the goal is to condense the information in the report. If the best (or only) way to do that is with a custom function as was described in #3 I can mark that as the correct answer and begin exploring it.

 

[Micron]

That's the link I would have given you as well, but was trying to talk you out of it as a method of data storage. I didn't realize this was just for display.