Forms & multiple tables

  • Thread starter Thread starter Wednesday
  • Start date Start date
W

Wednesday

Newbie question here. I am building a document tracking database wit
the following tables

DocumentPKe
DocumentTitl
DocumentNumbe
DocumentDescriptio
AuthorPKe
OwnerPKe

VersionNumberPKe
VersionNumbe
VersionDat
DocumentPKe

DocumentStatusPKe
DocumentStatu
StatusPerso
DocumentPKe

AuthorPKe
AuthorFirstNam
AuthorLastNam
AuthorCompan

OwnerPKe
OwnerFirstNam
OwnerLastNam
OwnerCompan

Can someone please advise me how to set up form(s) for data entry?
Thanks
 
Wednesday,

I would guess you need:

1. A standalone form for entry/editing of records into the Authors table.
2. A standalone form for entry/editing of records into the Owners table.
3. A form for entry/editing of records into the Documents table, in which:
- a combobox bound to the AuthorPKey field, the Row Source of which is
the Authors table
- a combobox bound to the OwnerPKey field, the Row Source of which is
the Owners table
- a subform based on the Versions table

I don't understand the DocumentStatus table.

What is DocumentNumber? Does this uniquely identify each document in
the Documents table? If so, why do you need DocumentPKey?
 
Thank you Steve. I'm revisiting Access after a long time away and a
finding that I remember very little--or maybe that I never had a goo
grasp to begin with.

So no way to do this all linked together somehow, huh? I was hopin
to be able to enter the info for each document all in one go. Sorr
I didn't explain the DocumentStatus table. It's to show what for
the doc is in, whether being revised, being reviewed, published o
archived, as well as who is working on it if it's being revised o
reviewed. The document number is not unique in that versions are no
being overwritten, so there may be in the database doc A1234 versio
1.0 archived, doc A1234 version 1.1 published and doc A1234 versio
1.2 in review
 
Wednesday,
So no way to do this all linked together somehow, huh? I was hoping
to be able to enter the info for each document all in one go.
Click to expand...

Sorry, I don't understand. Why can't you enter the info for each
document all in one go? My suggestion was to use a Documents form, with
a Versions subform. That's all in one go. You apparently want some
sort of "master listing" of Authors and Owners, which obviously gets set
up independently, but the only purpose of this really is to facilitate
the entry of the Author and Owner of the documents... they are just
ancillary lookup data, right?
Sorry
I didn't explain the DocumentStatus table. It's to show what form
the doc is in, whether being revised, being reviewed, published or
archived, as well as who is working on it if it's being revised or
reviewed.
Click to expand...

Ok. Well in that case, I can't see how this can work with the design
you have. Maybe I'm wrong, but it seems to me that the Status of the
Document is in fact the Status of the most recent Version... would that
be right? Therefore you need a Status field in the Versions table.
The document number is not unique in that versions are not
being overwritten, so there may be in the database doc A1234 version
1.0 archived, doc A1234 version 1.1 published and doc A1234 version
1.2 in review.
Click to expand...

So the Document Number *is* unique. In this case, A1234. Isn't this
what goes in the DocumentNumber field in the Documents table? And the
1.0, 1.1, 1.2 etc is what goes in the VersionNumber field in the
Versions table? Therefore, I imagine you don't need a DocumentPKey
field, whatever it is, in the Documents table... the DocumentNumber
field should be designated as the primary key field in this table, and
used as the linking field to the related table (i.e. Versions). The
Documents table lists each document, and then there is a one-to-many
relationship with the Versions table, right? So the Versions table
lists each version of the document, and there can be more than one
version for each document.
 
Back
Top