Displaying fields from one table in another table

[Peter Stone]

I haven't used Access for a few years and I need some help.

I have designed two tables: (1) Guatemala destinations and (2) Guatemala
database.

I need to take text from fields in Table (1) and put the text into
corresponding fields in Table (2).

Table (2) has a form. In design view for this form: For the list box
(Destination), in the Properties, I entered:
Row Source Type: Table/Query
Row Source: SELECT [Guatemala destinations].[Record key], [Guatemala
destinations].Destination FROM [Guatemala destinations];

This enables me to select the record in Table (1) that I require and get the
text from the Field (Destination) and put it into the Field (Destination) in
Table (2).

So far so good, but I need to get text from other fields in the same record.

E.G. text from the Field (Destination type) in Table (1) and insert it into
the Field (Destination type) in Table (2).

I can't work out what clause to add to accomplish this or am I barking up
the wrong tree.

Thanks in advance

 

[John Vinson]

I haven't used Access for a few years and I need some help.

I have designed two tables: (1) Guatemala destinations and (2) Guatemala
database.

I need to take text from fields in Table (1) and put the text into
corresponding fields in Table (2).

No. You almost certainly do NOT.

You're using a relational database. Use it relationally! This means
using the "Grandmother's Pantry Principle": "A place - ONE place! -
for everything, everything in its place".

Text from the Destinations table should be stored ONLY in the
Destinations table. If you need to see it in conjnction with fields in
Table2 (I don't know what that table is), then you can use a Form with
a Subform, or a Query joining the two tables, or a Combo Box, or a
variety of other techniques to *see* the information together on a
Form or on a Report; but it is neither necessary nor appropriate to
store that data redundantly.


John W. Vinson[MVP]

 

[cackerman]

Is this an automatic udpate you want as records are being entered? Have
you tried creating the relationship betweent he two tables? You would
need to enforce relational integrity and cascade updates.
Let me know if this works.

 

[Peter Stone]

Thank you.
I get the point about not storing data twice.

I need to attach a record to every record in Table (2) by selecting the
required(Destination) field from Table (1) and then display that Table (1)
record in the Table (2) form.

I just don't need to *see* the information, I also need to be able to search
for records in Table (2) based on the information in the attached Table (1)
record.

Is all this best done by using a subform?

 

[TC]

I personally feel that you would make your problem clearer, if you
described it in ordinary natural language - without using technical
terms like table, record, field & so on. Your use of those technical
terms is somewhat confusing, & does not serve to clarify what you are
trying to do. Tell us what you want to achieve from an "end user"
viewpoint, then we can suggest a way to proceed.

HTH,
TC

 

[Peter Stone]

Thanks for the patience. It’s rather complicated. Hopefully this is a little
clearer. I will use New York as my example.

I have a table that contains travel information. Every record in that table
has to be attached to a Destination (e.g. information about the Surrey Hotel
has to be attached to the Upper East Side).

The Destination table contains records that pinpoint each destination’s
location (e.g., the record for Upper East Side will contain info such as (a)
New York (b) New York State (c) New England (d) The North East.

We will search for Information records using the fields of the attached
Destination records.

I need to:
(1) attach a Destination record to each Information record by selecting the
destination from a list (e.g., attach the Upper East Side record to the
Surrey Hotel record).
(2) view the other fields of the Destination record in the Information
record form (New York, New York State, New England, The North East, etc.).

In my question, I described how I achieved #1. What’s next? If I put a
sub-form into the Information form, will that automatically display the
attached Destination record?

 

[TC]

The instant you say: "I have some tables that ...", I have to respond:
"What is the primary key of each table?"

You can't discuss tables, unless you state their primary keys. But I
suspect you mightn't be ready for that! That's why I suggested that you
avoid discussing tables at all, initially.

If you're determined to talk about tables, that's fine - but you have
to state their primary keys, otherwise the discussion will get nowhere
quickly. If you don't know what a primay key is, you have to do one of
two things:

(a) research that topic until you understand it, or

(b) /forget/ about tables, records & fields, and describe your needs
in normal lanaguage - as I suggested before! Then we can help you take
it from there.

HTH,
TC

 

[Peter Stone]

Both tables are autonumbered.

The database is for a Website. I've designed the tables and the forms and
individually they do everything I want. It's just that I've never joined
tables before. I can't fully describe what I'm doing because it's very large
and multifaceted. Access help is OK if you know what you're doing, but it's
fragmented and the tutorials only demonstrate the basics. I'm quite happy to
read up on the matter and solve the problem myself, but I'm far from
civilization. So any suggestions for sources on the Net would be welcome (if
it's not free, I can use credit card and download).

Thanks

 

[TC]

- If you want to learn more about database table design, the magic
phrase is "database normalization". Google for that term, you'll find
lots of hits.

- If you want me to comment on your table structure, you'll have to
describe each table explicitly. Your description so far, is not nearly
enough. Do it like this (for each table): Identify the primary key
field(s) clearly.

tblCustomer
CustID < primary key
CustName
(other fields describing the customer)

HTH,
TC

 

[rkc]

Both tables are autonumbered.

The database is for a Website. I've designed the tables and the forms and
individually they do everything I want. It's just that I've never joined
tables before. I can't fully describe what I'm doing because it's very large
and multifaceted. Access help is OK if you know what you're doing, but it's
fragmented and the tutorials only demonstrate the basics. I'm quite happy to
read up on the matter and solve the problem myself, but I'm far from
civilization. So any suggestions for sources on the Net would be welcome (if
it's not free, I can use credit card and download).

My free advice is to spend a few hours using and then looking at
the forms, modules, and reports in the Northwind.mdb file that comes
with every install of Access. While it is much maligned as an actual
useable application it has examples of how to do nearly every thing
you would need to do in the UI to a relational database.

When you're done doing that do a search for Solutions.mdb and study
what it has to offer. It may be on your system already. It ships with
some versions of Access. If it's not on your system, search msdn for it.

 

[John Vinson]

Thank you.
I get the point about not storing data twice.

I need to attach a record to every record in Table (2) by selecting the
required(Destination) field from Table (1) and then display that Table (1)
record in the Table (2) form.

I just don't need to *see* the information, I also need to be able to search
for records in Table (2) based on the information in the attached Table (1)
record.

Is all this best done by using a subform?

As suggested elsethread, some study of the principles of relational
database design would be useful. One good list of resources is at Jeff
Conrad's website:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Database Design 101 list is particularly useful.

The way to do this STARTS with the Tables. Forms are just tools to
manage data in tables; don't start worrying about Forms until you have
your table structure right.

It sounds like you have a one-to-many relationship from Destinations
to your Table2. The way you implement this is to have a Primary Key
(which need not be an Autonumber, though that is often convenient) in
Table1, and a "Foreign Key" - a Long Integer if the primary key is an
autonumber, a field of the same datatype and size otherwise - in the
second table. This should be the ONLY field from Table1 that you store
in Table2.

Typically, one can create a Form based on the "one" side table, with a
Subform based on the "many" table, using the Primary Key as the master
link field and the matching foreign key as the Child Link Field. This
will let you display the Destination record on the main form, and all
of the matching related records on the subform. Searching for a
Destination on the mainform will automatically bring up the related
information.

John W. Vinson[MVP]

 

[Peter Stone]

Your last paragraph gets to the heart of my problem. I think this will solve
my problem.

I'm looking at the links provided by you and TC. I appreciate the help.

Merry Christmas.