Linking two tables in a query

[Guest]

I am trying to retrieve data from one table and compare it to another table,
and print data from the second table based on the first table. The two
tables are linked by a common variable (name). One table contains input data
from a form, including the name. The second table contains a list of
possible names, as well as their location. i would like to display for all
input items (on table1) the corresponding location matched from table2, based
on the matching of the name on the two tables. I have created a query with
an outer join between the two tables (using name), but all I get are the
results from table1, and nothing from table2. As an example, if the name
fred is input on the form and placed on table1, i would like to match that
with the name fred on table2 and print out chicago, as it corresponds on
table 2 in the same record. I would like to do this for all values at once.

 

[Lynn Trapp]

It will help if you will post the SQL for your query.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Lynn:

Here is the SQL code for the query:
SELECT [Main Table].Unit, [Unit List].[Military Unit], [Unit List].Battalion
FROM [Main Table] LEFT JOIN [Unit List] ON [Main Table].Unit = [Unit
List].[Military Unit];


The table, [Main Table] is the table where the input from the form is
located. The table [Unit List] details the name of the unit and the
batallion (location). I would like to match the input of the unit from the
form which is then stored on the main table (as [Unit]), and then print from
the second table the batallion which the unit [Military Unit] belongs to. It
should be noted that the input of the unit from the form is a drop down box
which uses the table [Unit List] as the input parameters. It should also be
noted that this is my first go 'round at this. Also, if I create a join
between the autogenerated ID, it does what I am trying to do, only it links
the ID numbers instead of the units.

 

[Lynn Trapp]

Have you tried turning your join around:

SELECT [Main Table].Unit, [Unit List].[Military Unit], [Unit List].Battalion
FROM [Main Table] RIGHT JOIN [Unit List] ON [Main Table].Unit = [Unit
List].[Military Unit];


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Lynn:

Here is the SQL code for the query:
SELECT [Main Table].Unit, [Unit List].[Military Unit], [Unit
List].Battalion
FROM [Main Table] LEFT JOIN [Unit List] ON [Main Table].Unit = [Unit
List].[Military Unit];


The table, [Main Table] is the table where the input from the form is
located. The table [Unit List] details the name of the unit and the
batallion (location). I would like to match the input of the unit from
the
form which is then stored on the main table (as [Unit]), and then print
from
the second table the batallion which the unit [Military Unit] belongs to.
It
should be noted that the input of the unit from the form is a drop down
box
which uses the table [Unit List] as the input parameters. It should also
be
noted that this is my first go 'round at this. Also, if I create a join
between the autogenerated ID, it does what I am trying to do, only it
links
the ID numbers instead of the units.

It will help if you will post the SQL for your query.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Yes, i just get the other columns (Military Unit and Battalion)

Have you tried turning your join around:

SELECT [Main Table].Unit, [Unit List].[Military Unit], [Unit List].Battalion
FROM [Main Table] RIGHT JOIN [Unit List] ON [Main Table].Unit = [Unit
List].[Military Unit];


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Lynn:

Here is the SQL code for the query:
SELECT [Main Table].Unit, [Unit List].[Military Unit], [Unit
List].Battalion
FROM [Main Table] LEFT JOIN [Unit List] ON [Main Table].Unit = [Unit
List].[Military Unit];


The table, [Main Table] is the table where the input from the form is
located. The table [Unit List] details the name of the unit and the
batallion (location). I would like to match the input of the unit from
the
form which is then stored on the main table (as [Unit]), and then print
from
the second table the batallion which the unit [Military Unit] belongs to.
It
should be noted that the input of the unit from the form is a drop down
box
which uses the table [Unit List] as the input parameters. It should also
be
noted that this is my first go 'round at this. Also, if I create a join
between the autogenerated ID, it does what I am trying to do, only it
links
the ID numbers instead of the units.

It will help if you will post the SQL for your query.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



I am trying to retrieve data from one table and compare it to another
table,
and print data from the second table based on the first table. The two
tables are linked by a common variable (name). One table contains
input
data
from a form, including the name. The second table contains a list of
possible names, as well as their location. i would like to display for
all
input items (on table1) the corresponding location matched from table2,
based
on the matching of the name on the two tables. I have created a query
with
an outer join between the two tables (using name), but all I get are
the
results from table1, and nothing from table2. As an example, if the
name
fred is input on the form and placed on table1, i would like to match
that
with the name fred on table2 and print out chicago, as it corresponds
on
table 2 in the same record. I would like to do this for all values at
once.

 

[Lynn Trapp]

Then I would suggest doing the join on your ID fields. That's what they are
for.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Yes, i just get the other columns (Military Unit and Battalion)

Have you tried turning your join around:

SELECT [Main Table].Unit, [Unit List].[Military Unit], [Unit
List].Battalion
FROM [Main Table] RIGHT JOIN [Unit List] ON [Main Table].Unit = [Unit
List].[Military Unit];


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Lynn:

Here is the SQL code for the query:
SELECT [Main Table].Unit, [Unit List].[Military Unit], [Unit
List].Battalion
FROM [Main Table] LEFT JOIN [Unit List] ON [Main Table].Unit = [Unit
List].[Military Unit];


The table, [Main Table] is the table where the input from the form is
located. The table [Unit List] details the name of the unit and the
batallion (location). I would like to match the input of the unit from
the
form which is then stored on the main table (as [Unit]), and then print
from
the second table the batallion which the unit [Military Unit] belongs
to.
It
should be noted that the input of the unit from the form is a drop down
box
which uses the table [Unit List] as the input parameters. It should
also
be
noted that this is my first go 'round at this. Also, if I create a
join
between the autogenerated ID, it does what I am trying to do, only it
links
the ID numbers instead of the units.

:

It will help if you will post the SQL for your query.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



message
I am trying to retrieve data from one table and compare it to another
table,
and print data from the second table based on the first table. The
two
tables are linked by a common variable (name). One table contains
input
data
from a form, including the name. The second table contains a list
of
possible names, as well as their location. i would like to display
for
all
input items (on table1) the corresponding location matched from
table2,
based
on the matching of the name on the two tables. I have created a
query
with
an outer join between the two tables (using name), but all I get are
the
results from table1, and nothing from table2. As an example, if the
name
fred is input on the form and placed on table1, i would like to
match
that
with the name fred on table2 and print out chicago, as it
corresponds
on
table 2 in the same record. I would like to do this for all values
at
once.

 

[Guest]

I've tried that. The result is that it matches the ID fields between the
tables, and not the wanted fields.

Then I would suggest doing the join on your ID fields. That's what they are
for.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Yes, i just get the other columns (Military Unit and Battalion)

Have you tried turning your join around:

SELECT [Main Table].Unit, [Unit List].[Military Unit], [Unit
List].Battalion
FROM [Main Table] RIGHT JOIN [Unit List] ON [Main Table].Unit = [Unit
List].[Military Unit];


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Lynn:

Here is the SQL code for the query:
SELECT [Main Table].Unit, [Unit List].[Military Unit], [Unit
List].Battalion
FROM [Main Table] LEFT JOIN [Unit List] ON [Main Table].Unit = [Unit
List].[Military Unit];


The table, [Main Table] is the table where the input from the form is
located. The table [Unit List] details the name of the unit and the
batallion (location). I would like to match the input of the unit from
the
form which is then stored on the main table (as [Unit]), and then print
from
the second table the batallion which the unit [Military Unit] belongs
to.
It
should be noted that the input of the unit from the form is a drop down
box
which uses the table [Unit List] as the input parameters. It should
also
be
noted that this is my first go 'round at this. Also, if I create a
join
between the autogenerated ID, it does what I am trying to do, only it
links
the ID numbers instead of the units.

:

It will help if you will post the SQL for your query.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



message
I am trying to retrieve data from one table and compare it to another
table,
and print data from the second table based on the first table. The
two
tables are linked by a common variable (name). One table contains
input
data
from a form, including the name. The second table contains a list
of
possible names, as well as their location. i would like to display
for
all
input items (on table1) the corresponding location matched from
table2,
based
on the matching of the name on the two tables. I have created a
query
with
an outer join between the two tables (using name), but all I get are
the
results from table1, and nothing from table2. As an example, if the
name
fred is input on the form and placed on table1, i would like to
match
that
with the name fred on table2 and print out chicago, as it
corresponds
on
table 2 in the same record. I would like to do this for all values
at
once.