Query with joins to CrossTab and Sub-Query

I

iTISTIC

I have one query (SQL below) that works fine on it's own:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tblCustomers.account_number, tblCustomers.last_name,
tblCustomers.city, tblCustomers.zip_code,
qryCustomer_NextFilterChange.MinOfservice_call_date AS [Next Change],
tblCustomerEquipment_Crosstab.Cond,
tblCustomerEquipment_Crosstab.[R/O],
tblCustomerEquipment_Crosstab.USUV, tblCustomerEquipment_Crosstab.WHUV,
tblCustomers.service_only
FROM (tblCustomers INNER JOIN qryCustomer_NextFilterChange ON
tblCustomers.account_number =
qryCustomer_NextFilterChange.account_number) LEFT JOIN
tblCustomerEquipment_Crosstab ON tblCustomers.account_number =
tblCustomerEquipment_Crosstab.account_number
WHERE (((qryCustomer_NextFilterChange.MinOfservice_call_date) Between
[Start Date] And [End Date]))
ORDER BY tblCustomers.account_number;

I have another query which will return a result set of all customer
account numbers, and ONE of the customer's phone numbers (SQL below):

SELECT tblCustomers.account_number, (SELECT TOP 1 phone_number FROM
tblCustomerPhoneNumbers WHERE account_number =
tblCustomers.account_number) AS [Phone Number]
FROM tblCustomers;

Both of these queries execute without an error on their own. When I
modify the first query to join on the second and show the customer's
phone number, I receive the error "The Microsoft Jet database engine
does not recognize 'tblCustomers.account_number' as a valid field name
or expression." I have pasted the SQL code below of query #1 modified
with the join to query #2:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tblCustomers.account_number, tblCustomers.last_name,
tblCustomers.city, tblCustomers.zip_code,
qryCustomers_OnePhoneNumber.[Phone Number],
qryCustomer_NextFilterChange.MinOfservice_call_date AS [Next Change],
tblCustomerEquipment_Crosstab.Cond,
tblCustomerEquipment_Crosstab.[R/O],
tblCustomerEquipment_Crosstab.USUV, tblCustomerEquipment_Crosstab.WHUV,
tblCustomers.service_only
FROM ((tblCustomers INNER JOIN qryCustomer_NextFilterChange ON
tblCustomers.account_number =
qryCustomer_NextFilterChange.account_number) LEFT JOIN
tblCustomerEquipment_Crosstab ON tblCustomers.account_number =
tblCustomerEquipment_Crosstab.account_number) LEFT JOIN
qryCustomers_OnePhoneNumber ON tblCustomers.account_number =
qryCustomers_OnePhoneNumber.account_number
WHERE (((qryCustomer_NextFilterChange.MinOfservice_call_date) Between
[Start Date] And [End Date]))
GROUP BY qryCustomers_OnePhoneNumber.[Phone Number]
ORDER BY tblCustomers.account_number;

Anyone have any idea why this may be? I have tried putting the
sub-query for the phone number directly into query #1, joining on query
#2, etc. all with the same result (error message referenced above). I
use this same sub-query in other queries in the same database and it
works fine. The only difference in these other queries is that there is
no crosstab involved. I am assuming that is part of the problem.
 
V

Vincent Johns

Reconstructing your Tables from the SQL you posted, I came up with the
following 4 Tables:

[tblCustomers]
[qryCustomer_NextFilterChange]
[tblCustomerEquipment_Crosstab]
[tblCustomerPhoneNumbers]

Perhaps [qryCustomer_NextFilterChange] was intended to be a Query (I'm
just guessing based on its name), but you didn't say so, and you didn't
post its SQL if so.

Anyhow, I didn't have any problems with your first 2 Queries, but the
third one referred to yet another Table, [qryCustomers_OnePhoneNumber].
Renaming [query #2] to [qryCustomers_OnePhoneNumber] and revising the
SQL of the 3rd Query a bit gave me this SQL, which works but maybe not
quite the way you want it to.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tblCustomers.account_number,
tblCustomers.last_name,
tblCustomers.city, tblCustomers.zip_code,
qryCustomers_OnePhoneNumber.[Phone Number],
qryCustomer_NextFilterChange.MinOfservice_call_date AS [Next Change],
tblCustomerEquipment_Crosstab.Cond,
tblCustomerEquipment_Crosstab.[R/O],
tblCustomerEquipment_Crosstab.USUV,
tblCustomerEquipment_Crosstab.WHUV,
tblCustomers.service_only
FROM ((tblCustomers
INNER JOIN qryCustomer_NextFilterChange
ON tblCustomers.account_number =
qryCustomer_NextFilterChange.account_number)
LEFT JOIN tblCustomerEquipment_Crosstab
ON tblCustomers.account_number
= tblCustomerEquipment_Crosstab.account_number)
LEFT JOIN qryCustomers_OnePhoneNumber
ON tblCustomers.account_number
= qryCustomers_OnePhoneNumber.account_number
WHERE (((qryCustomer_NextFilterChange.MinOfservice_call_date)
Between [Start Date] And [End Date]))
GROUP BY tblCustomers.account_number,
tblCustomers.last_name, tblCustomers.city,
tblCustomers.zip_code,
qryCustomers_OnePhoneNumber.[Phone Number],
qryCustomer_NextFilterChange.MinOfservice_call_date,
tblCustomerEquipment_Crosstab.Cond,
tblCustomerEquipment_Crosstab.[R/O],
tblCustomerEquipment_Crosstab.USUV,
tblCustomerEquipment_Crosstab.WHUV,
tblCustomers.service_only
ORDER BY tblCustomers.account_number;

When I ran it, it produced what looks like good output. You might want
to post some sample data for the Tables, and if some of your Tables
cited here are really Queries, also post the SQL for those Queries, and
maybe a description of what you'd like them to return. None of what you
posted involved any crosstab, for example.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

I have one query (SQL below) that works fine on it's own:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tblCustomers.account_number, tblCustomers.last_name,
tblCustomers.city, tblCustomers.zip_code,
qryCustomer_NextFilterChange.MinOfservice_call_date AS [Next Change],
tblCustomerEquipment_Crosstab.Cond,
tblCustomerEquipment_Crosstab.[R/O],
tblCustomerEquipment_Crosstab.USUV, tblCustomerEquipment_Crosstab.WHUV,
tblCustomers.service_only
FROM (tblCustomers INNER JOIN qryCustomer_NextFilterChange ON
tblCustomers.account_number =
qryCustomer_NextFilterChange.account_number) LEFT JOIN
tblCustomerEquipment_Crosstab ON tblCustomers.account_number =
tblCustomerEquipment_Crosstab.account_number
WHERE (((qryCustomer_NextFilterChange.MinOfservice_call_date) Between
[Start Date] And [End Date]))
ORDER BY tblCustomers.account_number;

I have another query which will return a result set of all customer
account numbers, and ONE of the customer's phone numbers (SQL below):

SELECT tblCustomers.account_number, (SELECT TOP 1 phone_number FROM
tblCustomerPhoneNumbers WHERE account_number =
tblCustomers.account_number) AS [Phone Number]
FROM tblCustomers;

Both of these queries execute without an error on their own. When I
modify the first query to join on the second and show the customer's
phone number, I receive the error "The Microsoft Jet database engine
does not recognize 'tblCustomers.account_number' as a valid field name
or expression." I have pasted the SQL code below of query #1 modified
with the join to query #2:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT tblCustomers.account_number, tblCustomers.last_name,
tblCustomers.city, tblCustomers.zip_code,
qryCustomers_OnePhoneNumber.[Phone Number],
qryCustomer_NextFilterChange.MinOfservice_call_date AS [Next Change],
tblCustomerEquipment_Crosstab.Cond,
tblCustomerEquipment_Crosstab.[R/O],
tblCustomerEquipment_Crosstab.USUV, tblCustomerEquipment_Crosstab.WHUV,
tblCustomers.service_only
FROM ((tblCustomers INNER JOIN qryCustomer_NextFilterChange ON
tblCustomers.account_number =
qryCustomer_NextFilterChange.account_number) LEFT JOIN
tblCustomerEquipment_Crosstab ON tblCustomers.account_number =
tblCustomerEquipment_Crosstab.account_number) LEFT JOIN
qryCustomers_OnePhoneNumber ON tblCustomers.account_number =
qryCustomers_OnePhoneNumber.account_number
WHERE (((qryCustomer_NextFilterChange.MinOfservice_call_date) Between
[Start Date] And [End Date]))
GROUP BY qryCustomers_OnePhoneNumber.[Phone Number]
ORDER BY tblCustomers.account_number;

Anyone have any idea why this may be? I have tried putting the
sub-query for the phone number directly into query #1, joining on query
#2, etc. all with the same result (error message referenced above). I
use this same sub-query in other queries in the same database and it
works fine. The only difference in these other queries is that there is
no crosstab involved. I am assuming that is part of the problem.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Queries 2
Crosstab Query Questions 5
Using results from a Sub Query in a Crosstab Query 4
still no dynamic crosstab report 0
Crosstab query and report 14
Parameter Query Form Problem 17
DATABASE REPORT/CROSSTAB QUERY HELP 2

Top