CODEWITHSUNDEEP
sqlms-accessms-access-2010
acr_scout
acr_scout

Reputation: 579

Why is MS Access 2010 SQL choking on this query?

The following query will not show up in design view and if you trying to make it show it locks up MS Access and you have to use the Task Manager to stop MS Access. The query actually runs and produces the correct results. If there is a better way I will certainly accept that.

SELECT
    log_metric_N.metric_title,
    log_metric_N.metric_N
FROM
    (
        SELECT
                tref_log_metrics_weights_and_factors.metric_title,
                [metric_base].[metric_count],
                [metric_base].[metric_sum],
                (([metric_base].[metric_count]*[tref_log_metrics_weights_and_factors].[metric_weight])/[metric_base].[metric_sum]) AS metric_N
            FROM
                tref_log_metrics_weights_and_factors,

                (
                    SELECT 
                        Count(tref_log_metrics_weights_and_factors.metric_weight) AS metric_count, 
                        Sum(tref_log_metrics_weights_and_factors.metric_weight) AS metric_sum
                    FROM 
                        tref_log_metrics_weights_and_factors
                    WHERE (((tref_log_metrics_weights_and_factors.metric_weight)<>0))
                ) as metric_base

    )  AS log_metric_N;

Upvotes: 1

Views: 64

Answers (1)

acr_scout
acr_scout

Reputation: 579

@HansUp you were exactly right on. I forgot all about the Domain functions and they work perfectly without the complexity. Below is the resultant SQL statement.

SELECT
    tref_log_metrics_weights_and_factors.metric_title,
    DCount("[metric_weight]","tref_log_metrics_weights_and_factors","[metric_weight]<>0") AS metric_count,
    Dsum("[metric_weight]","tref_log_metrics_weights_and_factors") AS metric_sum,
    (([metric_count]*[tref_log_metrics_weights_and_factors].[metric_weight])/[metric_sum]) AS metric_N
FROM
    tref_log_metrics_weights_and_factors

Upvotes: 1

Related Questions