in a table using lookup how do i get results based on a value in .

[Mark05]

I have a table, for a field I am using a look up from another table. But I
want to base the return values based on the current table row.
I have table A where I have a field called Client. I want to return from
table B all the values from a field called WMS where the Client is the same
as the row in table A I am looking at. This needs to be a table as it will be
used in a form.

 

[John Vinson]

I have a table, for a field I am using a look up from another table.

That's the source of your problems. Most of us HATE the Lookup
misfeature! See

http://www.mvps.org/access/lookupfields.htm

for a critique.

But I
want to base the return values based on the current table row.
I have table A where I have a field called Client. I want to return from
table B all the values from a field called WMS where the Client is the same
as the row in table A I am looking at. This needs to be a table as it will be
used in a form.

Your table DOES NOT contain client names. Yes, it appears to, but
that's because the actual contents of your table - a numeric ClientID
- is being concealed from your view. You need to include the lookup
table in your query.

Secondly, you're assuming that "this needs to be a table because it
will be used in a form". That assumption IS WRONG. It's not only
possible to base a Form on a Query, rather than directly on a table;
it's perfectly routine and ordinary.

I'm not sure I understand how TableA, TableB, and your Clients lookup
table are related or just what you're trying to accomplish - but
believe me, the lookup field is (as it often does) doing more harm
than good.

John W. Vinson[MVP]

 

[Mark05]

Thanks John but I think I need to explain it more. I know you can use querys
in a form but I need to have values I can change and store as well as add new
rows.
my question is this.
Can I create a table (A) where I can specify a look up value for a field.
This look up value comes from another table (B), however I need to have a
WHERE clause or filter the return values based on a field in the current
table (A) that the look up field is in. So lets say table B contains the
following rows
ID Name value
1 Tom 65
2 Tom 44
3 Frank 22
4 Tom 88

So now in my table A I am adding a new row and here are the fields
ID Name Value

What I want to do is for the value field have the ability based on the name
I put in to see the values from table B that only apply to the Name entered
and not ALL the values from table B which will show up if my look up SQL
looks like SELECT values FROM table B
So Here is what I enter in table A
ID Name Value
7 Tom
My choices for my look up for the Value field should show
65 44 and 88 and not show 22
I hope this is clearer
mark

 

[John Vinson]

On Thu, 13 Jan 2005 09:13:06 -0800, Mark05

Answers inline.

Thanks John but I think I need to explain it more. I know you can use querys
in a form but I need to have values I can change and store as well as add new
rows.

No problem. Many, many queries are updateable. If you are assuming
that you must base a form on a Table to update it or add new rows,
RETHINK THAT ASSUMPTION; it's wrong. There are other tools as well;
see below.

my question is this.
Can I create a table (A) where I can specify a look up value for a field.

Of course. It's perfectly routine.

This look up value comes from another table (B), however I need to have a
WHERE clause or filter the return values based on a field in the current
table (A) that the look up field is in. So lets say table B contains the
following rows
ID Name value
1 Tom 65
2 Tom 44
3 Frank 22
4 Tom 88

So now in my table A I am adding a new row and here are the fields
ID Name Value

Your TableA *SHOULD NOT CONTAIN* either Name or Value. Storing that
information redundantly is neither necessary nor is it good design!
TableA should store the ID, and any needed information about TableA's
use of that value.

What I want to do is for the value field have the ability based on the name
I put in to see the values from table B that only apply to the Name entered
and not ALL the values from table B which will show up if my look up SQL
looks like SELECT values FROM table B
So Here is what I enter in table A
ID Name Value
7 Tom
My choices for my look up for the Value field should show
65 44 and 88 and not show 22
I hope this is clearer
mark

Stop thinking that you need a different Table for everything! You
don't.

Create a Form based on TableA. Put a Combo Box control (a "lookup"
tool, but it is NOT necessary to use a Table Lookup to create one!) on
the form based on a Query. Create this Query by adding TableB to the
query design window; select only the name field (and hope there aren't
two people named Tom in your system); set the query's Unique Values
property to True. Now create a Combo Box on your form based on this
query; this will give you a "lookup" on the form showing each name
only once. Let's say your form is named MyForm and the combo box is
named cboName.

Now create another query also based on TableB. Select the Name and
Value fields. As a criterion on the Name field put

=[Forms]![MyForm]![cboName]

This will filter this query to show only the values for the name
currently selected in cboName. Let's call this combo cboValue.

You'll need one additional thing: view the Properties of cboName; on
the Events tab select the AfterUpdate event. Click the ... icon and
choose the Code Builder. Access will put you into the VBA editor with
the Sub and End Sub lines; just add the line in between:

Private Sub cboName_AfterUpdate()
Me!cboValue.Requery
End Sub

This will "wake up" cboValue to the fact that its criteria have
changed.

John W. Vinson[MVP]

 

[Mark05]

Hi John
I am sorry I think you got the impression that I needed help with basic
coding, I do not. Nor do I need help with designing forms or using querys.
The question I was trying to get ansered was if I can create a TABLE and have
a field with a look up function that could limit the values returned based on
a different field in the same table and the SAME ROW in THIS TABLE. I
completely understand the concept of normalizing a database and the names I
used before were to keep things simple and readable, I use PF and FK
relationships, but the name TOM is easier to read while still getting my
point and question across. Tom is the FK in table B, it is also the FK in
Table A, I need to pick a value from table B to store in my new row in Table
A, The reason is Table A will have a multitude of new columns that are not
going to apply to every row in table B so to save space (there will not be a
row in table A for every record in table B) I will bring this one duplicate
field across. This is easier and will take up the same amount of space as
bringing the PK from table B. I realize this is not level 3 but as I am sure
you are aware sometimes you make adjustments that make good business sense.
You said 'Stop thinking that you need a different Table for everything!' I
never implied I do nor do you understand the project I am working, again it
was a straight forward question if it is possible in a TABLE for a field LOOK
UP to Filter based on this tables row you are on? I think this has been a
waste of time for both of us, sorry about that. I will just code the form
without using the look up feature in tables in access.
mark

On Thu, 13 Jan 2005 09:13:06 -0800, Mark05

Answers inline.

Thanks John but I think I need to explain it more. I know you can use querys
in a form but I need to have values I can change and store as well as add new
rows.

No problem. Many, many queries are updateable. If you are assuming
that you must base a form on a Table to update it or add new rows,
RETHINK THAT ASSUMPTION; it's wrong. There are other tools as well;
see below.

my question is this.
Can I create a table (A) where I can specify a look up value for a field.

Of course. It's perfectly routine.

This look up value comes from another table (B), however I need to have a
WHERE clause or filter the return values based on a field in the current
table (A) that the look up field is in. So lets say table B contains the
following rows
ID Name value
1 Tom 65
2 Tom 44
3 Frank 22
4 Tom 88

So now in my table A I am adding a new row and here are the fields
ID Name Value

Your TableA *SHOULD NOT CONTAIN* either Name or Value. Storing that
information redundantly is neither necessary nor is it good design!
TableA should store the ID, and any needed information about TableA's
use of that value.

What I want to do is for the value field have the ability based on the name
I put in to see the values from table B that only apply to the Name entered
and not ALL the values from table B which will show up if my look up SQL
looks like SELECT values FROM table B
So Here is what I enter in table A
ID Name Value
7 Tom
My choices for my look up for the Value field should show
65 44 and 88 and not show 22
I hope this is clearer
mark

Stop thinking that you need a different Table for everything! You
don't.

Create a Form based on TableA. Put a Combo Box control (a "lookup"
tool, but it is NOT necessary to use a Table Lookup to create one!) on
the form based on a Query. Create this Query by adding TableB to the
query design window; select only the name field (and hope there aren't
two people named Tom in your system); set the query's Unique Values
property to True. Now create a Combo Box on your form based on this
query; this will give you a "lookup" on the form showing each name
only once. Let's say your form is named MyForm and the combo box is
named cboName.

Now create another query also based on TableB. Select the Name and
Value fields. As a criterion on the Name field put

=[Forms]![MyForm]![cboName]

This will filter this query to show only the values for the name
currently selected in cboName. Let's call this combo cboValue.

You'll need one additional thing: view the Properties of cboName; on
the Events tab select the AfterUpdate event. Click the ... icon and
choose the Code Builder. Access will put you into the VBA editor with
the Sub and End Sub lines; just add the line in between:

Private Sub cboName_AfterUpdate()
Me!cboValue.Requery
End Sub

This will "wake up" cboValue to the fact that its criteria have
changed.

John W. Vinson[MVP]

 

[John Vinson]

The question I was trying to get ansered was if I can create a TABLE and have
a field with a look up function that could limit the values returned based on
a different field in the same table and the SAME ROW in THIS TABLE.

I apologize for misinterpreting.

No, you cannot. That functionality is available on forms but not on
tables.

John W. Vinson[MVP]