Query Problem

G

Guest

I have a query that I have created mostly in the design view but some in the
SQL view as well. What it is meant to do is extract the Max Date for a number
of wells that were sampled in a month of a certain quarter. When I look for
just the dates I get the correct number of results 22 for the last day of the
month I enter. A month must be entered as one of the parameters. BUT when I
add the additional column to get the reading I get 55 results, one for each
well for every day it was sampled, except the first one! I only want the
results from the last day! I am pasting the SQL query in hopes that someone
can tell me why this is happenning.

SELECT [MW Gauging].[Well ID], Max([MW Gauging].[Gauging Date]) AS
[MaxOfGauging Date], DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]) AS Quarter, [MW Gauging].[Totalizer Reading]
FROM [MW Gauging]
WHERE
(((DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1]
Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1]))
GROUP BY [MW Gauging].[Well ID], DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]), [MW Gauging].[Totalizer Reading]
HAVING ((([MW Gauging].[Well ID])="AR-1") AND ((DatePart("q",[Gauging Date])
& "Q" & DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR
((([MW Gauging].[Well ID])="AR-2") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="AR-3") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="AR-4") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-1") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-2") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-3") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-4") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-5") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-11") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-12") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-13") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-14") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-1A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-2A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-3A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-4A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-5") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-7") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-8") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-9") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-9.1") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1]));

Hope this makes sense.
 
G

Guest

You are grouping by the Totalizer Reading column so will get a row returned
for every distinct value of that column. To get this value but still
restrict the return set to the last date of the selected month you'd need to
use a correlated subquery in the outer query's WHERE clause to find the
latest date of the month for the current well. To restrict the query to the
list of well ID's you can use the IN operator with a value list.

Try this:

PARAMETERS [Enter Month 1] SHORT, [Enter Year for Month 1] SHORT;
SELECT [Well ID], [Gauging Date]),
DATEPART("q",[Gauging Date]) & "Q" & DATEPART("yyyy",[Gauging Date]) AS
Quarter,
[Totalizer Reading]
FROM [MW Gauging] AS MWG1
WHERE [Well ID] IN("AR-1", "AR-2", <and so on to> "RW-9.1")
AND [Gauging Date] =
(SELECT MAX([Gauging Date])
FROM [MW Gauging] AS MWG2
WHERE MWG2.[Well ID] = MWG1.[Well ID]
AND MONTH([Gauging Date]) = [Enter Month 1]
AND YEAR(Gauging Date]) = [Enter Year for Month 1]);

Ken Sheridan
Stafford, England

Larry G. said:
I have a query that I have created mostly in the design view but some in the
SQL view as well. What it is meant to do is extract the Max Date for a number
of wells that were sampled in a month of a certain quarter. When I look for
just the dates I get the correct number of results 22 for the last day of the
month I enter. A month must be entered as one of the parameters. BUT when I
add the additional column to get the reading I get 55 results, one for each
well for every day it was sampled, except the first one! I only want the
results from the last day! I am pasting the SQL query in hopes that someone
can tell me why this is happenning.

SELECT [MW Gauging].[Well ID], Max([MW Gauging].[Gauging Date]) AS
[MaxOfGauging Date], DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]) AS Quarter, [MW Gauging].[Totalizer Reading]
FROM [MW Gauging]
WHERE
(((DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1]
Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1]))
GROUP BY [MW Gauging].[Well ID], DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]), [MW Gauging].[Totalizer Reading]
HAVING ((([MW Gauging].[Well ID])="AR-1") AND ((DatePart("q",[Gauging Date])
& "Q" & DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR
((([MW Gauging].[Well ID])="AR-2") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="AR-3") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="AR-4") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-1") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-2") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-3") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-4") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-5") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-11") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-12") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-13") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-14") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-1A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-2A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-3A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-4A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-5") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-7") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-8") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-9") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-9.1") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1]));

Hope this makes sense.
Click to expand...
 
G

Guest

never mind - I am AN IDIOT! I found the problem...
--
Never give up, the answer IS out there, it just takes a while to find it
sometimes!


Larry G. said:
I have a query that I have created mostly in the design view but some in the
SQL view as well. What it is meant to do is extract the Max Date for a number
of wells that were sampled in a month of a certain quarter. When I look for
just the dates I get the correct number of results 22 for the last day of the
month I enter. A month must be entered as one of the parameters. BUT when I
add the additional column to get the reading I get 55 results, one for each
well for every day it was sampled, except the first one! I only want the
results from the last day! I am pasting the SQL query in hopes that someone
can tell me why this is happenning.

SELECT [MW Gauging].[Well ID], Max([MW Gauging].[Gauging Date]) AS
[MaxOfGauging Date], DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]) AS Quarter, [MW Gauging].[Totalizer Reading]
FROM [MW Gauging]
WHERE
(((DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1]
Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1]))
GROUP BY [MW Gauging].[Well ID], DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]), [MW Gauging].[Totalizer Reading]
HAVING ((([MW Gauging].[Well ID])="AR-1") AND ((DatePart("q",[Gauging Date])
& "Q" & DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR
((([MW Gauging].[Well ID])="AR-2") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="AR-3") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="AR-4") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-1") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-2") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-3") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-4") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-5") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-11") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-12") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-13") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-14") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-1A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-2A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-3A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-4A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-5") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-7") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-8") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-9") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-9.1") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1]));

Hope this makes sense.
Click to expand...
 
G

Guest

I hope your solution wasn't to use MAX([Totalizer Reading]). This is a common
mistake made in similar scenarios in my experience. It does not return the
reading for the latest date, but the highest reading, which may well be on a
completely different date to the latest date.

Ken Sheridan
Stafford, England
 
S

strive4peace

Hi Larry,

Try this:

SELECT
[Well ID],
[Gauging Date],
Format([Gauging Date],"q\Qyyyy") AS Quarter,
[Totalizer Reading]
FROM [MW Gauging]
WHERE
(
(
[MW Gauging].[Gauging Date] =
dMax("[Gauging Date]",
"[MW Gauging]",
"[Well ID]='" & [Well ID]
& "' AND Month([Gauging Date])="
& [Enter Month 1]
& " AND Format([Gauging Date],'q\Qyyyy') = '"
& [Enter Quarter for Month 1] & "'" )
)
AND
(
[MW Gauging].[Well ID] IN
(
"AR-1", "AR-2",
"AR-3","AR-4", "L-1", "L-2", "L-3",
"L-4", "L-5", "RW-11", "RW-12",
"RW-13", "RW-14", "RW-1A", "RW-2A",
"RW-3A", "RW-4A", "RW-5", "RW-7",
"RW-8","RW-9","RW-9.1"
)
)
);

Hope I got everything balanced!

Instead of using dMax to return the latest date for each
Well_ID, you could also use a subquery, which is faster, but
this is a good technique to know and easier to use.

DatePart returns an integer but since you are concatenating,
using Format (which returns a string) is less complex.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

I have a query that I have created mostly in the design view but some in the
SQL view as well. What it is meant to do is extract the Max Date for a number
of wells that were sampled in a month of a certain quarter. When I look for
just the dates I get the correct number of results 22 for the last day of the
month I enter. A month must be entered as one of the parameters. BUT when I
add the additional column to get the reading I get 55 results, one for each
well for every day it was sampled, except the first one! I only want the
results from the last day! I am pasting the SQL query in hopes that someone
can tell me why this is happenning.

SELECT [MW Gauging].[Well ID], Max([MW Gauging].[Gauging Date]) AS
[MaxOfGauging Date], DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]) AS Quarter, [MW Gauging].[Totalizer Reading]
FROM [MW Gauging]
WHERE
(((DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1]
Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1] Or
(DatePart("m",[Gauging Date]))=[Enter Month 1] Or (DatePart("m",[Gauging
Date]))=[Enter Month 1] Or (DatePart("m",[Gauging Date]))=[Enter Month 1]))
GROUP BY [MW Gauging].[Well ID], DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]), [MW Gauging].[Totalizer Reading]
HAVING ((([MW Gauging].[Well ID])="AR-1") AND ((DatePart("q",[Gauging Date])
& "Q" & DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR
((([MW Gauging].[Well ID])="AR-2") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="AR-3") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="AR-4") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-1") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-2") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-3") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-4") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="L-5") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-11") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-12") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-13") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-14") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-1A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-2A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-3A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-4A") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-5") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-7") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-8") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-9") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1])) OR ((([MW
Gauging].[Well ID])="RW-9.1") AND ((DatePart("q",[Gauging Date]) & "Q" &
DatePart("yyyy",[Gauging Date]))=[Enter Quarter for Month 1]));

Hope this makes sense.
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

Selecting Min and Max Dates within a given range 1
Cross tab - complex IIF 2
how to add condition to In Clause? 1
#Name? error - Question on Dates 6
Running Sum within Query 4
Retrieving one months data 1
Nulls in a crostab query 2
adding result together 6

Top