Multiple Tables linking

[tekspeek]

I'm trying to develop a database with three tables as follows:
Vendor
Vendor#, Vendor Name

Specification
Spec#, SpecName, Paragraph#

Results
Vendor#,Spec#, Paragraph#, Yes,No, Comments

The work is all done at the RESULTS table. To update I will select a Vendor,
a specification and a Paragraph number and will answer Yes or No and add
Comments.

I've tried to do this with a Querie but have not been able to figure it out.

Thanks in advance.

 

[John Vinson]

I'm trying to develop a database with three tables as follows:
Vendor
Vendor#, Vendor Name

Specification
Spec#, SpecName, Paragraph#

Results
Vendor#,Spec#, Paragraph#, Yes,No, Comments

The work is all done at the RESULTS table. To update I will select a Vendor,
a specification and a Paragraph number and will answer Yes or No and add
Comments.

I've tried to do this with a Querie but have not been able to figure it out.

Thanks in advance.

Use a Form.

Tables and table datasheets are of very limited utility, and
professional developers always provide a Form to interact with the
data.

I'd suggest using a Form based on the Vendor table, with a Subform
based on Results. I'd guess that you could use a Combo Box on this
subform based on the Specification table to let you select the
appropriate Spec#. If the Spec# is always tied to a corresponding
Paragraph# then it's redundant to store both in the Results table -
I'm not clear about your design in that regard! Also you should
certainly not have two fields named Yes and No - instead, use one
Yes/No field (which can be displayed as a checkbox or as a combo box
allowing the choices Yes or No).

It's also best not to use the # character in fieldnames: it's a date
delimiter and can become confusing.

John W. Vinson[MVP]

 

[tekspeek via AccessMonster.com]

Thanks for the response - after I posted and re-read my message I thought to
myself - that makes no sense at all - let me try again.

I have 4 tables
The first table holds Specification Names (SPECNAME)
The second table (SPECREQ) holds the paragraph requirements for the
specification. This table will be the many side to the SPECNAME table.

The third table holds suppliers names and addresses. (SUPPLIER) It will be
the 1 side to the 4th table.

The fourth table (AUDIT) tries to tie the three tables together. This table
consists of SPECNAME:specification, SPECREQaragraph, SUPPLIER:suppliernum
and fields, Yes, No, Comments. The way I have it set now is AUDIT is the many
side to SUPPLIER and SPECREQ

The user will select the supplier, and the specification then will step
through the paragraphs answering yes, no and filling out any comments.

I am using forms and subforms. I can select a supplier name, and a
specification. That part works fine. What I can't do is fill in the
Yes/No/Comments for the individual paragraph numbers. I can only get the
first row of the AUDIT table to display.

I hope this is clearer than my post yesterday. Any help would be apprecidated.
(I have removed the # out - thanks)

Pete

I'm trying to develop a database with three tables as follows:
Vendor

[quoted text clipped - 13 lines]

Thanks in advance.

Use a Form.

Tables and table datasheets are of very limited utility, and
professional developers always provide a Form to interact with the
data.

I'd suggest using a Form based on the Vendor table, with a Subform
based on Results. I'd guess that you could use a Combo Box on this
subform based on the Specification table to let you select the
appropriate Spec#. If the Spec# is always tied to a corresponding
Paragraph# then it's redundant to store both in the Results table -
I'm not clear about your design in that regard! Also you should
certainly not have two fields named Yes and No - instead, use one
Yes/No field (which can be displayed as a checkbox or as a combo box
allowing the choices Yes or No).

It's also best not to use the # character in fieldnames: it's a date
delimiter and can become confusing.

John W. Vinson[MVP]