How to build a relational table in Access that will allow me to manage documents and publishers

[eabourland]

Hello. May I ask some help, please? I am trying to build a database in MS Access that allows me to keep track of hundreds of documents and hundreds of publishers.

I've been struggling with this problem for a while.

So far, in Microsoft Access, I have two tables:



tableName: publishers
columns:
publisherID (primary key)
publicationTitle
coverLetter

tableName: documents
columns:
documentID (primary key)
documentTitle
documentNotes
publisherID (foreign key from table publishers)

* A document may have only one publisher! But a publisher may have many documents. *

1. How can I create a form that will let me indicate when a Publisher has accepted, or rejected, a Document?

2. Is there a way to display data, so that I can see which documents have been accepted, and which rejected?

I hope I am making sense, and not bothering anyone with my simple question. Thank you for your time.

Eric

[June7]

If there is no publisherID in Documents then there is no publisher (rejected?). If you want to track submittal status to one and only one publisher, have date fields for Submitted and Decision in Documents - if you want to account for the time between events - as well as a field for pending/rejected/accepted status.
If this is to track submittals and rejections, could same document be submitted to multiple publishers (until one accepts or you give up)? If you want to document this history, would mean a many-to-many relationship and require a third table.

[CJ_London]

1. You need to add two fields to your documents table - a status field (accepted/rejected - leave as null until decided) and a date field
2. Use a form to list your documents and filter on the status field

[eabourland]

Dear CJ and June, these are great ideas. I am working on this! I really appreciate you. Eric

[eabourland]

Hi, June, thank you so much for your thoughtful note.

Replies inline:

>>>>If this is to track submittals and rejections, could same document be submitted to multiple publishers (until one accepts or you give up)?

Actually, I never submit a document in multiple, or simultaneous, submissions. I wish to be very scrupulous about this -- it's a point of honor. =) It's one reason I am learning to build this database.

>>>>If you want to document this history, would mean a many-to-many relationship and require a third table.
But I also do want to have the document history. Which publications received the document, and when? So I am thinking I do need a third table. I am learning a lot. Thank you so much. I'll send another update soon.

Gratefully, Eric

[eabourland]

Dear CJ and June,

Here's an update. My two tables, so far:

tableName: publishers
columns:
publisherID (primary key)
publicationTitle
coverLetter

tableName: documents
columns:
documentID (primary key)
documentTitle
documentNotes
publisherID (foreign key from table publishers)
documentStatus
documentDate

I'm not quite sure what to do next.

I think my goals are:

1) keep track of publishers
2) keep track of documents (these are essays, a few poems and stories, research notes, proposals, etc.)
3) keep essential track of which documents have been sent to which publishers (I think this might be the complicated part, and I don't want to make this into a HUGE project that devours weeks)

Thank you again for your kind and expert replies.

Eric

[June7]

I am not suggesting simultaneous submission. Once a publisher rejects a submittal, certainly you are free to try another?

What you do next is build forms and reports.

For a start, a form for documents with a combobox to select publisher.

Have you studied an introductory tutorial on Access?

[eabourland]

Dear June,

Thank you! I really appreciate the reply. I have spent a lot of today on the Microsoft Access support site. I am older, and have been out of the workforce for a while. Some of this database stuff I learned in the 1990s, but I've forgotten now. =) I really appreciate your kind and thoughtful reply.

I have been reading up on Forms, and I will also read up on Reports. Thank you so much. I think I understand where I need to look now.

Eric

[June7]

You could also do a form/subform arrangement. Main form bound to Publishers and subform bound to Documents.

[orange]

Eric,

Step back and describe what happens when you want to track a document, then a publisher etc. Just going through the process to tell us in simple terms will benefit you and readers regarding your set up, intentions and some ideas on a course of action. Getting your tables and relationships established and vetted with some sample, but realistic, data will help.

We've all been in your situation, So you should get meaningful responses/experiences.

Will you have Notes about Publisher(s) as well as Document(s)?

[eabourland]

Dear orange, thank you for the kind words. That's great advice and I am listening. I have stepped back and am trying to "see" the task.

>>>Will you have Notes about Publisher(s) as well as Document(s)?

I think so! I'm reading up on relationships. I used to do this stuff thirty years ago -- it seems a long time, now. I am making progress. I am really grateful for the guidance I have gotten here. I will post again as I make progress.

I hope you're great. Thank you again.

Eric

[Welshgasman]

30 years is a long time.

[mike60smart]

Hi Eric
Are you able to upload a copy of your database?

[eabourland]

Friends, I'm making slow progress. I have been doing a lot of reading about relationships: https://support.microsoft.com/en-us/...2-574ee271500a

I am working on building and relating all of the tables I will need, then building tools to update those tables (forms), and then (I think?) other tools to view the data the tables offer.

Right now I am working on relationships, of every kind. =) Thank you again for your kind help; I feel well supported and guided, and I'm most grateful. I'll write again when I build something functional. =)

All the best,

Eric

[Welshgasman]

And with a picture being worth a thousand words. :-)
https://www.youtube.com/results?sear...+relationships