sql - Microsoft Access: How to pass parameter from main query to subquery - Stack Overflow

admin管理员组

文章数量:1292785

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.

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.

• sql
• ms-access

Share Improve this question Follow edited Feb 13 at 5:52 Dale K 27.4k1515 gold badges5858 silver badges8383 bronze badges asked Feb 13 at 5:06 John CJohn C 9711 gold badge11 silver badge1010 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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;

本文标签： sqlMicrosoft Access How to pass parameter from main query to subqueryStack Overflow