Matching data in a query from two tables

[Guest]

I have a form which inputs data ("Unit') in a drop down box (into the main
table) This data in the drop down box is based off of a separate table. In
addition, this table also matches other data to it. Example: Unit1 also has
Location1 and Battalion1 listed in the same record. I would like to develop
a query based on the input of the main form (the selection of the Unit), and
show in my results the Unit selected from the form (which shows on the main
table), plus the additional information tied to it from the second table
(location and battalion). Any help with this would be highly appreciated;
I've been trying to figure this out for days...

 

[[MVP] S.Clark]

Add both tables to the query, link them appropriately.

Add to the criteria:
WHERE UnitID = Forms!FormName!comboboxname

replacing the last two parts with the real names.

 

[Guest]

Thanks for the reply. I guess I am not doind something right. When doing
what you suggested and I run the query, it asks me for a parameter. What I
am attempting is that when I run the query, I wish to have the data evaluated
for all responses thus far, and placed into the query. I will then generate
a reprt from the query. It should also be said that this is my first attempt
at writing a database, though I learn more as I go... Thanks again.

 

[Guest]

Maybe this will clear up what I am attempting. I have a number of units each
assigned to a batallion. Each of these batallions are part of a brigade, as
such:

Unit Batallion Brigade
a m x
b m x
c n x
d n x
e o y
f o y
g p y
h p y

Each of the units is tied on a separate table (used for the input of the
drop down box) to the batallion and brigade (all in same record). I want to
generate a report based on a query which will show which units were selected
by batallion and by brigade for all of the units input thus far. I hope this
helps clarify what I am trying to do. It's hard to put into words. Thanks.

 

[[MVP] S.Clark]

What is the parameter name?

Thanks for the reply. I guess I am not doind something right. When doing
what you suggested and I run the query, it asks me for a parameter. What
I
am attempting is that when I run the query, I wish to have the data
evaluated
for all responses thus far, and placed into the query. I will then
generate
a reprt from the query. It should also be said that this is my first
attempt
at writing a database, though I learn more as I go... Thanks again.

 

[Guest]

It tells me:

Enter Parameter Value
Forms!IGAR Case Log!Unit

 

[[MVP] S.Clark]

Does adding square brackets help?

Forms![IGAR Case Log]![Unit]

Is the form open when you try to run the query?

 

[Guest]

No, for both cases.

I've been doing research in the massive help files available. I would think
that I could run an autolookup query to do this operation, but it is not
working. I want to match each record from one table to the corresponding
record in another table, then also display the associated information from
that table. I set up a query for both tables using a one to many
relationship using the field "unit" as the joining fields, however, the query
only lists from the main table and not the lookup table.

Does adding square brackets help?

Forms![IGAR Case Log]![Unit]

Is the form open when you try to run the query?

It tells me:

Enter Parameter Value
Forms!IGAR Case Log!Unit

 

[[MVP] S.Clark]

If the form is not open, then of course you'll get the parameter prompt.
Open the form first.

No, for both cases.

I've been doing research in the massive help files available. I would
think
that I could run an autolookup query to do this operation, but it is not
working. I want to match each record from one table to the corresponding
record in another table, then also display the associated information from
that table. I set up a query for both tables using a one to many
relationship using the field "unit" as the joining fields, however, the
query
only lists from the main table and not the lookup table.

Does adding square brackets help?

Forms![IGAR Case Log]![Unit]

Is the form open when you try to run the query?

It tells me:

Enter Parameter Value
Forms!IGAR Case Log!Unit

:

What is the parameter name?

message
Thanks for the reply. I guess I am not doind something right. When
doing
what you suggested and I run the query, it asks me for a parameter.
What
I
am attempting is that when I run the query, I wish to have the data
evaluated
for all responses thus far, and placed into the query. I will then
generate
a reprt from the query. It should also be said that this is my
first
attempt
at writing a database, though I learn more as I go... Thanks again.

:

Add both tables to the query, link them appropriately.

Add to the criteria:
WHERE UnitID = Forms!FormName!comboboxname

replacing the last two parts with the real names.

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

message
I have a form which inputs data ("Unit') in a drop down box (into
the
main
table) This data in the drop down box is based off of a separate
table.
In
addition, this table also matches other data to it. Example:
Unit1
also
has
Location1 and Battalion1 listed in the same record. I would like
to
develop
a query based on the input of the main form (the selection of the
Unit),
and
show in my results the Unit selected from the form (which shows
on
the
main
table), plus the additional information tied to it from the
second
table
(location and battalion). Any help with this would be highly
appreciated;
I've been trying to figure this out for days...