[thelist] Conditionals in Access

[Scott Brady]

The Access "Help" files are pretty unhelpful if you're looking for Access-specific SQL help.

I have this query:

SELECT
	u.user_id,
	u.preferred_name,
	u.first_name,
	u.last_name,
	us.status,
	SUM(t.num_swims) AS balance
FROM
	(
		usersTbl u LEFT OUTER JOIN
		practice_transactionTbl t
		ON t.member_id = u.user_id
	)
	INNER JOIN
	user_statusTbl us
	ON us.user_status_id = u.user_status_id
WHERE
	u.active_code = 1
	AND u.user_id
	IN
	(
		SELECT
			t.member_id
		FROM
			practice_transactionTbl t,
			transaction_typeTbl tt
		WHERE
			tt.transaction_type_id = t.transaction_type_id
			AND 
			(
				UCASE(tt.transaction_type) = 'SWIM WORKOUT'
				OR UCASE(tt.transaction_type) = 'VISIT WORKOUT'
			)
			AND t.date_transaction >= #08/02/2003#
			AND t.active_code = 1
	)
	AND UCASE(us.status) = 'MEMBER'
GROUP BY
	u.user_id,
	u.last_name,
	u.preferred_name,
	u.first_name,
	us.status
ORDER BY
	u.last_name, u.preferred_name

This query is incredibly slow (I think it's the combination of the SUM() and the IN sub-query, because other similar queries that have either of those (but not both) are nice and speedy.

Worse, I have a ColdFusion page where I'm running this query twice (once for "IN" and once for "NOT IN"), and I hope there's a better way to do this in Access.

Ideally, what I'd do is something like this add a column in the main select that determines the activity level and then order the results by that column first.  Theoretically, it would look something like this:

SELECT
	u.user_id,
	u.preferred_name,
	u.first_name,
	u.last_name,
	us.status,
	SUM(t.num_swims) AS balance,
	CASE WHEN (DateDiff('d',Now(),MAX(t.date_practice)) < 90)
		'Active'
	ELSE
		'Inactive'
	END CASE
	AS activityLevel
FROM
	(
		usersTbl u LEFT OUTER JOIN
		practice_transactionTbl t
		ON t.member_id = u.user_id
	)
	INNER JOIN
	user_statusTbl us
	ON us.user_status_id = u.user_status_id
WHERE
	u.active_code = 1
	AND UCASE(us.status) = 'MEMBER'
GROUP BY
	activityLevel,
	u.user_id,
	u.last_name,
	u.preferred_name,
	u.first_name,
	us.status,
ORDER BY
	activityLevel, u.last_name, u.preferred_name

But, I can't really find any info on conditionals in Access SQL.  So, I was wondering if there are any options I have, assuming I'm even making any sense.

This site is in ColdFusion MX 6.1 on IIS.

Thanks!

Scott
----------------------------
Scott Brady
http://www.scottbrady.net/