Lookup long list of values within a query of two linked tables

P

picmeup

Excuse my non-expertise of Access, but I am trying to load a long list
(30,000 "values") to see if any of these match up with those within a table
(table A we'll call it). Table A is linked to TAble B to look at a field
within that table. The common field linked to these tables is the "values."
How can I load all these values, without having to go into criteria (which
only allows up to 1,024 characters, or in my case, about 60 "values" at a
time)? Is there a command to look at all the values (I have these listed in a
column - 1 value per row).
 
J

Jeff Boyce

If I'm understanding what you are trying to do...

You can create a new query, add both TableA and (whatever table is holding
your 30,000 values), join the two on the fields in each that are supposed to
match. Select one/more fields from TableA (and/or the other) so you'll know
which rows match ... ?an ID field.

This tells Access to show fields when the values match up.

Or have I not understood...?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

picmeup

I'll try that, thanks.

Jeff Boyce said:
If I'm understanding what you are trying to do...

You can create a new query, add both TableA and (whatever table is holding
your 30,000 values), join the two on the fields in each that are supposed to
match. Select one/more fields from TableA (and/or the other) so you'll know
which rows match ... ?an ID field.

This tells Access to show fields when the values match up.

Or have I not understood...?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
P

picmeup

I'm not sure how to do this part you mentioned: 'Select one/more fields from
TableA (and/or the other) so you'll know which rows match ... ?an ID field.'
 
A

Albert D. Kallal

picmeup said:
I'm not sure how to do this part you mentioned: 'Select one/more fields
from
TableA (and/or the other) so you'll know which rows match ... ?an ID
field.'
Click to expand...


Yes, that sounds about right.

So assuming tablea has a whole bunch of fields but one of them is a part
number and table B. is simply a list the part numbers.

you would fire up that query builder drop in both tables and simply draw
join line from the part number of table A to the one field part number in
table B.

When you run the query you're going to get a list of records where all the
there's a match in both tables.

ultimately at the end of the day the best solution is going to be in what
context are you using this list. are you trying to restrict the list in a
report for example?

you can also use a where clause and SQL as a sub-select. in place of the
join idea we floated above. Thus, you can go something like

select * from tableA where tablea.ID in (select id from tableB)

So you have actually quite a few solutions at your fingertips. Your best
solution will depend in what context you are doing this. However if it is a
report I would simply take the existing query the report is based on, and
drop in tableB and draw the join line in the query builder.
 
Top