Fancy Crosstab Try Again!

S

Susy

I tabbed right out of this and into send before I was
finished!

I'd like to know if I can add another value to a crosstab
query in which I find min and max values for each year.

This is what the data look like:

Well Name Latitude Longitude Date GW
Adams 1000000 -100000 1/1/1999 0.212
Baker 2000000 -200000 3/5/1999 0.969
Charley 3000000 -300000 2/1/1999 0.4556
Adams 1000000 -100000 1/1/1999 0.333
Baker 2000000 -200000 1/2/1999 0.45
Charley 3000000 -300000 3/1/1999 0.7
Adams 1000000 -100000 1/5/1999 0.5
Baker 2000000 -200000 5/3/1999 0.22
Charley 3000000 -300000 7/6/1999 0.875
Adams 1000000 -100000 1/28/1999 0.2345
Baker 2000000 -200000 2/5/1999 0.5
Charley 3000000 -300000 6/1/1999 0.333


Here's my crosstab (I made one for min and one for max):
Minimum 1999
Adams 1000000 -100000 0.21
Baker 2000000 -200000 0.22
Charley 3000000 -300000 0.33

Can I add the date of each min (or max) to the crosstab?
Or do I need to create another query?

I appreciate any help.
 
J

John Viescas

Getting the dates will be tricky. You don't need a crosstab. Start with
this basic query:

qryMinMax:
SELECT [Well Name], Latitude, Longitude, Min(GW) As MinGW, Max(GW) As MaxGW
FROM MyTable
GROUP BY [Well Name], Latitude, Longitude

Now add the dates with subqueries:

SELECT [Well Name], Latitude, Longitude, MinGW,
(SELECT Max([Date]) FROM MyTable As M2
WHERE M2.[Well Name] = qryMinMax.[Well Name]
AND M2.Latitude = qryMinMax.Latitude
AND M2.Longitude = qryMinMax.Longitude
AND M2.GW = qryMinMax.MinGW) As MinDate, MaxGW,
(SELECT Max([Date]) FROM MyTable As M3
WHERE M3.[Well Name] = qryMinMax.[Well Name]
AND M3.Latitude = qryMinMax.Latitude
AND M3.Longitude = qryMinMax.Longitude
AND M3.GW = qryMinMax.MaxGW) As MaxDate
FROM qryMinMax

This will return the "latest" dates at which the min or max occurred. Use
MIN in the subqueries if you want the "earliest" dates.
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
S

Susy

John:

It's brilliant and it worked perfectly - but it's not
exactly what I needed. My fault. I didn't state my needs
as completely as I should have. Here's what I need:

1999 2000
Min Date Min Date
Adams 1000000 -100000 0.21 1/1/99 0.11 1/1/00
Baker 2000000 -200000 0.21 1/1/99 0.11 1/1/00
Charley 3000000 -300000 0.21 1/1/99 0.11 1/1/00

Can I do this with qryMinMax? It gives me the Min and Max
for the whole data set and I need to break it out by year.
On a report maybe?

Thanks, Susy
-----Original Message-----
Getting the dates will be tricky. You don't need a crosstab. Start with
this basic query:

qryMinMax:
SELECT [Well Name], Latitude, Longitude, Min(GW) As MinGW, Max(GW) As MaxGW
FROM MyTable
GROUP BY [Well Name], Latitude, Longitude

Now add the dates with subqueries:

SELECT [Well Name], Latitude, Longitude, MinGW,
(SELECT Max([Date]) FROM MyTable As M2
WHERE M2.[Well Name] = qryMinMax.[Well Name]
AND M2.Latitude = qryMinMax.Latitude
AND M2.Longitude = qryMinMax.Longitude
AND M2.GW = qryMinMax.MinGW) As MinDate, MaxGW,
(SELECT Max([Date]) FROM MyTable As M3
WHERE M3.[Well Name] = qryMinMax.[Well Name]
AND M3.Latitude = qryMinMax.Latitude
AND M3.Longitude = qryMinMax.Longitude
AND M3.GW = qryMinMax.MaxGW) As MaxDate
FROM qryMinMax

This will return the "latest" dates at which the min or max occurred. Use
MIN in the subqueries if you want the "earliest" dates.
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I tabbed right out of this and into send before I was
finished!

I'd like to know if I can add another value to a crosstab
query in which I find min and max values for each year.

This is what the data look like:

Well Name Latitude Longitude Date GW
Adams 1000000 -100000 1/1/1999 0.212
Baker 2000000 -200000 3/5/1999 0.969
Charley 3000000 -300000 2/1/1999 0.4556
Adams 1000000 -100000 1/1/1999 0.333
Baker 2000000 -200000 1/2/1999 0.45
Charley 3000000 -300000 3/1/1999 0.7
Adams 1000000 -100000 1/5/1999 0.5
Baker 2000000 -200000 5/3/1999 0.22
Charley 3000000 -300000 7/6/1999 0.875
Adams 1000000 -100000 1/28/1999 0.2345
Baker 2000000 -200000 2/5/1999 0.5
Charley 3000000 -300000 6/1/1999 0.333


Here's my crosstab (I made one for min and one for max):
Minimum 1999
Adams 1000000 -100000 0.21
Baker 2000000 -200000 0.22
Charley 3000000 -300000 0.33

Can I add the date of each min (or max) to the crosstab?
Or do I need to create another query?

I appreciate any help.
Click to expand...


.
Click to expand...
 
J

John Viescas

Hmmm. Try this for starters:

qryMinMaxYr:
SELECT [Well Name], Latitude, Longitude, Year([Date]) As WellYear, Min(GW)
As MinGW, Max(GW) As MaxGW
FROM MyTable
GROUP BY [Well Name], Latitude, Longitude, Year([Date])

Now add the dates with subqueries:

SELECT [Well Name], Latitude, Longitude, MinGW, WellYear,
(SELECT Max([Date]) FROM MyTable As M2
WHERE M2.[Well Name] = qryMinMaxYr.[Well Name]
AND M2.Latitude = qryMinMaxYr.Latitude
AND M2.Longitude = qryMinMaxYr.Longitude
AND Year(M2.[Date]) = qryMinMaxYr.WellYear
AND M2.GW = qryMinMaxYr.MinGW) As MinDate, MaxGW,
(SELECT Max([Date]) FROM MyTable As M3
WHERE M3.[Well Name] = qryMinMaxYr.[Well Name]
AND M3.Latitude = qryMinMaxYr.Latitude
AND M3.Longitude = qryMinMaxYr.Longitude
AND Year(M3.[Date]) = qryMinMaxYr.WellYear
AND M3.GW = qryMinMax.MaxGW) As MaxDate
FROM qryMinMaxYr

That will get you something like:

Well Latitude Longitude Year Min MinDate Max MaxDate
Adams 1000000 -100000 1999 0.21 1/1/99 0.45 4/30/99
Adams 1000000 -100000 2000 0.11 1/1/00 0.40 5/1/00
Baker 2000000 -200000 1999 0.21 1/1/99 0.85 6/1/99
Baker 2000000 -200000 2000 0.11 1/1/00 0.70 8/5/00
Charley 3000000 -300000 1999 0.21 1/1/99 0.35 2/15/99
Charley 3000000 -300000 2000 0.11 1/1/00 0.68 10/1/00

But I can't see an easy way to Pivot that on Year in a Crosstab because you
have multiple "Values" that you want to display for each year.

Does that get you closer?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Susy said:
John:

It's brilliant and it worked perfectly - but it's not
exactly what I needed. My fault. I didn't state my needs
as completely as I should have. Here's what I need:

1999 2000
Min Date Min Date
Adams 1000000 -100000 0.21 1/1/99 0.11 1/1/00
Baker 2000000 -200000 0.21 1/1/99 0.11 1/1/00
Charley 3000000 -300000 0.21 1/1/99 0.11 1/1/00

Can I do this with qryMinMax? It gives me the Min and Max
for the whole data set and I need to break it out by year.
On a report maybe?

Thanks, Susy
-----Original Message-----
Getting the dates will be tricky. You don't need a crosstab. Start with
this basic query:

qryMinMax:
SELECT [Well Name], Latitude, Longitude, Min(GW) As MinGW, Max(GW) As MaxGW
FROM MyTable
GROUP BY [Well Name], Latitude, Longitude

Now add the dates with subqueries:

SELECT [Well Name], Latitude, Longitude, MinGW,
(SELECT Max([Date]) FROM MyTable As M2
WHERE M2.[Well Name] = qryMinMax.[Well Name]
AND M2.Latitude = qryMinMax.Latitude
AND M2.Longitude = qryMinMax.Longitude
AND M2.GW = qryMinMax.MinGW) As MinDate, MaxGW,
(SELECT Max([Date]) FROM MyTable As M3
WHERE M3.[Well Name] = qryMinMax.[Well Name]
AND M3.Latitude = qryMinMax.Latitude
AND M3.Longitude = qryMinMax.Longitude
AND M3.GW = qryMinMax.MaxGW) As MaxDate
FROM qryMinMax

This will return the "latest" dates at which the min or max occurred. Use
MIN in the subqueries if you want the "earliest" dates.
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I tabbed right out of this and into send before I was
finished!

I'd like to know if I can add another value to a crosstab
query in which I find min and max values for each year.

This is what the data look like:

Well Name Latitude Longitude Date GW
Adams 1000000 -100000 1/1/1999 0.212
Baker 2000000 -200000 3/5/1999 0.969
Charley 3000000 -300000 2/1/1999 0.4556
Adams 1000000 -100000 1/1/1999 0.333
Baker 2000000 -200000 1/2/1999 0.45
Charley 3000000 -300000 3/1/1999 0.7
Adams 1000000 -100000 1/5/1999 0.5
Baker 2000000 -200000 5/3/1999 0.22
Charley 3000000 -300000 7/6/1999 0.875
Adams 1000000 -100000 1/28/1999 0.2345
Baker 2000000 -200000 2/5/1999 0.5
Charley 3000000 -300000 6/1/1999 0.333


Here's my crosstab (I made one for min and one for max):
Minimum 1999
Adams 1000000 -100000 0.21
Baker 2000000 -200000 0.22
Charley 3000000 -300000 0.33

Can I add the date of each min (or max) to the crosstab?
Or do I need to create another query?

I appreciate any help.
Click to expand...


.
Click to expand...
Click to expand...
 
S

Susy

Yes, you solved the main problem for me! With dates
attached to the max and min values, I could get the report
to work - SOMEHOW.

My workaround is clumsy but effective.

1) I created a subquery for each year as a make-table (I
didn't do this at first but running the report with
queries took FOREVER).

2) I created a subreport from each new table with Max, Max
Date, Min, and Min Date for that year.

3) I put them all into a main report with the Well Name,
Lat, and Long (and headings for each year as well) and
linked them by Well Name.

I need to fiddle with the format a bit but basically I'm
there. Thanks so much!

Susy
-----Original Message-----
Hmmm. Try this for starters:

qryMinMaxYr:
SELECT [Well Name], Latitude, Longitude, Year([Date]) As WellYear, Min(GW)
As MinGW, Max(GW) As MaxGW
FROM MyTable
GROUP BY [Well Name], Latitude, Longitude, Year([Date])

Now add the dates with subqueries:

SELECT [Well Name], Latitude, Longitude, MinGW, WellYear,
(SELECT Max([Date]) FROM MyTable As M2
WHERE M2.[Well Name] = qryMinMaxYr.[Well Name]
AND M2.Latitude = qryMinMaxYr.Latitude
AND M2.Longitude = qryMinMaxYr.Longitude
AND Year(M2.[Date]) = qryMinMaxYr.WellYear
AND M2.GW = qryMinMaxYr.MinGW) As MinDate, MaxGW,
(SELECT Max([Date]) FROM MyTable As M3
WHERE M3.[Well Name] = qryMinMaxYr.[Well Name]
AND M3.Latitude = qryMinMaxYr.Latitude
AND M3.Longitude = qryMinMaxYr.Longitude
AND Year(M3.[Date]) = qryMinMaxYr.WellYear
AND M3.GW = qryMinMax.MaxGW) As MaxDate
FROM qryMinMaxYr

That will get you something like:

Well Latitude Longitude Year Min MinDate Max MaxDate
Adams 1000000 -100000 1999 0.21 1/1/99 0.45 4/30/99
Adams 1000000 -100000 2000 0.11 1/1/00 0.40 5/1/00
Baker 2000000 -200000 1999 0.21 1/1/99 0.85 6/1/99
Baker 2000000 -200000 2000 0.11 1/1/00 0.70 8/5/00
Charley 3000000 -300000 1999 0.21 1/1/99 0.35 2/15/99
Charley 3000000 -300000 2000 0.11 1/1/00 0.68 10/1/00

But I can't see an easy way to Pivot that on Year in a Crosstab because you
have multiple "Values" that you want to display for each year.

Does that get you closer?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John:

It's brilliant and it worked perfectly - but it's not
exactly what I needed. My fault. I didn't state my needs
as completely as I should have. Here's what I need:

1999 2000
Min Date Min Date
Adams 1000000 -100000 0.21 1/1/99 0.11 1/1/00
Baker 2000000 -200000 0.21 1/1/99 0.11 1/1/00
Charley 3000000 -300000 0.21 1/1/99 0.11 1/1/00

Can I do this with qryMinMax? It gives me the Min and Max
for the whole data set and I need to break it out by year.
On a report maybe?

Thanks, Susy
-----Original Message-----
Getting the dates will be tricky. You don't need a crosstab. Start with
this basic query:

qryMinMax:
SELECT [Well Name], Latitude, Longitude, Min(GW) As MinGW, Max(GW) As MaxGW
FROM MyTable
GROUP BY [Well Name], Latitude, Longitude

Now add the dates with subqueries:

SELECT [Well Name], Latitude, Longitude, MinGW,
(SELECT Max([Date]) FROM MyTable As M2
WHERE M2.[Well Name] = qryMinMax.[Well Name]
AND M2.Latitude = qryMinMax.Latitude
AND M2.Longitude = qryMinMax.Longitude
AND M2.GW = qryMinMax.MinGW) As MinDate, MaxGW,
(SELECT Max([Date]) FROM MyTable As M3
WHERE M3.[Well Name] = qryMinMax.[Well Name]
AND M3.Latitude = qryMinMax.Latitude
AND M3.Longitude = qryMinMax.Longitude
AND M3.GW = qryMinMax.MaxGW) As MaxDate
FROM qryMinMax

This will return the "latest" dates at which the min or max occurred. Use
MIN in the subqueries if you want the "earliest" dates.
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I tabbed right out of this and into send before I was
finished!

I'd like to know if I can add another value to a crosstab
query in which I find min and max values for each year.

This is what the data look like:

Well Name Latitude Longitude Date GW
Adams 1000000 -100000 1/1/1999 0.212
Baker 2000000 -200000 3/5/1999 0.969
Charley 3000000 -300000 2/1/1999 0.4556
Adams 1000000 -100000 1/1/1999 0.333
Baker 2000000 -200000 1/2/1999 0.45
Charley 3000000 -300000 3/1/1999 0.7
Adams 1000000 -100000 1/5/1999 0.5
Baker 2000000 -200000 5/3/1999 0.22
Charley 3000000 -300000 7/6/1999 0.875
Adams 1000000 -100000 1/28/1999 0.2345
Baker 2000000 -200000 2/5/1999 0.5
Charley 3000000 -300000 6/1/1999 0.333


Here's my crosstab (I made one for min and one for max):
Minimum 1999
Adams 1000000 -100000 0.21
Baker 2000000 -200000 0.22
Charley 3000000 -300000 0.33

Can I add the date of each min (or max) to the crosstab?
Or do I need to create another query?

I appreciate any help.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Top