Linking records

[davegb]

I've searched here and in the help screens, but I can't find the answer
to a very simple question. How do I create a specific link between 2
records in 2 different tables? I know how to use the Relationship view
to tell Access that a given field is generically linked to another
field in another table. But I can't find a clue as to how to tell it
that Record x in table A is related to Record y in Table B. It seems so
basic, but I can't figure it out. I'm guessing that I just have to type
have one field repeat in both tables and then type the appropriate
value from the other table into that field. Or something like that.
Thanks in advance!

[MGFoster]

davegb wrote:

I've searched here and in the help screens, but I can't find the answer
to a very simple question. How do I create a specific link between 2
records in 2 different tables? I know how to use the Relationship view
to tell Access that a given field is generically linked to another
field in another table. But I can't find a clue as to how to tell it
that Record x in table A is related to Record y in Table B. It seems so
basic, but I can't figure it out. I'm guessing that I just have to type
have one field repeat in both tables and then type the appropriate
value from the other table into that field. Or something like that.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No, don't do that. The first part of your paragraph seems correct: you
"generically" establish the relationship between the tables, not the
exact values in the columns. E.g.:

VehicleTypes:
vehicle_code integer -- contains the code for different vehicle types
description text -- the acutal names of the vehicle types

MotorPool:
VIN integer - the Vehicle ID Number
vehicle_code integer -- links to VehicleTypes
last_maintenance date -- the last maintenance date of the vehicle
... other columns ...

If you want to write a query that pulls data from both tables, like the
VehicleTypes.description, you'd do this:

SELECT MP.VIN, VT.description, MP.last_maintenance
FROM MotorPool As MP INNER JOIN VehicleTypes As VT
ON MP.vehicle_code = VT.vehicle_code

The results would look something like this:

VIN description last_maintenance
--- ----------- ----------------
2 Sedan 1/3/2005
3 Pickup 2/2/2005

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnaOZYechKqOuFEgEQJrxgCffhD2z3oqvff/WHui4uGsXQrh6IEAnjSq
e0QLz2dAh3ikIAttW7Y4dq5b
=SjCa
-----END PGP SIGNATURE-----

[davegb]

Thanks for your reply.
I think you've accidentally given me the answer, I do have to enter the
data, either by typing it in or from a drop-down list. Lets say I have
an "Employees" table, and a motor pool table. If I want to see what
vehicle is assigned to what employee, I have to enter the vehicle code
into an appropriate field in the Employee table. There's no graphic way
to connect that record in one table with the appropriate record in the
other table.
I can make it quicker by creating a drop-down list (using the Wizard)
of the motor vehicles and use that to create the individual link to a
person in the Employee table.
I was wondering if there was a quicker, probably graphic way to create
such a connection, like I've seen in other software. I'm hearing there
isn't.
Thanks for your help.

[MGFoster]

davegb wrote:

Thanks for your reply.
I think you've accidentally given me the answer, I do have to enter the
data, either by typing it in or from a drop-down list. Lets say I have
an "Employees" table, and a motor pool table. If I want to see what
vehicle is assigned to what employee, I have to enter the vehicle code
into an appropriate field in the Employee table. There's no graphic way
to connect that record in one table with the appropriate record in the
other table.
I can make it quicker by creating a drop-down list (using the Wizard)
of the motor vehicles and use that to create the individual link to a
person in the Employee table.
I was wondering if there was a quicker, probably graphic way to create
such a connection, like I've seen in other software. I'm hearing there
isn't.
Thanks for your help.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I believe you're talking about the Relationships window.

Tools > Relationships

When the Relationships window opens right click and select Add Table.
Add all the tables you want to create relationships between. To create
a realtionship between two tables: from the Primary Key (PK) table drag
& drop the PK column(s) to the 2nd table's (the Foreign Key [FK] table)
column(s). The "Edit Relationships" dialog box will appear - check the
appropriate boxes: Enforce Referential Integrity, or, Cascade Update
Related Fields, or, Cascade Delete Related Fields.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnkdMYechKqOuFEgEQK8mwCgqzMtO8NmcZjHsf9uwugdxs Ge+vYAn3BL
Woj+khgHUIj+w/qzthI3xZHH
=FQTN
-----END PGP SIGNATURE-----

[davegb]

Thanks for your reply.
No, I'm not talking about the Relationships window. As I stated in my
original inquiry, I ALREADY created a relationship between the 2 fields
in the 2 tables using the Relationship window. Fast and easy. But that
only creates a "Generic" relationship, 1:1 or 1:many, between the 2
fields in the 2 tables. I still have to create that relationship
between specific records in each table. As in my previous example, if I
have employees and cars, I create a generic relationship showing that
cars can be assigned to employees. What I am asking about is creating
the relationship between, say Car "54" and Employee "Muldoon". I have
to have a field in the Employee table which shows which car that
employee has. I understand it doesn't have to be named the same in both
tables. But to tell Access that Car "54" has been assigned to Employee
"Muldoon", I have to either have an employee field in the Car table, or
a car field in the employee table (the 2 fields I linked in the
Relationship diagram). If I choose the latter, I have to enter "54"
into the "car" field in the employee table to make the specific
connection between the 2 records in the 2 tables. The only other way I
can figure out to make this a little quicker is to make one a list into
a drop-down box in the other table, and select the appropriate item
from the list.
Is that any clearer? I've solved the problem. Was just thinking it
would be faster and easier if I could put the 2 tables in a window like
the relationship window, but each having a list of the items from each
table. Then just click and drag, like in the relationship window, to
connect car "54" to employee "Muldoon" without having to re-type this
information or use a drop-down window. Since this is not a feature,
I'll use the drop-down window to create the specific relationships.
Thank again.

[Bob Quintal]

"davegb" <da****@safebrowse.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

Thanks for your reply.
No, I'm not talking about the Relationships window.
I have to either have an employee field in
the Car table, or a car field in the employee table (the 2
fields I linked in the Relationship diagram). Is that any clearer? I've solved the problem. Was just
thinking it would be faster and easier if I could put the 2
tables in a window like the relationship window, but each
having a list of the items from each table. Then just click
and drag, like in the relationship window, to connect car "54"
to employee "Muldoon" without having to re-type this
information or use a drop-down window. Since this is not a
feature, I'll use the drop-down window to create the specific
relationships. Thank again.

you can do something sort-of graphical, using a third table.

First create a table with two fields, the employee ID and the
CarID. make the combination of both fields the primary key.

Create a form bound to the new table and put two listboxes on
it, side by side, Make the rowsource for the lefthand one the
employee info, the righthand one the vehicle info. The control
source fo each listbox is the relevant field in the new
table.Now you can view the pertinent item from each side.
because the listbox will inverse the video for the right items

--
Bob Quintal

PA is y I've altered my email address.

[davegb]

Thanks for the reply, Bob. I'm afraid you're way over my head. I'm new
to Access (just started working with it this week!) and I have some
questions.

"Create a form bound to the new table "

I sort of know how to create a form, but I don't know what "bound"
means or how to do it. Can you explain?

"and put two listboxes on it, side by side"
I don't know what that means.

"Make the rowsource for the lefthand one the employee info, the
righthand one the vehicle info."
What is a "rowsource" and how do I create one?

"The control source fo each listbox is the relevant field in the new
table"
Is this automatic after I do the above steps, or are more steps
involved here? What are they?

Thanks for the help.