Expressions for an SQL Query

[saraann422]

Can you use an expression, in a SELECT statement in an Access Query that would take (from the following example) the first Select statement and divide it by the second?

Thanks!
Sarah

SELECT (Count([Date/Time])*5) AS QADateTimePR,

(SELECT (Count([Date/Time])*5) FROM [QC Information] WHERE [Analyst]=[First Last] AND ([Date/Time] IN ('1','2')) AND [Today's Date] Between [StartDate] AND [EndDate] AND ([Case Status] IN ('Expedite/Re-QC','Expedite','Re-QC','New'))) AS QADateTimePT,

FROM [QC Information]
WHERE [Analyst]=[First Last] AND [Date/Time]="1" AND [Today's Date] Between [StartDate] AND [EndDate] AND ([Case Status] IN ('Expedite/Re-QC','Expedite','Re-QC','New'));

 

[lameid]

Assuming it runs already (Access can be peculiar about sub-queries)...

I would try adding a field/column that does the arithmetic on the aliases (don't try that in SQL Server).

SELECT (Count([Date/Time])*5) AS QADateTimePR,

(SELECT (Count([Date/Time])*5) FROM [QC Information] WHERE [Analyst]=[First Last] AND ([Date/Time] IN ('1','2')) AND [Today's Date] Between [StartDate] AND [EndDate] AND ([Case Status] IN ('Expedite/Re-QC','Expedite','Re-QC','New'))) AS QADateTimePT,
[b][COLOR=red]QADateTimePR / QADateTimePT As YourColumnNameHere[/color][/b]
FROM [QC Information]
WHERE [Analyst]=[First Last] AND [Date/Time]="1" AND [Today's Date] Between [StartDate] AND [EndDate] AND ([Case Status] IN ('Expedite/Re-QC','Expedite','Re-QC','New'));

Although that might not work and you'll have to repeat both expressions in the next column.

Beyond that I am having a hard time figuring out what are the parameters and what are the fields in the where clauses.

Depending on what you are doing, the Dcount function might work well too.

It looks like the tables may be related by person's name in the analyst field.

The way I normally do this in Access is to have two separate queries that group on the related fields and summarize the data I want in each. Then I put both queries in a third (in the from clause) and join on the related field (analyst?) and return fields or do math there. I hope this makes sense. This way you can use the query designer the whole way and never have to look at the SQL.