Selectable list (?)

[Guest]

Yes, I am also new to this discussion group.
I have a database of Job Information, keyed by Job Number.
One function I've been requested to create in the database has to do
with documents to be delivered to the Customer. Basically, there is a list
of about 30 different documents. The users would like to be able to select
certain items from the list, depending on whether they will need to send this
to the client. Then, they will need to input the date on which each document
was sent (b/c they will be sent at different times).
They would then like to print out this information on a report, which
should only display the documents selected, and the dates each was sent.
This is getting a little bit beyond my basic Access skills. The list
of documents is not really organizable in any way- each job could have a
unique selection of documents to be sent.
The easiest and most crude approach would be to make all these items
as independent fields: document name, check-box, and date sent. Then, I
guess a query could be made for the report to display only those items that
were selected. However, this seems pretty cumbersome.
Any other ideas or refinements?
Thanks for the help,

 

[John Vinson]

Yes, I am also new to this discussion group.
I have a database of Job Information, keyed by Job Number.
One function I've been requested to create in the database has to do
with documents to be delivered to the Customer. Basically, there is a list
of about 30 different documents. The users would like to be able to select
certain items from the list, depending on whether they will need to send this
to the client. Then, they will need to input the date on which each document
was sent (b/c they will be sent at different times).
They would then like to print out this information on a report, which
should only display the documents selected, and the dates each was sent.
This is getting a little bit beyond my basic Access skills. The list
of documents is not really organizable in any way- each job could have a
unique selection of documents to be sent.
The easiest and most crude approach would be to make all these items
as independent fields: document name, check-box, and date sent. Then, I
guess a query could be made for the report to display only those items that
were selected. However, this seems pretty cumbersome.
Any other ideas or refinements?
Thanks for the help,

This sounds like a very classic case of a many to many relationship:
each Job has multiple Documents, and each (type of) Document may be
needed for many jobs.

The proper technique for this is to add a third table - JobDocuments.
This would have (at least) three fields - the Job Number as a link to
the Jobs table, the DocumentID, and the date on which that document
was sent in connection with that job.

You'ld simply use a Subform on the Jobs table; in the subform there
would be a combo box to allow selection of the document, and a textbox
for the date (with perhaps a default value of Date() so that you can
simply select the document on the date that it's sent).

Your Report would be based on a query joining all three tables - the
job information from the Job Information table, the document title
from the Documents table, and the date sent from the JobDocuments
table.

John W. Vinson[MVP]
John W. Vinson[MVP]