Criteria in Query based on data from another table

[bethabernathy]

I am having trouble creating a query where some of the criteria in the expression comes from a table that is not part of the query. Does anyone know the formula for this?

 

[tlbroadbent]

Could you give an example of the data and how you expect to use it? Include the tables in the query and the other table containing the criteria, please. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bethabernathy]

Hi There: By the way, I love this forum. There are three tables: a customer table, a transaction table and a 3rd table that contains data related to the transaction table but does not contain a field that relates the tables. What I want to do is pull a date field from the 3rd table named "date". Is this possible?

Thanks so much for your Great help. Beth

 

[tlbroadbent]

I'm still not clear on the requirement. Are you looking for something like one of the following queries?

SELECT
Customer.CustId,
Customer.CiustName,
Transactions.TranID,
Transactions.TranInfo,
Transactions.TranDate,
(Select Max([date]) From Dates) As MaxDate
FROM Customer
INNER JOIN Transactions
ON Customer.CustId = Transactions.CustID;

--------------------------------

SELECT Customer.CustId, Customer.CiustName, Transactions.TranID, Transactions.TranInfo, Transactions.TranDate
FROM Customer INNER JOIN Transactions ON Customer.CustId = Transactions.CustID
Where TranDate <= (Select max([Date]) From Dates);
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.