Junction table and concatenation

[M.Ursa]

Hi y'all!

I'm trying to set up a database of historical records. These records have specific designations I can't change, so bear with me if this sounds messy; there's really not much I can do about these designations.

Some notes about the data:

• Each record has a category (A, B, or C) and a number associated with it. The numbers and categories can repeat, but each combination is unique (i.e., you can have A1 and B1, but not two records designated A1).
• Between the three categories, there's ~90,000 records, and it's all rather messy. As such, I'm trying very hard to minimize the opportunities for entry error.
• The records sometimes refer back to any number of others from any of the three categories. Each record could be referenced multiple times.

Each record needs to have a detailed view, which I'm doing using a form. Within that form, I'd like to have a subform that contains a list of referenced records, and have the entries on the subform actually link back to the record in question, so that someone using the database and viewing the details of a specific record can double click on a reference and have its details open in a popup (I've already done this part in another area of the database). I'd also like to make it relatively straightforward for the person on the data entry side to create that link.

My first thought had been to try to have a junction table that takes the primary key, category, and number from the main table, concatenates the category and number together, and then have the subform be linked to that table. However, I have since discovered that there doesn't seem to be a good way to feed concatenated values back into a table, and I haven't found any workable alternative solutions.

Is there something I'm missing? Is there just no good way to do this?

Thanks!

M

 

[plog]

You keep mentioning "record" which is one row in one table, but you also keep using the term "referencing" which implies a relationship between 2 tables. You never give an indication of what your second table is composed of or how its used. In fact, it seems as you only have 1 table and somehow the records look back into the same table to "reference" other records.

Can you be less generic? Perhaps sample data? My suggestion is to give a 1 paragraph overview of what your organization does. Then give a 1 paragraph overview of what you hope this database will help you acomplish.

 

[bob fitz]

The records sometimes refer back to any number of others from any of the three categories. Each record could be referenced multiple times

How is this done

 

[M.Ursa]

You keep mentioning "record" which is one row in one table, but you also keep using the term "referencing" which implies a relationship between 2 tables. You never give an indication of what your second table is composed of or how its used. In fact, it seems as you only have 1 table and somehow the records look back into the same table to "reference" other records.

Can you be less generic? Perhaps sample data? My suggestion is to give a 1 paragraph overview of what your organization does. Then give a 1 paragraph overview of what you hope this database will help you acomplish.

Sorry about that, I was using record both in the database sense and in a more generic sense! I'll try to give more detail, though it's hard for me to figure out how to summarize it all. The two main purposes of this database are 1) looking up specific historical records, which are all summaries of committee meetings, broken down by topic; and 2) tracking those topics over time.

In any given meeting, a committee could discuss a wide range of topics, from R&D to purchases that needed to be made to conversations had with a third party. Each topic of discussion at a meeting was given a numerical designation. This is what I meant by "category" and "number" earlier; "category" referred to one of three committees, and "number" referred to numerical designation given to a particular discussion, like so:

Committee	Entry Number	Date	Title
A	200	01/01/2020	Finalized Telescope Body
A	201	01/01/2020	Replacing Office Printers
A	202	01/01/2020	Discarding Files

While a lot of this data doesn't need to refer to anything else (like the last two rows), sometimes it's useful for tracking development over time, like so:

Committee	Entry Number	Date	Title	Refer To
A	200	01/01/2020	Finalized Telescope Body	A150, B150, C40
A	150	10/01/2019	Telescope Body Development	B150, C40
B	150	06/01/2019	New Telescope Lens	C40
C	40	02/01/2019	Designing a New Telescope

This is what I mean by records referencing each other; I'm suppose I'm essentially talking about citations. To use the example above, if the user of the database is looking at the form for the details of entry A200, it could be useful to see what other steps were taken in the R&D of the telescope.

On the detailed form view of an entry, I'd like to have the ability to double click on an item in the "Refer To" subform and have it open the detailed form of that entry in a popup window. From a data entry perspective, I'd like making that link happen be as simple as possible. I'd rather there be more work for me on the back end than make a frustrating user experience; I've had to work in a database that didn't function in an intuitive way (or sometimes at all), and it's no fun.

Hopefully that's a bit clearer!

M

 

[plog]

Forms have nothing to do with table structure, so put away any thoughts on forms for a bit. Get your data structured properly then fight with them.

With the data you have given thus far, you need 2 tables: one with fields for Comittee, Entry Number, Date (poor field name) and Title. Then another table to hold the ReferTo data.

The way you have shown the example data it seems that one record is a parent to another. That is, B150 refers to C40, but C40 does not refer to B150. Is that correct, or is the referencing equal? Should C40 contain a reference to B150 that is equal to the rference from B150 to C40?