[SOLVED] Microsoft Access: How to pass parameter from main query to subquery

Microsoft Access: How to pass parameter from main query to subquery

I am working with a Customers and TourBooking table. I need to return the number of trips for each customer (based on a criteria). I will need to add 2 more trip count columns (based on different criteria).

I created a subquery that only returns to count of trips for a specific CustomerID.

SELECT COUNT(tbl_Tour_Bookings.CustomerID) AS CountOfCustomerID
FROM tbl_Tour_Bookings
WHERE (((tbl_Tour_Bookings.CustomerID) = 2315) AND   
       ((tbl_Tour_Bookings.BookingStatusID) = 4));

The main query called qry_Master_Trip_Count is based on the Customers table with columns CustomerID, LastName, FirstName and an alias column called Trip Count with the SQL from the subquery.

SELECT 
    tbl_Customers.CustomerID, tbl_Customers.LastName, 
    tbl_Customers.FirstName, 
    (SELECT COUNT(tbl_Tour_Bookings.CustomerID) AS CountOfCustomerID
     FROM tbl_Tour_Bookings
     WHERE (((tbl_Tour_Bookings.CustomerID) = 2315) AND 
            ((tbl_Tour_Bookings.BookingStatusID) = 4))) AS [Trip Count]
FROM 
    tbl_Customers
ORDER BY 
    tbl_Customers.LastName, tbl_Customers.FirstName;

I have tried to replace the specific CustomerID (2315) with [qry_Master_Trip_Count].[CustomerID] in the subquery, but I just get just a prompt to enter a value. I have tried every way I know how and researched endless examples online (like the Northwinds example) but can't get it to work.

I could write a function with a DAO recordset but I have another 2 trip count alias column with different criteria so I know it would be crazy slow if each row had to make 3 function calls.

Any assistance would be most appreciated.

Solution

You should use the actual table.field reference:

SELECT 
    tbl_Customers.CustomerID, tbl_Customers.LastName, 
    tbl_Customers.FirstName, 
    (SELECT COUNT(tbl_Tour_Bookings.CustomerID) AS CountOfCustomerID
     FROM tbl_Tour_Bookings
     WHERE (((tbl_Tour_Bookings.CustomerID) = tbl_Customers.CustomerID) AND 
            ((tbl_Tour_Bookings.BookingStatusID) = 4))) AS [Trip Count]
FROM 
    tbl_Customers
ORDER BY 
    tbl_Customers.LastName, tbl_Customers.FirstName;