Junction table and concatenation (1 Viewer)

[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

 

[Pat Hartman]

In a relational database, relatiohsips are how we link tables or even rows in a single table (self-referencing relationship). Normally, this is done by using what we call a foreign key (FK) A FK is a piece data that contains the primary key (PK) of a different record or a different table. One field relationships is the norm. However, you can create unique indexes that include up to 10 different fields. You would NOT concatenate the values into a string. So, in an employee table, each employee has a supervisor and since that supervisor is himself an employee, this is a self-referencing relationship.

tblEmployee:
EmployeeID (PK)
FirstName
LastName
SupervisorID (FK to tblEmployee.EmployeeID)

To display the name of the supervisor in a query, you would add tblEmployee twice and you would join the SupervisorID of the first instance of tblEmployee to the EmployeeID in the second instance which will automatically be named tblEmployee_1

If it takes four columns to uniquely define a record, open the table in design view and choose the indexes dialog. On the first fully blank line, start defining the index by giving it a name. Anything is fine, short is best and it must be unique in this table. The next column you pick the first of the fields. In the properties below, choose unique as the type of index. Move to the next line. Skip the index name column and in the field column, select the second field. Go to the next row, skip the index name column and select the third field. You can select up to 10 columns for an index. By leaving the index name column blank, you are telling Access that this row belongs to the index defined above.

Now when you join the two tables, instead of using a single join line from FK to PK, you have to use multiple join lines. One for each field in the index. This is seriously awkward which is way we almost always use an autonumber as the PK. Then we would create a unique index to enforce the business rules but using this preferred method, the second table would never contain the fields of the unique index, it would only need to contain the PK of the first table as a FK. That way, the join is always one FK to one PK rather than 10 FK fields to 10 PK fields.

 

[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

 

[Pat Hartman]

The problem is that the Refer To column is useless as it exists. If you want to use it to find related records, you are going to have to normalize the data. That means you are going to need to create a new table to hold the references.

What is the primary key of this table? Is it Committee + EntryNumber + date or is there a hidden autonumber?
Does the Refer To column already exist or is that what you are looking to create? Because that is not how the relationship needs to be established.

 

[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?