One-to-one table with many records 2

[swtrader]

I use Access extensively and have become fairly good at accomplishing what I need done. This one stumps me, though.

I have a Customer table (tblCust)
and a Transactions table (tblTrans)with a one-to-many relationship.

Each transaction can have many different documents associated with it. I have a separate table (tblDocs) with fields such as DocName, Required? (yes/no), Received? (yes,no). A one-to-many relationship works great in selecting only applicable documents. However, and here's the rub, my management wants the entire list of 40 documents to pull up with each Transaction. Then, the Required docs can be checked and marked received as work continues.

Each transaction in tblTrans will have only one set of all records in the tblDocs associated with it. I cannot figure out how to get the complete list of all 40 records show up with each transaction.

I have tried setting a one-to-one relationship between tblTrans and tblDocs but the relationship defaults to one-to-many. (Do I have the primary key field in tblDocs set incorrectly?)

Suggestions?

Thanks.

Dan

 

[dhookom]

It is unclear what you want. You have multiple documents with each transaction yet you suggest trying to set up a one-to-one relationship. It's either one or the other.

You also state "list of all 40 records show up". "show up" where? A report, form, query,...?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[swtrader]

More thought on the subject......what I am really trying to do is to use a one-to-many relationship: 1Transaction-to-manyDocs with the Doc records static at about 40. SO, what I really want to do is to automatically create a list of the 40 individual documents for each new transaction (when the transaction is first entered). It seems this would be some kind of loop 'til done VB process but I have precious little VB coding experience.

Thanks.

swtrader

 

[dhookom]

This would probably be a cartesian append query based on two tables. Assuming you have tables:
tblTransactions
==========
TransID Primary Key
OtherFields
and
tblDocuments
==========
DocID Primary Key
OtherFields
and
tblTransDocs
==========
TransID Unique index with DocID
DocID

You want to add every possible document for every transaction
INSERT INTO tblTransDocs (TransID, DocID)
SELECT TransID, DocID
FROM tblTransactions, tblDocuments

It there are 20 transactions and 40 documents, you will end up with 800 records in tblTransDocs with no duplicates.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[willir]

This problem intrigued me.

(And Duane, I have not seen a cartesian solution in eons)

I am going to throw a spanner into the works.

Do you really need to link al documents to each transactions? Or do you really have a many-to-many relationship where a document can be associated to many transactions, and a transaction can have many documents ... AND the actual relationship is ONLY determined later when they are selected?

If I am right about this, then consider the following tweak to the design...

tblAssocDocs (associated documents)
DocID - foreign key to tblDoc.DocID
TransID - foreign key to tblTrans.TransID
RevDate - date field to capture when a doc was created / updated

The primary key would be DocID + TransID to prevent multiple records for the same thing.

You can also add to this table,
CustomerID - foreign key to tblCust.CustomerID
...if you want to track which documents were used for which transaction for which customer. Your primary key would then be DocID + TransID + CusomterID.

The revision date thing is just to track when a document was linked to the transaction or transaction + customer.

To display a list of all documents is easy. A simple contineous form perhaps, instead of a multi-select list box since you have 40 documents. Display this as a subform, say on the left side. We will call this subform, sbfrmAllDocs and would use tblDoc as the control source. The subform would have ateast the DocID and DocName where only DocName was visible.

Now, next to this subform that lists all documents, display another contineous form that displays all the applicable or "linked" documents for the transaction or transaction + customer. Display this form to the right of the subform that displays all documents. We will call this subform sbfrmAssocDocs. Oh yea, one more thing. This form would have both foreign keys, TransID and DocID, or the three foreign keys, TransID, DocID and CustomerID but only the DocID would be visible, and it would use a combo box with a row source of
SELECT DocID, DocName FROM tblDocs
where the DocID column would be hidden so now the user only sees the DocName value.

How it works...
Use the double click event proecure to on sbfrmAllDoc to "add" a record to tblAssocDocs. After adding the document, requery sbfrmAssocDocs subform to see the change.

You can then either use the double click on the sbfrmAllDoc to remove the record if it already exists, or use the double click on sbfrmAssocDocs to remove the document.

What the end user sees is to lists of documents. On the left, all available documents are shown. On the right, only the associated documents, records present in tblAssocDocs, are shown. They use a double click to add or remove the docuemnts.

This way, you will save on creating numerous unrequried records.

(But I still like your solution Duane)

Richard

 

[swtrader]

Willir -- I have to digest this but I really like the tweak...the side-by-side subforms which I have never thought of using before.

Thanks so much for your help.