Data from Table to Populate Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Walter - 26 Oct 2007 16:27 GMT

My query works great for entering information in two fields in the criteria
row and I get the data that I want. When I run the query it asks for a base
number and then a supplier code, I click run and it returns the data that I
want. I have a table with a couple of hundred base numbers and supplier
codes. I do not want to do them one at a time. My item field criteria in
the current query looks like this:
Like "*" & [Enter Base Number] & "*" and then the supplier code field
criteria is: [Enter code].
How do I pass each row in the table over to the query? Is that possible
with this setup? If you need more information let me know.

Reply to this Message

Darren Bartrup - 26 Oct 2007 16:46 GMT

You'll need to change your critieria to an IN clause and then reference all
the records in your second table using a subquery.

The SQL will look something like this:
SELECT Feb1.BaseNum, Feb1.SupplierCode, Feb1.field18
FROM Feb1
WHERE (Feb1.BaseNum In (SELECT Jan1.BaseNum FROM Jan1) AND Feb1.SupplierCode
IN (SELECT Jan1.SupplierCode FROM Jan1));

In the GUI this will appear as having In (SELECT Jan1.BaseNum FROM Jan1) in
the criteria for your BaseNum and In (SELECT Jan1.SupplierCode FROM Jan1) in
the criteria for your supplier code.

Basically it's asking "Does this number appear in that table?"

Reply to this Message

Walter - 26 Oct 2007 17:07 GMT

Is there a way to do this in the "design view" rather than with SQL? I am
not using a form or report. Just trying to produce raw data. I am not clear
on your explanation. If possible I would just like to run a query or two and
then export the data to excel once I have all the data that I want.

> You'll need to change your critieria to an IN clause and then reference all
> the records in your second table using a subquery.
[quoted text clipped - 10 lines]
>
> Basically it's asking "Does this number appear in that table?"

Reply to this Message

Darren Bartrup - 26 Oct 2007 17:11 GMT

That is using just a query - if you select the down arrow next to where you
switch between design view and datasheet view you'll see a third option of
SQL view.

But it doesn't work as expected anyway - see my post further down.

Reply to this Message

Darren Bartrup - 26 Oct 2007 17:05 GMT

Or you could write it as:

SELECT Feb1.BaseNum, Feb1.SupplierCode, Feb1.field18
FROM Feb1 INNER JOIN Jan1 ON (Feb1.SupplierCode = Jan1.SupplierCode) AND
(Feb1.BaseNum = Jan1.BaseNum);

Starting to think that neither will work - what if Jan1 table has records
that have BaseNum's in table and SupplierCode's in table, but they're not on
the same record.

Will need to think about this some more.

Reply to this Message

Darren Bartrup - 26 Oct 2007 17:16 GMT

Got it (I hope).

SELECT Feb1.BaseNum, Feb1.SupplierCode, Feb1.field18
FROM Feb1 INNER JOIN Jan1 ON (Feb1.BaseNum = Jan1.BaseNum AND
Feb1.SupplierCode = Jan1.SupplierCode);

To create a query using the SQL above, create a new query in design view,
but DON'T add any tables. Just click close on the Show Table dialog box.

Your design view will then drop-down menu will now show an SQL option.
Select this and you'll be given a blank screen with SELECT; written in it.
Delete this an enter the SQL above.

You'll need to make some changes to accommodate your table names and fields,
but it should work. :)

Reply to this Message

Walter - 26 Oct 2007 18:41 GMT

Okay, let me drop a bit more detail. Here is query design view:

Item Price
SupplyCode
PurchTbl PurchTbl
PurchTble
Like "*" & [Enter base number] & "*" [Enter
code]

This query works great when I enter in a base number and code and it returns
price along with some other information. However, I would like to be able to
pull a few hundred of these at the same time. I have a table called BaseNbrs
with two fields: BaseNbr and Code. The BaseNbr, which looks like W1234 is a
part of the whole Item such as 123W1234-001. The code I enter matches the
whole supply code field so there is no problem with a partial entry there.

I was hoping that somehow the query could call each row of data from the
table and enter it in the appropriate criteria row and then I would get 200
prices for 200 rows of data. Unfortunately, I can only link the two on the
supply code field as BaseNbr is only a part of Item. Does that make sense?

> Got it (I hope).
>
[quoted text clipped - 11 lines]
> You'll need to make some changes to accommodate your table names and fields,
> but it should work. :)

Reply to this Message

Walter - 26 Oct 2007 19:32 GMT

Never mind, I figured it out for myself... Thanks for the ideas.

> Okay, let me drop a bit more detail. Here is query design view:
>
[quoted text clipped - 32 lines]
> > You'll need to make some changes to accommodate your table names and fields,
> > but it should work. :)

Reply to this Message