Simple query question

[JohnGregor]

Hello,

I used to do some programming with Access and am just now trying to get back
into it. I have been driving myself slowly crazy for the last hour with
something that I know darn well is very simple.

I am in Microsoft design view. I am trying to create an expression to call
data from a different table based on a field value in the current table. All
I want to do is use the "select" command to create a temporary query. I will
be darned if I can remember how to do it. I also can't remember what you call
this kind of query or I am certain I could look it up in two seconds.

Could somebody please direct me to the proper area of the Microsoft
knowledgebase so that I can relearn how to do this?

Thanks,

 

[Ken Snell \(MVP\)]

SELECT T.FieldName
FROM TableName AS T
WHERE T.LinkingField = QueryTableName.LinkingField;

 

[JohnGregor]

Thanks for the fast reply, but it's not what I'm looking for (my fault
totally as I am having huge trouble explaining what I want).

I will give two specific examples of what I am trying to do.

1) I want to have a logic statement along the lines of:
Bpart: (iif (select part from table) is null), 1,0)

2) I also need to be able to return multiple items:
List: select Part from thistable where supplier.othertable =
supplier.thistable as temp

There is a whole section buried in Access help that describs these kinds of
queries, but I am completly unable to remember how to find it.

I really need to find it.

thanks,

 

[Ken Snell \(MVP\)]

Perhaps the better way for doing what you seek is to use the DLookup
function -- it's slower than a SELECT subquery, but easier to use.

1) I want to have a logic statement along the lines of:
Bpart: (iif (select part from table) is null), 1,0)

Bpart: IIf(DLookup("Part", "Table") Is Null, 1, 0)

2) I also need to be able to return multiple items:
List: select Part from thistable where supplier.othertable =
supplier.thistable as temp

List: DLookup("Part", "ThisTable", "Supplier=" & [Supplier])


Otherwise, using subqueries is similar to what you've posted. The "trick" is
that you have to be sure that the subquery will return no more than one
record in the cases you've shown. If you know there is just one record in
the table being looked up, then you're ok; but if there are more than one
record, then you need to write the subquery so that it cannot return more
than one record. This is where DLookup function is helpful because it only
returns a single value.

If this still isn't helping, give us some examples of data that are in the
tables and what you want the query to give you as a result.

 

[Ken Snell \(MVP\)]

Also, perhaps Allen Browne's info about subqueries will be helpful:
http://allenbrowne.com/subquery-01.html


--

Ken Snell
<MS ACCESS MVP>

Perhaps the better way for doing what you seek is to use the DLookup
function -- it's slower than a SELECT subquery, but easier to use.

1) I want to have a logic statement along the lines of:
Bpart: (iif (select part from table) is null), 1,0)

Bpart: IIf(DLookup("Part", "Table") Is Null, 1, 0)

2) I also need to be able to return multiple items:
List: select Part from thistable where supplier.othertable =
supplier.thistable as temp

List: DLookup("Part", "ThisTable", "Supplier=" & [Supplier])


Otherwise, using subqueries is similar to what you've posted. The "trick"
is that you have to be sure that the subquery will return no more than one
record in the cases you've shown. If you know there is just one record in
the table being looked up, then you're ok; but if there are more than one
record, then you need to write the subquery so that it cannot return more
than one record. This is where DLookup function is helpful because it only
returns a single value.

If this still isn't helping, give us some examples of data that are in the
tables and what you want the query to give you as a result.
--

Ken Snell
<MS ACCESS MVP>

Thanks for the fast reply, but it's not what I'm looking for (my fault
totally as I am having huge trouble explaining what I want).

I will give two specific examples of what I am trying to do.

1) I want to have a logic statement along the lines of:
Bpart: (iif (select part from table) is null), 1,0)

2) I also need to be able to return multiple items:
List: select Part from thistable where supplier.othertable =
supplier.thistable as temp

There is a whole section buried in Access help that describs these kinds
of
queries, but I am completly unable to remember how to find it.

I really need to find it.

thanks,

 

[JohnGregor]

Hello,

I followed your advice and now I have my query working.

Thanks for your help!

Also, perhaps Allen Browne's info about subqueries will be helpful:
http://allenbrowne.com/subquery-01.html


--

Ken Snell
<MS ACCESS MVP>

Perhaps the better way for doing what you seek is to use the DLookup
function -- it's slower than a SELECT subquery, but easier to use.

1) I want to have a logic statement along the lines of:
Bpart: (iif (select part from table) is null), 1,0)

Bpart: IIf(DLookup("Part", "Table") Is Null, 1, 0)

2) I also need to be able to return multiple items:
List: select Part from thistable where supplier.othertable =
supplier.thistable as temp

List: DLookup("Part", "ThisTable", "Supplier=" & [Supplier])


Otherwise, using subqueries is similar to what you've posted. The "trick"
is that you have to be sure that the subquery will return no more than one
record in the cases you've shown. If you know there is just one record in
the table being looked up, then you're ok; but if there are more than one
record, then you need to write the subquery so that it cannot return more
than one record. This is where DLookup function is helpful because it only
returns a single value.

If this still isn't helping, give us some examples of data that are in the
tables and what you want the query to give you as a result.
--

Ken Snell
<MS ACCESS MVP>

Thanks for the fast reply, but it's not what I'm looking for (my fault
totally as I am having huge trouble explaining what I want).

I will give two specific examples of what I am trying to do.

1) I want to have a logic statement along the lines of:
Bpart: (iif (select part from table) is null), 1,0)

2) I also need to be able to return multiple items:
List: select Part from thistable where supplier.othertable =
supplier.thistable as temp

There is a whole section buried in Access help that describs these kinds
of
queries, but I am completly unable to remember how to find it.

I really need to find it.

thanks,



:

SELECT T.FieldName
FROM TableName AS T
WHERE T.LinkingField = QueryTableName.LinkingField;

--

Ken Snell
<MS ACCESS MVP>

Hello,

I used to do some programming with Access and am just now trying to
get
back
into it. I have been driving myself slowly crazy for the last hour
with
something that I know darn well is very simple.

I am in Microsoft design view. I am trying to create an expression to
call
data from a different table based on a field value in the current
table.
All
I want to do is use the "select" command to create a temporary query.
I
will
be darned if I can remember how to do it. I also can't remember what
you
call
this kind of query or I am certain I could look it up in two seconds.

Could somebody please direct me to the proper area of the Microsoft
knowledgebase so that I can relearn how to do this?

Thanks,

 

[i_takeuti]

Hello,

I followed your advice and now I have my query working.

Thanks for your help!

Also, perhaps Allen Browne's info about subqueries will be helpful:
http://allenbrowne.com/subquery-01.html


--

Ken Snell
<MS ACCESS MVP>

Perhaps the better way for doing what you seek is to use the DLookup
function -- it's slower than a SELECT subquery, but easier to use.

1) I want to have a logic statement along the lines of:
Bpart: (iif (select part from table) is null), 1,0)

Bpart: IIf(DLookup("Part", "Table") Is Null, 1, 0)



2) I also need to be able to return multiple items:
List: select Part from thistable where supplier.othertable
=
supplier.thistable as temp

List: DLookup("Part", "ThisTable", "Supplier=" & [Supplier])


Otherwise, using subqueries is similar to what you've posted. The
"trick"
is that you have to be sure that the subquery will return no more than
one
record in the cases you've shown. If you know there is just one record
in
the table being looked up, then you're ok; but if there are more than
one
record, then you need to write the subquery so that it cannot return
more
than one record. This is where DLookup function is helpful because it
only
returns a single value.

If this still isn't helping, give us some examples of data that are in
the
tables and what you want the query to give you as a result.
--

Ken Snell
<MS ACCESS MVP>



Thanks for the fast reply, but it's not what I'm looking for (my fault
totally as I am having huge trouble explaining what I want).

I will give two specific examples of what I am trying to do.

1) I want to have a logic statement along the lines of:
Bpart: (iif (select part from table) is null), 1,0)

2) I also need to be able to return multiple items:
List: select Part from thistable where supplier.othertable
=
supplier.thistable as temp

There is a whole section buried in Access help that describs these
kinds
of
queries, but I am completly unable to remember how to find it.

I really need to find it.

thanks,



:

SELECT T.FieldName
FROM TableName AS T
WHERE T.LinkingField = QueryTableName.LinkingField;

--

Ken Snell
<MS ACCESS MVP>

Hello,

I used to do some programming with Access and am just now trying to
get
back
into it. I have been driving myself slowly crazy for the last hour
with
something that I know darn well is very simple.

I am in Microsoft design view. I am trying to create an expression
to
call
data from a different table based on a field value in the current
table.
All
I want to do is use the "select" command to create a temporary
query.
I
will
be darned if I can remember how to do it. I also can't remember
what
you
call
this kind of query or I am certain I could look it up in two
seconds.

Could somebody please direct me to the proper area of the Microsoft
knowledgebase so that I can relearn how to do this?

Thanks,