Help With Tables & Reports

[JVIDAL]

PLEASE HELP. I AM TRYING TO BUILD DATABASE TO TRACK DOCUMENTS ISSUED AND AFTER READING ABOUT NORMALIZATION I AM CONFUSED ON HOW TO DO IT.

I HAVE THE FOLLOWING:
JOB 1
DOCUMENT1 REV1 TRANSMITTAL1 DATE CUST
DOCUMENT1 REV2 TRANSMITTAL2 DATE CUST
DOCUMENT1 REV2 TRANSMITTAL3 DATE SHOP
DOCUMENT2 REV1 TRANSMITTAL1 DATE CUST
DOCUMENT2 REV2 TRANSMITTAL4 DATE CUST

JOB 2
DOCUMENT1 REV1 TRANSMITTAL1 DATE CUST
DOCUMENT1 REV2 TRANSMITTAL2 DATE CUST
DOCUMENT1 REV2 TRANSMITTAL3 DATE SHOP
DOCUMENT2 REV1 TRANSMITTAL1 DATE CUST
DOCUMENT2 REV2 TRANSMITTAL4 DATE CUST
AND SO ON...

I NEED TO SHOW FOR EACH DOCUMENT IN JOB, HOW MANY REVISIONS THERE WERE AND WHAT DATE WERE THEY ISSUED & TO WHOM.
CAN SOME ONE POINT ME IN RIGHT DIRECTION?

[KnooKie]

yes you did well reading about normalization as you will need to design your table structure first.

Looks like you need a JOBS table with jobID (autonumber & primary key(PK)) and a JobDescription fields, a
DOCUMENT table (as there are many documents in each job) with DocumentID (autonumber and PK), JobID(Long integer) and DocumentDescription fields.

See if what i have said makes sense with what you have read. Perhaps you can design the rest !?

Think one-to-many !!

e.g. one document has many revisions

[JVIDAL]

I came up with the following tables
tblProject
ProjectId (pk)
ProjectMgrId(fk)(table>tblProjectMgr)
CustomerId(fk) (table>tblCustomer)

tblDocument
DocumentID(pk)
ProjectId(fk)
CatagoryID(fk)(table>tblCatagory)
DocumentNo
ReferenceNo
Description
Sheets
Notes

tblRevision
RevId(pk)
DocumentId(fk)
RevisionNo

tblOutTransmittal
TransmittalID(pk)
TransmittalDate
IssuedTo
DocumentID(fk)<<< points to specific
RevId(fk) <<< document and revision

tblInTransmittal
InTransmittalID(pk)
ProjectID(fk)
date
Sender

tblInDetails
InDetailsId(pk)
InTransmittalID(fk)
DocumentID(fk)
RevId(fk)
Status
ActionTaken

is this how tables should look? i cant seem to link transmittal<->document & rev tables without having to type the transmittal info each time into subdatasheet. & how do i link so i dont have to type in document no in the tblInDetails ? is there a way of setting combo box or listbox to show documents transmitted to the person listed in Sender field of the Intransmittal table only w/ option to add if not on list?

[KnooKie]

Looks like you pretty much got the hang of it anyway.

Not sure what you mean with the subdatasheet bit exactly although i assume you have a subform. You need to link the parent and child properties of the subform i think.

For the combo box bit you will need to put a query together to show the fields that you want in your combo box. You will also need to add some code to the On Not In List event if you want to add items not in the list. Get the combobox showing what you want first.