Query dependent on different query

[mbedford]

I have a table that has data from several different forms. Prior to building the form, I thought the best idea was to create several queries that pull all the data together from the various forms based on various selections.

The difficulty lies in that there is not a single common field for all the involved tables.

The most central table is tblAssets, which has the following fields important to the question:

ID (text, unique asset identifier)
AcquireID (number, refers to tblAcquire.ID)

In the form, the operator will select an entry from a list derived from tblAssets with the bound column being the ID field. Based on the selection in this list, the form will populate the information drawn from several tables.

Most of the tables have a reference to tblAssets.ID, except for tblAcquire, which represents purchase details for assets. Because we purchase many items in bulk, in order to optimize the database size I seperated out the purchase information such as date, lease or purchase, price, lease number or invoice number, etc. And then I refer the the appropriate entry using the tblAssets.Acqui reID field.

qryAssets pulls all the fields from tblAssets based on the selection in the list in the form. And then qryAcquire should pull all the fields from tblAcquire based on qryAssets.Acqui reID. In practice it doesn't work.

I open the form and select an entry, then I open qryAssets and it displays the appropriate data based on that selected entry. But opening qryAcquire, it prompts me to enter data for qryAssets.Acqui reID.

Can I refer to another query in the WHERE statement in a query? If so, is it different from refering to a table entry or form control selection?

qryAcquire (doesn't work):

SELECT *
FROM tblAcquire
WHERE (((tblAcquire.ID)=([qryAssets].[AcquireID])));

qryAssets (works):

SELECT ID, UID, ModelID, Status, Dept, SubDept, SN, AcquireID
FROM tblAssets
WHERE (((tblAssets.ID)=[Forms]![formPrint]![listPrinter]));

I tried qryAssets using SELECT * first, and encountered the problem, and so thought it may be because I need to specify each of the fields, especially the AcquireID. But changing it did not make a difference.

[Steven Kogan]

You would need to refer to both the table and query in the FROM statement using a JOIN or LEFT JOIN... It is easier to set this up in Query Design mode. In SQL it should be something like:

SELECT tblAcquire.*
FROM tblAcquire INNER JOIN qryAssets ON tblAcquire.ID = qryAssets.AcquireID;

[mbedford]

Steven,

Thanks. I just got it figured out based on your instructions and it appears to be working now as I wanted.