HELP WITH QUERY BY DATE

G

Guest

Here is my SQL view of a query I am running.


SELECT [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue (+)]+[Sum
Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100 AS [Accuracy %], [Sum Of
SumOfTrue (+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100 AS
[Sensitivity %], 100-(([Sum Of SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]))*100) AS [Specificity %], Year([Total Findings Query].[Date
By Month])*12+DatePart('m',[Total Findings Query].[Date By Month])-1 AS Expr1
FROM [Total Findings Query], MPI INNER JOIN [TRUE/FALSE Findings] ON
MPI.Date = [TRUE/FALSE Findings].Date
GROUP BY [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue
(+)]+[Sum Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue
(-)]+[Sum Of SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100, [Sum Of SumOfTrue
(+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100, 100-(([Sum Of
SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum Of SumOfFalse (+)]))*100),
Year([Total Findings Query].[Date By Month])*12+DatePart('m',[Total Findings
Query].[Date By Month])-1
ORDER BY Year([Total Findings Query].[Date By Month])*12+DatePart('m',[Total
Findings Query].[Date By Month])-1;

RESULTS SHOW AS:
Date By Month Accuracy % Sensitivity % Specificity % Expr1
January 2003 81.08 96.77 100.00 24036


I would like to add to this query a new field called # OF PATIENT'S CATH'D.
I have a query that I use for a monthly total:

SELECT [Total Findings for January].[SumOfTrue (+)], [Total Findings for
January].[SumOfTrue (-)], [Total Findings for January].[SumOfFalse (+)],
[Total Findings for January].[SumOfFalse (-)], [Total Findings for
January].[SumOf# of Patient's seen], ([SumOfTrue (+)]+[SumOfTrue
(-)]+[SumOfFalse (+)]+[SumOfFalse (-)])/[SumOf# of Patient's seen] AS [Total
Cath'd]
FROM [Total Findings for January];

RESULTS SHOW:
SumOfTrue (+) SumOfTrue (-) SumOfFalse (+) SumOfFalse (-) SumOf# of
Patient's seen Total Cath'd
30 0 6 1 165 0.22

But I would like to add the field Total Cath'd to the first query so that
it will list the result by month:

So it will show as

Date By Month Accuracy % Sensitivity % Specificity % Total Cath'd
January 2003 81.08 96.77 100.00 22%
 
G

Guest

When I tried to write the query myself this is my SQL view:

SELECT [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue (+)]+[Sum
Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100 AS [Accuracy %], [Sum Of
SumOfTrue (+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100 AS
[Sensitivity %], 100-(([Sum Of SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]))*100) AS [Specificity %], ([Sum Of SumOfTrue (+)]+[Sum Of
SumOfTrue (-)]+[Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]+[Sum Of SumOfFalse (-)])/[MPI]![# of Patient's seen] AS
[Total Cath'd], Year([Total Findings Query].[Date By
Month])*12+DatePart('m',[Total Findings Query].[Date By Month])-1 AS Expr1
FROM [Total Findings Query], MPI INNER JOIN [TRUE/FALSE Findings] ON
MPI.Date = [TRUE/FALSE Findings].Date
GROUP BY [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue
(+)]+[Sum Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue
(-)]+[Sum Of SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100, [Sum Of SumOfTrue
(+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100, 100-(([Sum Of
SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum Of SumOfFalse (+)]))*100), ([Sum
Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)]+[Sum Of SumOfTrue (+)]+[Sum Of
SumOfTrue (-)]+[Sum Of SumOfFalse (+)]+[Sum Of SumOfFalse (-)])/[MPI]![# of
Patient's seen], Year([Total Findings Query].[Date By
Month])*12+DatePart('m',[Total Findings Query].[Date By Month])-1
ORDER BY Year([Total Findings Query].[Date By Month])*12+DatePart('m',[Total
Findings Query].[Date By Month])-1;

BUT THIS IS MY RESULTS:

Date By Month Accuracy % Sensitivity % Specificity % Total Cath'd Expr1
January 2003 81.08 96.77 100.00 24036
January 2003 81.08 96.77 100.00 0.406060606060606 24036
January 2003 81.08 96.77 100.00 0.458904109589041 24036
January 2003 81.08 96.77 100.00 0.496296296296296 24036

I should only have one value for the ENTIRE month of January.

Thanks for any help,

Kate said:
Here is my SQL view of a query I am running.


SELECT [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue (+)]+[Sum
Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100 AS [Accuracy %], [Sum Of
SumOfTrue (+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100 AS
[Sensitivity %], 100-(([Sum Of SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]))*100) AS [Specificity %], Year([Total Findings Query].[Date
By Month])*12+DatePart('m',[Total Findings Query].[Date By Month])-1 AS Expr1
FROM [Total Findings Query], MPI INNER JOIN [TRUE/FALSE Findings] ON
MPI.Date = [TRUE/FALSE Findings].Date
GROUP BY [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue
(+)]+[Sum Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue
(-)]+[Sum Of SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100, [Sum Of SumOfTrue
(+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100, 100-(([Sum Of
SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum Of SumOfFalse (+)]))*100),
Year([Total Findings Query].[Date By Month])*12+DatePart('m',[Total Findings
Query].[Date By Month])-1
ORDER BY Year([Total Findings Query].[Date By Month])*12+DatePart('m',[Total
Findings Query].[Date By Month])-1;

RESULTS SHOW AS:
Date By Month Accuracy % Sensitivity % Specificity % Expr1
January 2003 81.08 96.77 100.00 24036


I would like to add to this query a new field called # OF PATIENT'S CATH'D.
I have a query that I use for a monthly total:

SELECT [Total Findings for January].[SumOfTrue (+)], [Total Findings for
January].[SumOfTrue (-)], [Total Findings for January].[SumOfFalse (+)],
[Total Findings for January].[SumOfFalse (-)], [Total Findings for
January].[SumOf# of Patient's seen], ([SumOfTrue (+)]+[SumOfTrue
(-)]+[SumOfFalse (+)]+[SumOfFalse (-)])/[SumOf# of Patient's seen] AS [Total
Cath'd]
FROM [Total Findings for January];

RESULTS SHOW:
SumOfTrue (+) SumOfTrue (-) SumOfFalse (+) SumOfFalse (-) SumOf# of
Patient's seen Total Cath'd
30 0 6 1 165 0.22

But I would like to add the field Total Cath'd to the first query so that
it will list the result by month:

So it will show as

Date By Month Accuracy % Sensitivity % Specificity % Total Cath'd
January 2003 81.08 96.77 100.00 22%
Click to expand...
 
D

Duane Hookom

You clearly have unique values in one of your columns that you are grouping
by.

I tried to make some sense of all this by pasting your sql into Word and
then formatting. In the future, you might want to avoid spaces and symbols
in object names. Also "Date" is a function so I would never use it as a
field name.

--
Duane Hookom
MS Access MVP
--

Kate said:
When I tried to write the query myself this is my SQL view:

SELECT [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue
(+)]+[Sum
Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100 AS [Accuracy %], [Sum Of
SumOfTrue (+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100 AS
[Sensitivity %], 100-(([Sum Of SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum
Of
SumOfFalse (+)]))*100) AS [Specificity %], ([Sum Of SumOfTrue (+)]+[Sum Of
SumOfTrue (-)]+[Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]+[Sum Of SumOfFalse (-)])/[MPI]![# of Patient's seen] AS
[Total Cath'd], Year([Total Findings Query].[Date By
Month])*12+DatePart('m',[Total Findings Query].[Date By Month])-1 AS Expr1
FROM [Total Findings Query], MPI INNER JOIN [TRUE/FALSE Findings] ON
MPI.Date = [TRUE/FALSE Findings].Date
GROUP BY [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue
(+)]+[Sum Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue
(-)]+[Sum Of SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100, [Sum Of
SumOfTrue
(+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100, 100-(([Sum Of
SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum Of SumOfFalse (+)]))*100),
([Sum
Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)]+[Sum Of SumOfTrue (+)]+[Sum Of
SumOfTrue (-)]+[Sum Of SumOfFalse (+)]+[Sum Of SumOfFalse (-)])/[MPI]![#
of
Patient's seen], Year([Total Findings Query].[Date By
Month])*12+DatePart('m',[Total Findings Query].[Date By Month])-1
ORDER BY Year([Total Findings Query].[Date By
Month])*12+DatePart('m',[Total
Findings Query].[Date By Month])-1;

BUT THIS IS MY RESULTS:

Date By Month Accuracy % Sensitivity % Specificity % Total Cath'd Expr1
January 2003 81.08 96.77 100.00 24036
January 2003 81.08 96.77 100.00 0.406060606060606 24036
January 2003 81.08 96.77 100.00 0.458904109589041 24036
January 2003 81.08 96.77 100.00 0.496296296296296 24036

I should only have one value for the ENTIRE month of January.

Thanks for any help,

Kate said:
Here is my SQL view of a query I am running.


SELECT [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue
(+)]+[Sum
Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue (-)]+[Sum Of
SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100 AS [Accuracy %], [Sum Of
SumOfTrue (+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100 AS
[Sensitivity %], 100-(([Sum Of SumOfTrue (-)]/([Sum Of SumOfTrue
(-)]+[Sum Of
SumOfFalse (+)]))*100) AS [Specificity %], Year([Total Findings
Query].[Date
By Month])*12+DatePart('m',[Total Findings Query].[Date By Month])-1 AS
Expr1
FROM [Total Findings Query], MPI INNER JOIN [TRUE/FALSE Findings] ON
MPI.Date = [TRUE/FALSE Findings].Date
GROUP BY [Total Findings Query].[Date By Month], ([Sum Of SumOfTrue
(+)]+[Sum Of SumOfTrue (-)])/([Sum Of SumOfTrue (+)]+[Sum Of SumOfTrue
(-)]+[Sum Of SumOfFalse (+)]+[Sum Of SumOfFalse (-)])*100, [Sum Of
SumOfTrue
(+)]/([Sum Of SumOfTrue (+)]+[Sum Of SumOfFalse (-)])*100, 100-(([Sum Of
SumOfTrue (-)]/([Sum Of SumOfTrue (-)]+[Sum Of SumOfFalse (+)]))*100),
Year([Total Findings Query].[Date By Month])*12+DatePart('m',[Total
Findings
Query].[Date By Month])-1
ORDER BY Year([Total Findings Query].[Date By
Month])*12+DatePart('m',[Total
Findings Query].[Date By Month])-1;

RESULTS SHOW AS:
Date By Month Accuracy % Sensitivity % Specificity % Expr1
January 2003 81.08 96.77 100.00 24036


I would like to add to this query a new field called # OF PATIENT'S
CATH'D.
I have a query that I use for a monthly total:

SELECT [Total Findings for January].[SumOfTrue (+)], [Total Findings for
January].[SumOfTrue (-)], [Total Findings for January].[SumOfFalse (+)],
[Total Findings for January].[SumOfFalse (-)], [Total Findings for
January].[SumOf# of Patient's seen], ([SumOfTrue (+)]+[SumOfTrue
(-)]+[SumOfFalse (+)]+[SumOfFalse (-)])/[SumOf# of Patient's seen] AS
[Total
Cath'd]
FROM [Total Findings for January];

RESULTS SHOW:
SumOfTrue (+) SumOfTrue (-) SumOfFalse (+) SumOfFalse (-) SumOf# of
Patient's seen Total Cath'd
30 0 6 1 165 0.22

But I would like to add the field Total Cath'd to the first query so
that
it will list the result by month:

So it will show as

Date By Month Accuracy % Sensitivity % Specificity % Total Cath'd
January 2003 81.08 96.77 100.00 22%
Click to expand...
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

division by zero 5
division by zero-PLEASE HELP 5
divison by zero 2
Union query not displaying select statement 1
Crosstab query help please 1
SUM & COUNT Functions are duplicating records in calculation 1
Troubleshoot A Query 2
cross tabg query 1

Top