how do I get 4 fields in one table to show up in another table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables. One table called listing, that has one field as an ID and
key field and four data fields (text). I have another table called findings
with 7 fields. The findings table has one field as the ID & Key field, a
field which is a combo box that selects the value I am looking for from
listing. I have three other fields in findings table that I want to
populate after the combo box choice is selected, automatcally, with the
coresponding data from the fields in listing and place them in these three
fields in findings.

The findings table has two other fields, one called findings(memo type) and
the other recomendations (memo type). This may not be the way to desing
the db, but I am creating a database to use for reporting on HIPAA audits
and I just do not want to type or select a lot of information each time I go
to write up a finding. Anyone got some ideas? Programing is not my bag
anymore.
 
This is crazy unless the fields in listings change and you want to record in
findings 'a snapshot of the settings' at the time selected. Otherwise the
values can be seen by useing the relationship between the two tables in a
query.

I am assuming you have something like this:

tblFindings = FindingID, ListingID, ... finding fields

tblListings = ListingID, .... listing fields


If you are looking for ideas on how to show the related fields in the listing
table while viewing a form with findings as the record source ... my favorite
way is to:

Use tblListings or create a query from tblListings as the source for a Combo
Box, Combo box would be bound (record source) to the LisingID in the Fields
table. in the Combo Box properties be sure to set Column Count to the
number of Columns,and Column Width to the column widths ... two columns could
look like this if you wanted to hide 1 from the viewer ...

2";0"

Then set the record source of unbound controls on the form, each to the
appropriate column of the combo box, so the record source would look
something like this ...

=ctrListingID.Column(0) would show the first column
=ctrListingID.Column(1) shows the second column, etc.

hope this helps,
Roger
 
Thanks, that is what I was looking for. I have not played with access since
access 97, and now I am back at it again to resolve some work related issues.
Thanks.
 
I want to
populate after the combo box choice is selected, automatcally, with the
coresponding data from the fields in listing and place them in these three
fields in findings.
Click to expand...

Why?

You're using a relational database. Use it relationally!

Relational databases use the "Grandmother's Pantry Principle" - "A
place (ONE place!) for everything, everything in its place".

Store these four fields once, and once only, in the Listings table. If
you want to see the values in these fields in conjunction with values
in another table, store the ID in the other table, and then use a
Query joining the two tables. It is neither necessary nor good design
to store the data redundantly in a second table.

John W. Vinson[MVP]
 
I did get the result that I wanted in the form form the other reply, however,
now that I wanted to create a report, I can get only part of what I am
looking for. So what you said makes sense, but this is not my area of
expertise. So I am going to expand a little more on what was stated before
with some questions.

The database that I am setting up is for doing a audit of the HIPAA Security
Rule . The listing table is contains the, key foe;d. Major Safeguard as one
field, the sub -Safeguard in the second field, the statute in the 3rd field.
and in the last field is the implementation. There are only 41 records and
they will remain static .

The findings table has 4 fields. The key field, Standards, Findings &
Recomendations. Both the findings and recomendations are noting more than a
memo field. The Standards field in this table is a combo box that stores
the key id of the row of the listing table that the findings and
recomendations are to be made about. This table will have many records and
each record will link to the one of the records from listing. There may be
multible records in findings that relate to the same record in listings.

The form is of the findings table and now displays the key # in the field,
using bound text boxes to display each of the four fields from the listing
table that was selected from the listing table, and then I can enter the
findings and recomendations data into the appropriate fields.

Now I want a report that list each of the safeguards, in the groups and
all the findings and recomendation for each listing group, that was
identified when filling in the form for the findings and recomendations
table.

The report should be like this :
Standard:
Finding
Recomendation
Finding
Recomendation
Standard:
Finding
Recomendation, etc.

So maybe the design that I have created is wrong and may have to rethink the
way that it was created.

So I think that I have basicly done what you suggested. Here is where I
have a problem creating a query to create the report. that I am looking for.
The report will need to use both tables and pull the information into the
report. how would i feed the querry so that when it sees the standards key
number in the findings table , to display all the fields?
 
What John said, the combo box technique is for displaying the fields in the
other table only, not for storing the same info in both tables. You can use
the combo box technique or a query that combines the two tables for the
source of the form. No matter what you should have the table structure as
John said.

It sounds like your table structure should look like this;

Listing Table:
ListingID = Primary Key
MajorSafegaurd
SubSafeguard
Statute
Implementation

Findings Table:
FindingsID = Primary Key
ListingID = what you called standards, foreign key to Listings Table
Findings
Recomendations

To combine the two tables in a query:
-Create a new query and add the two table from the add tables dialog.
-Drag the asterisk * at the top of the Findings table to the first field in
the query
-Drag the all fields except the ListingID in the listing Table each to it's
own query output field
-If Access didn't create it automatically drag from one tabl'e listing field
to the other tables listing field to create a relationship
-double click on this line that now runs between the two ListingID fields
-A relationship join type dialog box will open, select show all records from
Findings Table and only the maching records from the Listings Table - this
insures that if a record in the findings table was created, but a listing was
not selected that it still show up on your reports.

This query can be the source for your report or form

One reason for not including the ListingID, primary key in the Listings Table
is that if you use this query as the source for the form ... when you add a
record Access will correctly guess that you want the new record added to the
Findings Table and the the Listings Table is acting as a Look Up Table in
it's fuction. It is also redundant and confusing to have the ListingID in
two fields, when it is the same information.

To create a Report, use the Report Sorting and Grouping dialog. You can get
to it from the tool bar or the View menu. To see how this works use the
report wizard with the above query as your record source. Add the fields for
grouping through the report wizard and then go into the Grouping and Sorting
dialog to see how it is set up - experiment with different settings and use
F1 to see how it works;)

Hope this helps,
Roger
 
I auctually have it. I needed to created a relationship between the two
tables for the report. once I did that then it all linked up. Thanks for
everyones help.
 
Back
Top