Queries Question

  • Thread starter Thread starter Polina
  • Start date Start date
P

Polina

I am running a query in access which looks at two tables
and returns in a query only the data from one table that
matches the data in another table. In excel this is
called a vlookup function, can someone please advise me
on what I have to enter under criteria for this to work.
 
Hi Polina,
If I understand you correctly, the query works okay but
you only want a particular match (similar to vlookup) out
of all the matches. If this is correct, in criteria for a
text field you're keying on type:
Like "*"&[Enter record text sought]&"*"

The string between the square brackets will generate on on-
screen prompt asking for some string sequence that would
identify the sought record.

Again, if I understand you correctly, you can also look
for records keying on numeric values by typing in criteria:
=[Enter value sought]

Or, if you want records within a range of values, type:
=[Enter lower limit] and <=[Enter upper limit]
Click to expand...

There are more variations, depending on how you want to
restrict the output.

If I've misunderstood the question, I apologize, but
perhaps more info would help get you your answer.

hcj
 
HCJ,

Thanks for your response...My problem is this I have
created two tables "A" and "B" in table "A" I have a list
of cost centers that I need to get information about, in
Table "B" I have a list of charges for all the cost
centers that are out there. Thus I need to run a query
that will Take table "B" and only return cost centers
that are found in "A", this is similar to a vlookup in
excel. how and what would I need to do to run this query
to get the right results. Thanks again

-----Original Message-----
Hi Polina,
If I understand you correctly, the query works okay but
you only want a particular match (similar to vlookup) out
of all the matches. If this is correct, in criteria for a
text field you're keying on type:
Like "*"&[Enter record text sought]&"*"

The string between the square brackets will generate on on-
screen prompt asking for some string sequence that would
identify the sought record.

Again, if I understand you correctly, you can also look
for records keying on numeric values by typing in criteria:
=[Enter value sought]

Or, if you want records within a range of values, type:
=[Enter lower limit] and <=[Enter upper limit]
Click to expand...

There are more variations, depending on how you want to
restrict the output.

If I've misunderstood the question, I apologize, but
perhaps more info would help get you your answer.

hcj

-----Original Message-----
I am running a query in access which looks at two tables
and returns in a query only the data from one table that
matches the data in another table. In excel this is
called a vlookup function, can someone please advise me
on what I have to enter under criteria for this to work.
.
Click to expand...
.
Click to expand...
 
Hi again,
I assume you know how to build a query. If you're
brand new at this, let me know and I'll take smaller steps.

The short answer is to add both tables A and B to the
query grid in design mode and join the two tables at the
cost center field. Select the cost center field from
Table A and charge data from Table B. Select
sort/ascending for the cost center field. You can subsort
on other fields if you like.
If you run that query as is, you'll get cost centers
from A that DO have matching charge records from B.
Charges in B that don't have a matching cost center in A
will NOT list. Cost centers in A that have no charges in
B will NOT list either.

The long answer is if you need more help and don't know
how to build a query, I'll be happy to step you through
the process. Post back and let me know how you're doing.
I'll check for your reply over the next day or so (I'm in
US Central time). I also speak Excel, although I'm using
Office 97 at the moment. In fact, I also speak "cost
center" and "charge records", so your question is very
interesting to me.

hcj

-----Original Message-----
HCJ,

Thanks for your response...My problem is this I have
created two tables "A" and "B" in table "A" I have a list
of cost centers that I need to get information about, in
Table "B" I have a list of charges for all the cost
centers that are out there. Thus I need to run a query
that will Take table "B" and only return cost centers
that are found in "A", this is similar to a vlookup in
excel. how and what would I need to do to run this query
to get the right results. Thanks again

-----Original Message-----
Hi Polina,
If I understand you correctly, the query works okay but
you only want a particular match (similar to vlookup) out
of all the matches. If this is correct, in criteria for a
text field you're keying on type:
Like "*"&[Enter record text sought]&"*"

The string between the square brackets will generate on on-
screen prompt asking for some string sequence that would
identify the sought record.

Again, if I understand you correctly, you can also look
for records keying on numeric values by typing in criteria:
=[Enter value sought]

Or, if you want records within a range of values, type:
=[Enter lower limit] and <=[Enter upper limit]
Click to expand...

There are more variations, depending on how you want to
restrict the output.

If I've misunderstood the question, I apologize, but
perhaps more info would help get you your answer.

hcj

-----Original Message-----
I am running a query in access which looks at two tables
and returns in a query only the data from one table that
matches the data in another table. In excel this is
called a vlookup function, can someone please advise me
on what I have to enter under criteria for this to work.
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Thanks so very much for all your help you just saved me
hours of work.. thanks for all your help
 
Back
Top