Join on a UNION query

T

Tokyo Alex

Dear all,

I have a table of quotes, a table of relevent customer numbers and a table
of relevent quote keywords.

I have written a query to return all records where the quote has either a
relevent customer number or a relevent keyword, but not both.

The SQL code I use is:
SELECT otblQuotes.QUOTE_NUM, otblQuotes.QUOTE_KEYWORD,
otblSalesReps.SALES_REP_NAME, strfnMakeYear([otblQuotes]![TS_YY]) &
strfnMakeMonDay([otblQuotes]![TS_MM]) & strfnMakeMonDay([otblQuotes]![TS_DD])
AS DateStamp, otblQuotes.ITEMS_TOTAL, otblQuotes.SHIPPING_CHARGES,
otblQuotes.ORDER_TOTAL, otblQuotes.OE_NUMBER, [otblQuotes]![UPDATE_TIMESTAMP]
AS SYSTEM_TIMESTAMP
FROM ([tblCustomers] INNER JOIN otblQuotes ON [tblCustomers].CUSTOMER_NUM =
otblQuotes.CUSTOMER_NUM) LEFT JOIN otblSalesReps ON otblQuotes.SALESREP_NUM =
otblSalesReps.SALESREP_NUM
WHERE ((([otblQuotes]![UPDATE_TIMESTAMP])>=[Forms]![Main Board]![STDATE]))
UNION ALL SELECT otblQuotes.QUOTE_NUM, otblQuotes.QUOTE_KEYWORD,
otblSalesReps.SALES_REP_NAME, strfnMakeYear([otblQuotes]![TS_YY]) &
strfnMakeMonDay([otblQuotes]![TS_MM]) & strfnMakeMonDay([otblQuotes]![TS_DD])
AS DateStamp, otblQuotes.ITEMS_TOTAL, otblQuotes.SHIPPING_CHARGES,
otblQuotes.ORDER_TOTAL, otblQuotes.OE_NUMBER, [otblQuotes]![UPDATE_TIMESTAMP]
AS SYSTEM_TIMESTAMP
FROM (tblKeywords INNER JOIN (otblQuotes LEFT JOIN otblSalesReps ON
otblQuotes.SALESREP_NUM = otblSalesReps.SALESREP_NUM) ON
tblKeywords.QUOTE_KEYWORD = otblQuotes.QUOTE_KEYWORD) LEFT JOIN
[tblCustomers] ON otblQuotes.CUSTOMER_NUM = [tblCustomers].CUSTOMER_NUM
WHERE ((([otblQuotes]![UPDATE_TIMESTAMP])>=[Forms]![Main Board]![STDATE])
AND (([tblCustomers].CUSTOMER_NUM) Is Null));

This works fine. But...
I now need to use this (as a saved query) in another query joined to the
table of quote line items. Unfortunately, Access won't let me, giving me a
"Query is too complex" error.

As a workaround, I use a MakeTable query on the above Union query, then run
my second query on the resulting table. This works, but is clunky.

Is there any way to do an "either, but not both" on two separate fields
using only joins (no Union)?
Is there any other, more elegant workaround I can use to avoid this error?

For that matter, since I know you can have queries on saved Union queries (I
have some, and they work), can anyone tell me why this one produces an error?

Version: Access 2007 SP2
Database: Access 2003 - .mdb
OS: Windows XP Pro

Thanks very much for any advice.

Cheers,
Alex.
 
T

Tokyo Alex

Just reread that, and what I meant to say was the query returned records that
had either a relevant customer number, a relevant keyword, or both, but only
one record if it had both. THis way the QUOTE_NUM ID remains unique in the
result.

That should make a little more sense, now.

Again, thanks for any assistance.

Cheers,
Alex.


Tokyo Alex said:
Dear all,

I have a table of quotes, a table of relevent customer numbers and a table
of relevent quote keywords.

I have written a query to return all records where the quote has either a
relevent customer number or a relevent keyword, but not both.

The SQL code I use is:
SELECT otblQuotes.QUOTE_NUM, otblQuotes.QUOTE_KEYWORD,
otblSalesReps.SALES_REP_NAME, strfnMakeYear([otblQuotes]![TS_YY]) &
strfnMakeMonDay([otblQuotes]![TS_MM]) & strfnMakeMonDay([otblQuotes]![TS_DD])
AS DateStamp, otblQuotes.ITEMS_TOTAL, otblQuotes.SHIPPING_CHARGES,
otblQuotes.ORDER_TOTAL, otblQuotes.OE_NUMBER, [otblQuotes]![UPDATE_TIMESTAMP]
AS SYSTEM_TIMESTAMP
FROM ([tblCustomers] INNER JOIN otblQuotes ON [tblCustomers].CUSTOMER_NUM =
otblQuotes.CUSTOMER_NUM) LEFT JOIN otblSalesReps ON otblQuotes.SALESREP_NUM =
otblSalesReps.SALESREP_NUM
WHERE ((([otblQuotes]![UPDATE_TIMESTAMP])>=[Forms]![Main Board]![STDATE]))
UNION ALL SELECT otblQuotes.QUOTE_NUM, otblQuotes.QUOTE_KEYWORD,
otblSalesReps.SALES_REP_NAME, strfnMakeYear([otblQuotes]![TS_YY]) &
strfnMakeMonDay([otblQuotes]![TS_MM]) & strfnMakeMonDay([otblQuotes]![TS_DD])
AS DateStamp, otblQuotes.ITEMS_TOTAL, otblQuotes.SHIPPING_CHARGES,
otblQuotes.ORDER_TOTAL, otblQuotes.OE_NUMBER, [otblQuotes]![UPDATE_TIMESTAMP]
AS SYSTEM_TIMESTAMP
FROM (tblKeywords INNER JOIN (otblQuotes LEFT JOIN otblSalesReps ON
otblQuotes.SALESREP_NUM = otblSalesReps.SALESREP_NUM) ON
tblKeywords.QUOTE_KEYWORD = otblQuotes.QUOTE_KEYWORD) LEFT JOIN
[tblCustomers] ON otblQuotes.CUSTOMER_NUM = [tblCustomers].CUSTOMER_NUM
WHERE ((([otblQuotes]![UPDATE_TIMESTAMP])>=[Forms]![Main Board]![STDATE])
AND (([tblCustomers].CUSTOMER_NUM) Is Null));

This works fine. But...
I now need to use this (as a saved query) in another query joined to the
table of quote line items. Unfortunately, Access won't let me, giving me a
"Query is too complex" error.

As a workaround, I use a MakeTable query on the above Union query, then run
my second query on the resulting table. This works, but is clunky.

Is there any way to do an "either, but not both" on two separate fields
using only joins (no Union)?
Is there any other, more elegant workaround I can use to avoid this error?

For that matter, since I know you can have queries on saved Union queries (I
have some, and they work), can anyone tell me why this one produces an error?

Version: Access 2007 SP2
Database: Access 2003 - .mdb
OS: Windows XP Pro

Thanks very much for any advice.

Cheers,
Alex.
Click to expand...
 
D

Duane Hookom

You might need to declare the data type of your parameter [Forms]![Main
Board]![STDATE]. Also, make sure your functions are declared to return
strings.
 
Top