Handling relationships between Documents and references

[Denise2020]

I had a hard time even coming up with a succint title to this question. I am creating a database to act as a searchable register of reports. These reports come yearly, about six per month, since about 15 years back. I am trying to make the database as searchable as possible to enable finding relevant reports as easy as possible.

Reports have their own individual numbers (ex. 89/2023) and often contain references within to previous reports (ex. ref. 31/2022). Sometimes, multiple references (ex. Ref. 31/2022, 16/2022, 88/2021).

Some fields on my table are ReportTitle, ItemNo (report number), and RefNo (the previous reports referenced within).

I cannot for the life of me wrap my head around a good solution for the references. I do not want a text box, but am unsure how to implement adding references to previous ItemNo's (ie. Report numbers). I am quite sure there is an obvious solution I am missing but as I am not trained in Access and have only taught myself, I am not seeing it. Any suggestions? Thank you in advance!

 

[MajP]

You need a table of child references which is a many to many junction table. The question is do you want to add the references not in your database as record in your report table or do they go into a new table. I will assume references do not go into your existing table but they could be in your existing table. You will have to decide how you want to handle that.

if you do not add references to your report table you need to add to a seperate reference table
tblReferences
-referenceID
-referenceName
-other reference field

Then you need a junction table
tblReports_References
-Report_ReferenceID
-Report_FK 'fk to report table
-ReferencedReport_FK ' fk to reference table


Then add a subform to your report table to relate a report to its many references. You need a button on that report to add new references to the reference table

See design here

Search (if not found: add) in a table on a subform

Hi! In short: I've got a database where I put in details of people who followed courses and with some macro's and links to Word, I can send them their certificate automaticly by mail. To explain the issue I have, I'll give you the necessary details of the "impacted" details: Three tables...

www.access-programmers.co.uk

 

[MajP]

FYI,
If a reference is always one of your previous reports and it makes sense to enter a reference into the reports table the junction table is still needed. The tblReferences goes away. The ReferencedReport_FK is a foreign key to the report table. This is called a self referencing table where both the Parent and child keys come from the same table.

 

[Denise2020]

I have been so busy with other work but I want to thank you for your reply! I am working on it and it is not done yet but this got me on the path and I am much closer. Thank you so much!