TWO Crosstab Query Ouput Questions

You have me laughing at myself because my little mind is mixing a lot more
than just those two posts. I 'm rather compelled to understand this now, but
did decide that my "workaround" was just to add 1000 dummy records. That
wasn't too difficult in Excel for Paste Append into the main Access Table.
By leaving the field that is COUNTed blank, it doesn't harm my results, I
don't think. There might be some impact on other queries, but I'll deal with
them and hope to understand how TO DO this before I have to worry about it.
Deleting those extra records will be easy, which brings me back to showing
you the structure.

First, the table that I am using is "static". The data is updated only once
a year and I will simply replace the table with a new imported one at that
time (due soon though). I did save the Excel source of the extra records if
I need to continue the workaround.

Anyway, the data consists of more fields, but I'll just limit it to the ones
in question here at this point.

ID BAND SPEC BFLY BSTA RFLY RSTA
1 058567904 1290 1 44 1 44
2 058567903 1290 1 44 1 44
3 060795381 1290 1 44 1 59
4 142710239 1290 1 44 1 18
5 142710231 1290 1 44 1 44

These fields are as follows:
ID - Field I let Access add as a Primary Key. Band should have been unique
values, but prone to errors, not sequential, and this matches record # for
adding/deleting.
BAND - Unique (?) value that I normally use to COUNT in crosstab results.
By not putting a value in dummy records, they will not count but will force
the rows to appear.
SPEC - Species ID Number. I am looking at only 17 (been rounding to 16 in
discussion) species. Note that these are four-digit numbers as the past
above ran together with the BAND # and are the last four (eg. 1290).
BFLY - represents the "flyways" where birds were BANDED, an administrative
categorization by USFWS, etc.. I am looking at 1-5, which are the 4 flyways
in the continental US, Alaska, and Canada.
BSTA - This is a two-digit code for the state/province where birds were
BANDED. They should be unique, but there are actually more of these than 2
digits will allow, so the RFLY must be combined as unique identifiers (source
of my problem, perhaps).
RFLY - See BFLY, except that this pertains to where RECOVERED.
RSTA - See BSTA, except that this pertains to where RECOVERED.

The basic crosstab output that I want will show all of the recovery
locations (RFLY+RSTA) for each species (SPEC) as rows with the Banding
locations (BFLY+BSTA) as columns. In other words, it will show how many of
each species were recovered in each location from each source.

Expr1 SPEC Total Of BAND 096 118 121 122
122 1320 13 0 0 0 0 1
122 1326 5 0 0 0 0 5
122 1330 11 0 0 0 0 1
122 1340 0 0 0 0 0 0

The "Expr1" is the combined RFLY+RSTA. In these rows, it means Flyway 1 and
state 22. The SPEC shows the first four species IDs. Total is the count of
all recoveries in that location (D.C.) for each of those species. I have NO
idea what the ""column is and had never noticed it before. I think it came
from the forcing rows with dummy records? It will be easy to delete when
viewed in Excel. The remaining columns (only the first ones shown) indicate
the quantities from each banding location. (By the way, there are several
obvious things that could be done to this table, but there is an Excel
worksheet that performs all of the code lookups and regional filters that I
don't bother to do in Access, although I may in the long run.)

Note that this table does show even the rows with "0" values because of the
dummy records. When I started this question yesterday, it would not have
shown: (1.) The 122/1340 row (2.) The 118 column (3.) The "0" values in the
data. Actually, I think I am only lucky that it is showing all of the
columns because there are SOME recoveries in SOME row (not shown here) for
each. As I learned form other posts, I can force columns, but it would
require statements like the following with values for ALL 62 locations
instead of the years. As it is, I'm happy and adding a few columns in the
Excel output does not compare with adding hundreds of rows that way.

PIVOT WORKTEST.RYR In ("2000","2001","2002","2003","2004","2005");

For the record, the SQL of the current crosstab query looks like this:

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RFLY]+[RSTA] AS Expr1, RECV.SPEC, Count(RECV.[BAND]) AS [Total Of
BAND]
FROM RECV
WHERE (((RECV.SPEC)="1320" Or (RECV.SPEC)="1410" Or ((RECV.SPEC)="1326" Or
(RECV.SPEC)="1420") Or ((RECV.SPEC)="1330" Or (RECV.SPEC)="1430") Or
((RECV.SPEC)="1340" Or (RECV.SPEC)="1440") Or ((RECV.SPEC)="1350" Or
(RECV.SPEC)="1460") Or ((RECV.SPEC)="1370" Or (RECV.SPEC)="1470") Or
((RECV.SPEC)="1390" Or (RECV.SPEC)="1480") Or ((RECV.SPEC)="1400" Or
(RECV.SPEC)="1490") Or (RECV.SPEC)="1500"))
GROUP BY [RFLY]+[RSTA], RECV.SPEC
ORDER BY [RFLY]+[RSTA], RECV.SPEC, [BFLY]+[BSTA]
PIVOT [BFLY]+[BSTA];

The WHERE statement obviously limits the species to the 17 in question and I
will probably do the same for the location fields as a kind of "filter".
Doing this on my home PC with limited resources makes me balk at creating
tables from all of these queries. It is easier to set up different ones from
the "source" data table ("RECV") as required.

That's all there is to the data and problem. I apologize for being dense
concerning the Totals Query. It makes sense to me but it sounds like a
"crosstabs" query in my ignorance and leaves me with the same question of how
to make it show all rows, since each row IS a unique combination of the
values in two fields. I did work on it and created two Tables. One shows
all of the SPEC that I need and one does show all of the locations as the
combined RFLY+RSTA, but, as done, they have no common field to teach me to
Join. Obviously they do, but it is NOT the ones that I want to Join and I am
doubtful it would work (tell me otherwise). I picked the RYR and forced that
year because that was the year that had recoveries in all locations AND in
all species, but NOT all species IN all locations. of course. Maybe the
Joining will be a two-step process and I can use these? I do want my final
output from the RECV table to have every SPEC row from the second table below
shown as a row with every "Expr1" (location) from the first table.

Expr1 RYR SumOfID Total Of ID
118 1940 14343937 14343937
121 1940 8047170 8047170
122 1940 5375352 5375352

SPEC RYR SumOfID Total Of ID
1320 1940 2307862501 2307862501
1326 1940 2036170 2036170
1330 1940 2165790853 2165790853
1340 1940 2367846 2367846

I'm sure that is MORE than enough, but you did suggest that I show the data
and what I wanted. Thank you again for any and all thoughts. I am learning.

Tuffy


"KARL DEWEY" wrote:

You are mixing my post with David's. I did not say to create a table. I said
to create a totals query. I reccomend the totals query over a table in that
a table is static but the query will always have the latest information.

Why don't you post your table structure, a sample of 5-6 records, and what
you want out of the crosstab query.

"TuffyE" wrote:

Karl,

How do I create a table with ALL of those rows. I can/did create a table
with all of the rows that I need to show for EACH value in a field, but
combining them escapes me still.

Tuffy


"KARL DEWEY" wrote:

You can get all of your rows by creating a totals query for your rows and
using it left join to the table in the crosstab query.
Something like this --
TRANSFORM Count(CAROL.Word) AS CountOfWord
SELECT [CAROL NAMES].TITLE, Count(CAROL.Word) AS [Total Of Word]
FROM [CAROL NAMES] LEFT JOIN CAROL ON [CAROL NAMES].SONG = CAROL.Song
WHERE (((CAROL.What) Is Not Null))
GROUP BY [CAROL NAMES].TITLE
PIVOT CAROL.What;

I like your "I will fix this" attitude and your enterprise in your
work-around. Right now I am facing the choice between your query an 50
dancing girls, but I will leave you with something to be going on with:-

WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")

more later.

"TuffyE" wrote in message
...
You have me laughing at myself because my little mind is mixing a lot more
than just those two posts. I 'm rather compelled to understand this now,
but
did decide that my "workaround" was just to add 1000 dummy records. That
wasn't too difficult in Excel for Paste Append into the main Access Table.
By leaving the field that is COUNTed blank, it doesn't harm my results, I
don't think. There might be some impact on other queries, but I'll deal
with
them and hope to understand how TO DO this before I have to worry about
it.
Deleting those extra records will be easy, which brings me back to showing
you the structure.

First, the table that I am using is "static". The data is updated only
once
a year and I will simply replace the table with a new imported one at that
time (due soon though). I did save the Excel source of the extra records
if
I need to continue the workaround.

Anyway, the data consists of more fields, but I'll just limit it to the
ones
in question here at this point.

ID BAND SPEC BFLY BSTA RFLY RSTA
1 058567904 1290 1 44 1 44
2 058567903 1290 1 44 1 44
3 060795381 1290 1 44 1 59
4 142710239 1290 1 44 1 18
5 142710231 1290 1 44 1 44

These fields are as follows:
ID - Field I let Access add as a Primary Key. Band should have been
unique
values, but prone to errors, not sequential, and this matches record # for
adding/deleting.
BAND - Unique (?) value that I normally use to COUNT in crosstab results.
By not putting a value in dummy records, they will not count but will
force
the rows to appear.
SPEC - Species ID Number. I am looking at only 17 (been rounding to 16 in
discussion) species. Note that these are four-digit numbers as the past
above ran together with the BAND # and are the last four (eg. 1290).
BFLY - represents the "flyways" where birds were BANDED, an administrative
categorization by USFWS, etc.. I am looking at 1-5, which are the 4
flyways
in the continental US, Alaska, and Canada.
BSTA - This is a two-digit code for the state/province where birds were
BANDED. They should be unique, but there are actually more of these than
2
digits will allow, so the RFLY must be combined as unique identifiers
(source
of my problem, perhaps).
RFLY - See BFLY, except that this pertains to where RECOVERED.
RSTA - See BSTA, except that this pertains to where RECOVERED.

The basic crosstab output that I want will show all of the recovery
locations (RFLY+RSTA) for each species (SPEC) as rows with the Banding
locations (BFLY+BSTA) as columns. In other words, it will show how many
of
each species were recovered in each location from each source.

Expr1 SPEC Total Of BAND 096 118 121 122
122 1320 13 0 0 0 0 1
122 1326 5 0 0 0 0 5
122 1330 11 0 0 0 0 1
122 1340 0 0 0 0 0 0

The "Expr1" is the combined RFLY+RSTA. In these rows, it means Flyway 1
and
state 22. The SPEC shows the first four species IDs. Total is the count
of
all recoveries in that location (D.C.) for each of those species. I have
NO
idea what the ""column is and had never noticed it before. I think it
came
from the forcing rows with dummy records? It will be easy to delete when
viewed in Excel. The remaining columns (only the first ones shown)
indicate
the quantities from each banding location. (By the way, there are several
obvious things that could be done to this table, but there is an Excel
worksheet that performs all of the code lookups and regional filters that
I
don't bother to do in Access, although I may in the long run.)

Note that this table does show even the rows with "0" values because of
the
dummy records. When I started this question yesterday, it would not have
shown: (1.) The 122/1340 row (2.) The 118 column (3.) The "0" values in
the
data. Actually, I think I am only lucky that it is showing all of the
columns because there are SOME recoveries in SOME row (not shown here) for
each. As I learned form other posts, I can force columns, but it would
require statements like the following with values for ALL 62 locations
instead of the years. As it is, I'm happy and adding a few columns in the
Excel output does not compare with adding hundreds of rows that way.

PIVOT WORKTEST.RYR In ("2000","2001","2002","2003","2004","2005");

For the record, the SQL of the current crosstab query looks like this:

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RFLY]+[RSTA] AS Expr1, RECV.SPEC, Count(RECV.[BAND]) AS [Total Of
BAND]
FROM RECV
WHERE (((RECV.SPEC)="1320" Or (RECV.SPEC)="1410" Or ((RECV.SPEC)="1326" Or
(RECV.SPEC)="1420") Or ((RECV.SPEC)="1330" Or (RECV.SPEC)="1430") Or
((RECV.SPEC)="1340" Or (RECV.SPEC)="1440") Or ((RECV.SPEC)="1350" Or
(RECV.SPEC)="1460") Or ((RECV.SPEC)="1370" Or (RECV.SPEC)="1470") Or
((RECV.SPEC)="1390" Or (RECV.SPEC)="1480") Or ((RECV.SPEC)="1400" Or
(RECV.SPEC)="1490") Or (RECV.SPEC)="1500"))
GROUP BY [RFLY]+[RSTA], RECV.SPEC
ORDER BY [RFLY]+[RSTA], RECV.SPEC, [BFLY]+[BSTA]
PIVOT [BFLY]+[BSTA];

The WHERE statement obviously limits the species to the 17 in question and
I
will probably do the same for the location fields as a kind of "filter".
Doing this on my home PC with limited resources makes me balk at creating
tables from all of these queries. It is easier to set up different ones
from
the "source" data table ("RECV") as required.

That's all there is to the data and problem. I apologize for being dense
concerning the Totals Query. It makes sense to me but it sounds like a
"crosstabs" query in my ignorance and leaves me with the same question of
how
to make it show all rows, since each row IS a unique combination of the
values in two fields. I did work on it and created two Tables. One shows
all of the SPEC that I need and one does show all of the locations as the
combined RFLY+RSTA, but, as done, they have no common field to teach me to
Join. Obviously they do, but it is NOT the ones that I want to Join and I
am
doubtful it would work (tell me otherwise). I picked the RYR and forced
that
year because that was the year that had recoveries in all locations AND in
all species, but NOT all species IN all locations. of course. Maybe the
Joining will be a two-step process and I can use these? I do want my
final
output from the RECV table to have every SPEC row from the second table
below
shown as a row with every "Expr1" (location) from the first table.

Expr1 RYR SumOfID Total Of ID
118 1940 14343937 14343937
121 1940 8047170 8047170
122 1940 5375352 5375352

SPEC RYR SumOfID Total Of ID
1320 1940 2307862501 2307862501
1326 1940 2036170 2036170
1330 1940 2165790853 2165790853
1340 1940 2367846 2367846

I'm sure that is MORE than enough, but you did suggest that I show the
data
and what I wanted. Thank you again for any and all thoughts. I am
learning.

Tuffy


"KARL DEWEY" wrote:

You are mixing my post with David's. I did not say to create a table. I
said
to create a totals query. I reccomend the totals query over a table in
that
a table is static but the query will always have the latest information.

Why don't you post your table structure, a sample of 5-6 records, and
what
you want out of the crosstab query.

"TuffyE" wrote:

Karl,

How do I create a table with ALL of those rows. I can/did create a
table
with all of the rows that I need to show for EACH value in a field, but
combining them escapes me still.

Tuffy


"KARL DEWEY" wrote:

You can get all of your rows by creating a totals query for your rows
and
using it left join to the table in the crosstab query.
Something like this --
TRANSFORM Count(CAROL.Word) AS CountOfWord
SELECT [CAROL NAMES].TITLE, Count(CAROL.Word) AS [Total Of Word]
FROM [CAROL NAMES] LEFT JOIN CAROL ON [CAROL NAMES].SONG = CAROL.Song
WHERE (((CAROL.What) Is Not Null))
GROUP BY [CAROL NAMES].TITLE
PIVOT CAROL.What;

I used David's WHERE IN. The first query is the rollup I mentioned but it
seems I was not clear to you.

RECV_RollUp ----
SELECT [RFLY] & [RSTA] AS RFLY_RSTA, RECV.RFLY, RECV.RSTA
FROM RECV
GROUP BY [RFLY] & [RSTA], RECV.RFLY, RECV.RSTA;

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA] AS [Recovery
Fly - Sta], RECV.SPEC
FROM RECV_RollUp LEFT JOIN RECV ON (RECV_RollUp.RSTA = RECV.RSTA) AND
(RECV_RollUp.RFLY = RECV.RFLY)
WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")
GROUP BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA], RECV.SPEC
ORDER BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA], RECV.SPEC
PIVOT [RECV].[BFLY] & " - " & [RECV].[BSTA];


"David F Cox" wrote:

I like your "I will fix this" attitude and your enterprise in your
work-around. Right now I am facing the choice between your query an 50
dancing girls, but I will leave you with something to be going on with:-

WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")

more later.

"TuffyE" wrote in message
...
You have me laughing at myself because my little mind is mixing a lot more
than just those two posts. I 'm rather compelled to understand this now,
but
did decide that my "workaround" was just to add 1000 dummy records. That
wasn't too difficult in Excel for Paste Append into the main Access Table.
By leaving the field that is COUNTed blank, it doesn't harm my results, I
don't think. There might be some impact on other queries, but I'll deal
with
them and hope to understand how TO DO this before I have to worry about
it.
Deleting those extra records will be easy, which brings me back to showing
you the structure.

First, the table that I am using is "static". The data is updated only
once
a year and I will simply replace the table with a new imported one at that
time (due soon though). I did save the Excel source of the extra records
if
I need to continue the workaround.

Anyway, the data consists of more fields, but I'll just limit it to the
ones
in question here at this point.

ID BAND SPEC BFLY BSTA RFLY RSTA
1 058567904 1290 1 44 1 44
2 058567903 1290 1 44 1 44
3 060795381 1290 1 44 1 59
4 142710239 1290 1 44 1 18
5 142710231 1290 1 44 1 44

These fields are as follows:
ID - Field I let Access add as a Primary Key. Band should have been
unique
values, but prone to errors, not sequential, and this matches record # for
adding/deleting.
BAND - Unique (?) value that I normally use to COUNT in crosstab results.
By not putting a value in dummy records, they will not count but will
force
the rows to appear.
SPEC - Species ID Number. I am looking at only 17 (been rounding to 16 in
discussion) species. Note that these are four-digit numbers as the past
above ran together with the BAND # and are the last four (eg. 1290).
BFLY - represents the "flyways" where birds were BANDED, an administrative
categorization by USFWS, etc.. I am looking at 1-5, which are the 4
flyways
in the continental US, Alaska, and Canada.
BSTA - This is a two-digit code for the state/province where birds were
BANDED. They should be unique, but there are actually more of these than
2
digits will allow, so the RFLY must be combined as unique identifiers
(source
of my problem, perhaps).
RFLY - See BFLY, except that this pertains to where RECOVERED.
RSTA - See BSTA, except that this pertains to where RECOVERED.

The basic crosstab output that I want will show all of the recovery
locations (RFLY+RSTA) for each species (SPEC) as rows with the Banding
locations (BFLY+BSTA) as columns. In other words, it will show how many
of
each species were recovered in each location from each source.

Expr1 SPEC Total Of BAND 096 118 121 122
122 1320 13 0 0 0 0 1
122 1326 5 0 0 0 0 5
122 1330 11 0 0 0 0 1
122 1340 0 0 0 0 0 0

The "Expr1" is the combined RFLY+RSTA. In these rows, it means Flyway 1
and
state 22. The SPEC shows the first four species IDs. Total is the count
of
all recoveries in that location (D.C.) for each of those species. I have
NO
idea what the ""column is and had never noticed it before. I think it
came
from the forcing rows with dummy records? It will be easy to delete when
viewed in Excel. The remaining columns (only the first ones shown)
indicate
the quantities from each banding location. (By the way, there are several
obvious things that could be done to this table, but there is an Excel
worksheet that performs all of the code lookups and regional filters that
I
don't bother to do in Access, although I may in the long run.)

Note that this table does show even the rows with "0" values because of
the
dummy records. When I started this question yesterday, it would not have
shown: (1.) The 122/1340 row (2.) The 118 column (3.) The "0" values in
the
data. Actually, I think I am only lucky that it is showing all of the
columns because there are SOME recoveries in SOME row (not shown here) for
each. As I learned form other posts, I can force columns, but it would
require statements like the following with values for ALL 62 locations
instead of the years. As it is, I'm happy and adding a few columns in the
Excel output does not compare with adding hundreds of rows that way.

PIVOT WORKTEST.RYR In ("2000","2001","2002","2003","2004","2005");

For the record, the SQL of the current crosstab query looks like this:

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RFLY]+[RSTA] AS Expr1, RECV.SPEC, Count(RECV.[BAND]) AS [Total Of
BAND]
FROM RECV
WHERE (((RECV.SPEC)="1320" Or (RECV.SPEC)="1410" Or ((RECV.SPEC)="1326" Or
(RECV.SPEC)="1420") Or ((RECV.SPEC)="1330" Or (RECV.SPEC)="1430") Or
((RECV.SPEC)="1340" Or (RECV.SPEC)="1440") Or ((RECV.SPEC)="1350" Or
(RECV.SPEC)="1460") Or ((RECV.SPEC)="1370" Or (RECV.SPEC)="1470") Or
((RECV.SPEC)="1390" Or (RECV.SPEC)="1480") Or ((RECV.SPEC)="1400" Or
(RECV.SPEC)="1490") Or (RECV.SPEC)="1500"))
GROUP BY [RFLY]+[RSTA], RECV.SPEC
ORDER BY [RFLY]+[RSTA], RECV.SPEC, [BFLY]+[BSTA]
PIVOT [BFLY]+[BSTA];

The WHERE statement obviously limits the species to the 17 in question and
I
will probably do the same for the location fields as a kind of "filter".
Doing this on my home PC with limited resources makes me balk at creating
tables from all of these queries. It is easier to set up different ones
from
the "source" data table ("RECV") as required.

That's all there is to the data and problem. I apologize for being dense
concerning the Totals Query. It makes sense to me but it sounds like a
"crosstabs" query in my ignorance and leaves me with the same question of
how
to make it show all rows, since each row IS a unique combination of the
values in two fields. I did work on it and created two Tables. One shows
all of the SPEC that I need and one does show all of the locations as the
combined RFLY+RSTA, but, as done, they have no common field to teach me to
Join. Obviously they do, but it is NOT the ones that I want to Join and I
am
doubtful it would work (tell me otherwise). I picked the RYR and forced
that
year because that was the year that had recoveries in all locations AND in
all species, but NOT all species IN all locations. of course. Maybe the
Joining will be a two-step process and I can use these? I do want my
final
output from the RECV table to have every SPEC row from the second table
below
shown as a row with every "Expr1" (location) from the first table.

Expr1 RYR SumOfID Total Of ID
118 1940 14343937 14343937
121 1940 8047170 8047170
122 1940 5375352 5375352

SPEC RYR SumOfID Total Of ID
1320 1940 2307862501 2307862501
1326 1940 2036170 2036170
1330 1940 2165790853 2165790853
1340 1940 2367846 2367846

I'm sure that is MORE than enough, but you did suggest that I show the
data
and what I wanted. Thank you again for any and all thoughts. I am
learning.

Tuffy


"KARL DEWEY" wrote:

You are mixing my post with David's. I did not say to create a table. I
said
to create a totals query. I reccomend the totals query over a table in
that
a table is static but the query will always have the latest information.

Why don't you post your table structure, a sample of 5-6 records, and
what
you want out of the crosstab query.

"TuffyE" wrote:

Karl,

How do I create a table with ALL of those rows. I can/did create a
table
with all of the rows that I need to show for EACH value in a field, but
combining them escapes me still.

Tuffy


"KARL DEWEY" wrote:

You can get all of your rows by creating a totals query for your rows
and
using it left join to the table in the crosstab query.
Something like this --
TRANSFORM Count(CAROL.Word) AS CountOfWord
SELECT [CAROL NAMES].TITLE, Count(CAROL.Word) AS [Total Of Word]
FROM [CAROL NAMES] LEFT JOIN CAROL ON [CAROL NAMES].SONG = CAROL.Song
WHERE (((CAROL.What) Is Not Null))
GROUP BY [CAROL NAMES].TITLE
PIVOT CAROL.What;

I'm learning; I think I even found your typo. There was a
brackets-associated error that I think was really because of a field name
that you created in the first section. I made that change and adjusted it
for the name of the table that I actually created with the first part and,
VOILA, it ran. The only drawback is that I still have the dummy records in
the main table. I'm thinking that I can now remove them and it will still
run. We'll see right now.

NOPE, that doesn't do it without the dummy records. Each row has to be a
unique combination of the RFLY_RSTA that this created and the SPEC. My
thinking wants to do the same thing again using either this table or modify
the original line of:

SELECT [RFLY] & [RSTA] AS RFLY_RSTA, RECV.RFLY, RECV.RSTA INTO STATES2

into something like:

SELECT [RFLY] & [RSTA] & [SPEC] AS LOCATE, RECV.RFLY, RECV.RSTA INTO STATES2

That looks good to me and results in a new Table (STATES2) with a field that
combines all three necessary components. You'd think the only thing left to
do is to modify the main query to change your RFLY_RSTA to LOCATE and the
general name change to this new Table (STATES2). What I have looks like
this, but leaves me right back where I started because it does not show all
of the rows. There are 1566 rows in the STATES2 table that we created here
and only 1098 in the final query. The output LOOKS good, though it takes a
LONG time to run (Joins causing that? It even reruns moving from Design to
Dataset Views). I'm afraid it leaves me more stumped than ever. Heck, I
even found a problem with an earlier part of the fix Nz that I will ask
as a separate thread.

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC] AS LOCATE
FROM STATES2 LEFT JOIN RECV ON (STATES2.SPEC = RECV.SPEC) AND (STATES2.RFLY
= RECV.RFLY) AND (STATES2.RSTA = RECV.RSTA)
WHERE ((([RECV.SPEC]) In
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")))
GROUP BY [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC]
ORDER BY [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC]
PIVOT RECV.BFLY & " - " & RECV.BSTA;







"KARL DEWEY" wrote:

I used David's WHERE IN. The first query is the rollup I mentioned but it
seems I was not clear to you.

RECV_RollUp ----
SELECT [RFLY] & [RSTA] AS RFLY_RSTA, RECV.RFLY, RECV.RSTA
FROM RECV
GROUP BY [RFLY] & [RSTA], RECV.RFLY, RECV.RSTA;

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA] AS [Recovery
Fly - Sta], RECV.SPEC
FROM RECV_RollUp LEFT JOIN RECV ON (RECV_RollUp.RSTA = RECV.RSTA) AND
(RECV_RollUp.RFLY = RECV.RFLY)
WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")
GROUP BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA], RECV.SPEC
ORDER BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA], RECV.SPEC
PIVOT [RECV].[BFLY] & " - " & [RECV].[BSTA];


"David F Cox" wrote:

I like your "I will fix this" attitude and your enterprise in your
work-around. Right now I am facing the choice between your query an 50
dancing girls, but I will leave you with something to be going on with:-

WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")

more later.

"TuffyE" wrote in message
...
You have me laughing at myself because my little mind is mixing a lot more
than just those two posts. I 'm rather compelled to understand this now,
but
did decide that my "workaround" was just to add 1000 dummy records. That
wasn't too difficult in Excel for Paste Append into the main Access Table.
By leaving the field that is COUNTed blank, it doesn't harm my results, I
don't think. There might be some impact on other queries, but I'll deal
with
them and hope to understand how TO DO this before I have to worry about
it.
Deleting those extra records will be easy, which brings me back to showing
you the structure.

First, the table that I am using is "static". The data is updated only
once
a year and I will simply replace the table with a new imported one at that
time (due soon though). I did save the Excel source of the extra records
if
I need to continue the workaround.

Anyway, the data consists of more fields, but I'll just limit it to the
ones
in question here at this point.

ID BAND SPEC BFLY BSTA RFLY RSTA
1 058567904 1290 1 44 1 44
2 058567903 1290 1 44 1 44
3 060795381 1290 1 44 1 59
4 142710239 1290 1 44 1 18
5 142710231 1290 1 44 1 44

These fields are as follows:
ID - Field I let Access add as a Primary Key. Band should have been
unique
values, but prone to errors, not sequential, and this matches record # for
adding/deleting.
BAND - Unique (?) value that I normally use to COUNT in crosstab results.
By not putting a value in dummy records, they will not count but will
force
the rows to appear.
SPEC - Species ID Number. I am looking at only 17 (been rounding to 16 in
discussion) species. Note that these are four-digit numbers as the past
above ran together with the BAND # and are the last four (eg. 1290).
BFLY - represents the "flyways" where birds were BANDED, an administrative
categorization by USFWS, etc.. I am looking at 1-5, which are the 4
flyways
in the continental US, Alaska, and Canada.
BSTA - This is a two-digit code for the state/province where birds were
BANDED. They should be unique, but there are actually more of these than
2
digits will allow, so the RFLY must be combined as unique identifiers
(source
of my problem, perhaps).
RFLY - See BFLY, except that this pertains to where RECOVERED.
RSTA - See BSTA, except that this pertains to where RECOVERED.

The basic crosstab output that I want will show all of the recovery
locations (RFLY+RSTA) for each species (SPEC) as rows with the Banding
locations (BFLY+BSTA) as columns. In other words, it will show how many
of
each species were recovered in each location from each source.

Expr1 SPEC Total Of BAND 096 118 121 122
122 1320 13 0 0 0 0 1
122 1326 5 0 0 0 0 5
122 1330 11 0 0 0 0 1
122 1340 0 0 0 0 0 0

The "Expr1" is the combined RFLY+RSTA. In these rows, it means Flyway 1
and
state 22. The SPEC shows the first four species IDs. Total is the count
of
all recoveries in that location (D.C.) for each of those species. I have
NO
idea what the ""column is and had never noticed it before. I think it
came
from the forcing rows with dummy records? It will be easy to delete when
viewed in Excel. The remaining columns (only the first ones shown)
indicate
the quantities from each banding location. (By the way, there are several
obvious things that could be done to this table, but there is an Excel
worksheet that performs all of the code lookups and regional filters that
I
don't bother to do in Access, although I may in the long run.)

Note that this table does show even the rows with "0" values because of
the
dummy records. When I started this question yesterday, it would not have
shown: (1.) The 122/1340 row (2.) The 118 column (3.) The "0" values in
the
data. Actually, I think I am only lucky that it is showing all of the
columns because there are SOME recoveries in SOME row (not shown here) for
each. As I learned form other posts, I can force columns, but it would
require statements like the following with values for ALL 62 locations
instead of the years. As it is, I'm happy and adding a few columns in the
Excel output does not compare with adding hundreds of rows that way.

PIVOT WORKTEST.RYR In ("2000","2001","2002","2003","2004","2005");

For the record, the SQL of the current crosstab query looks like this:

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RFLY]+[RSTA] AS Expr1, RECV.SPEC, Count(RECV.[BAND]) AS [Total Of
BAND]
FROM RECV
WHERE (((RECV.SPEC)="1320" Or (RECV.SPEC)="1410" Or ((RECV.SPEC)="1326" Or
(RECV.SPEC)="1420") Or ((RECV.SPEC)="1330" Or (RECV.SPEC)="1430") Or
((RECV.SPEC)="1340" Or (RECV.SPEC)="1440") Or ((RECV.SPEC)="1350" Or
(RECV.SPEC)="1460") Or ((RECV.SPEC)="1370" Or (RECV.SPEC)="1470") Or
((RECV.SPEC)="1390" Or (RECV.SPEC)="1480") Or ((RECV.SPEC)="1400" Or
(RECV.SPEC)="1490") Or (RECV.SPEC)="1500"))
GROUP BY [RFLY]+[RSTA], RECV.SPEC
ORDER BY [RFLY]+[RSTA], RECV.SPEC, [BFLY]+[BSTA]
PIVOT [BFLY]+[BSTA];

The WHERE statement obviously limits the species to the 17 in question and
I
will probably do the same for the location fields as a kind of "filter".
Doing this on my home PC with limited resources makes me balk at creating
tables from all of these queries. It is easier to set up different ones
from
the "source" data table ("RECV") as required.

That's all there is to the data and problem. I apologize for being dense
concerning the Totals Query. It makes sense to me but it sounds like a
"crosstabs" query in my ignorance and leaves me with the same question of
how
to make it show all rows, since each row IS a unique combination of the
values in two fields. I did work on it and created two Tables. One shows
all of the SPEC that I need and one does show all of the locations as the
combined RFLY+RSTA, but, as done, they have no common field to teach me to
Join. Obviously they do, but it is NOT the ones that I want to Join and I
am
doubtful it would work (tell me otherwise). I picked the RYR and forced
that
year because that was the year that had recoveries in all locations AND in
all species, but NOT all species IN all locations. of course. Maybe the
Joining will be a two-step process and I can use these? I do want my
final
output from the RECV table to have every SPEC row from the second table
below
shown as a row with every "Expr1" (location) from the first table.

Expr1 RYR SumOfID Total Of ID
118 1940 14343937 14343937
121 1940 8047170 8047170
122 1940 5375352 5375352

SPEC RYR SumOfID Total Of ID
1320 1940 2307862501 2307862501
1326 1940 2036170 2036170
1330 1940 2165790853 2165790853
1340 1940 2367846 2367846

I'm sure that is MORE than enough, but you did suggest that I show the
data
and what I wanted. Thank you again for any and all thoughts. I am
learning.

Tuffy


"KARL DEWEY" wrote:

You are mixing my post with David's. I did not say to create a table. I
said
to create a totals query. I reccomend the totals query over a table in
that
a table is static but the query will always have the latest information.

Why don't you post your table structure, a sample of 5-6 records, and
what
you want out of the crosstab query.

"TuffyE" wrote:

Karl,

How do I create a table with ALL of those rows. I can/did create a
table
with all of the rows that I need to show for EACH value in a field, but
combining them escapes me still.

Tuffy


"KARL DEWEY" wrote:

You can get all of your rows by creating a totals query for your rows
and
using it left join to the table in the crosstab query.
Something like this --
TRANSFORM Count(CAROL.Word) AS CountOfWord
SELECT [CAROL NAMES].TITLE, Count(CAROL.Word) AS [Total Of Word]
FROM [CAROL NAMES] LEFT JOIN CAROL ON [CAROL NAMES].SONG = CAROL.Song
WHERE (((CAROL.What) Is Not Null))
GROUP BY [CAROL NAMES].TITLE
PIVOT CAROL.What;

Trudging deeper into the haunted forest ....

I have, at least, been lost here before. We do not want a WHERE clause in
the crosstab. We have to create a query/queries to select just the records
we want to see and use those as the input to the crosstab. This should make
it run faster too. It is always an idea to do selections as early as
possible in a process, otherwise you are working with records that we are
going to discard. More later.

"TuffyE" wrote in message
...
I'm learning; I think I even found your typo. There was a
brackets-associated error that I think was really because of a field name
that you created in the first section. I made that change and adjusted it
for the name of the table that I actually created with the first part and,
VOILA, it ran. The only drawback is that I still have the dummy records
in
the main table. I'm thinking that I can now remove them and it will still
run. We'll see right now.

NOPE, that doesn't do it without the dummy records. Each row has to be a
unique combination of the RFLY_RSTA that this created and the SPEC. My
thinking wants to do the same thing again using either this table or
modify
the original line of:

SELECT [RFLY] & [RSTA] AS RFLY_RSTA, RECV.RFLY, RECV.RSTA INTO STATES2

into something like:

SELECT [RFLY] & [RSTA] & [SPEC] AS LOCATE, RECV.RFLY, RECV.RSTA INTO
STATES2

That looks good to me and results in a new Table (STATES2) with a field
that
combines all three necessary components. You'd think the only thing left
to
do is to modify the main query to change your RFLY_RSTA to LOCATE and the
general name change to this new Table (STATES2). What I have looks like
this, but leaves me right back where I started because it does not show
all
of the rows. There are 1566 rows in the STATES2 table that we created
here
and only 1098 in the final query. The output LOOKS good, though it takes
a
LONG time to run (Joins causing that? It even reruns moving from Design to
Dataset Views). I'm afraid it leaves me more stumped than ever. Heck, I
even found a problem with an earlier part of the fix Nz that I will
ask
as a separate thread.

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC] AS LOCATE
FROM STATES2 LEFT JOIN RECV ON (STATES2.SPEC = RECV.SPEC) AND
(STATES2.RFLY
= RECV.RFLY) AND (STATES2.RSTA = RECV.RSTA)
WHERE ((([RECV.SPEC]) In
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")))
GROUP BY [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC]
ORDER BY [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC]
PIVOT RECV.BFLY & " - " & RECV.BSTA;







"KARL DEWEY" wrote:

I used David's WHERE IN. The first query is the rollup I mentioned but
it
seems I was not clear to you.

RECV_RollUp ----
SELECT [RFLY] & [RSTA] AS RFLY_RSTA, RECV.RFLY, RECV.RSTA
FROM RECV
GROUP BY [RFLY] & [RSTA], RECV.RFLY, RECV.RSTA;

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA] AS [Recovery
Fly - Sta], RECV.SPEC
FROM RECV_RollUp LEFT JOIN RECV ON (RECV_RollUp.RSTA = RECV.RSTA) AND
(RECV_RollUp.RFLY = RECV.RFLY)
WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")
GROUP BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA], RECV.SPEC
ORDER BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA], RECV.SPEC
PIVOT [RECV].[BFLY] & " - " & [RECV].[BSTA];


"David F Cox" wrote:

I like your "I will fix this" attitude and your enterprise in your
work-around. Right now I am facing the choice between your query an 50
dancing girls, but I will leave you with something to be going on
with:-

WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")

more later.

"TuffyE" wrote in message
...
You have me laughing at myself because my little mind is mixing a lot
more
than just those two posts. I 'm rather compelled to understand this
now,
but
did decide that my "workaround" was just to add 1000 dummy records.
That
wasn't too difficult in Excel for Paste Append into the main Access
Table.
By leaving the field that is COUNTed blank, it doesn't harm my
results, I
don't think. There might be some impact on other queries, but I'll
deal
with
them and hope to understand how TO DO this before I have to worry
about
it.
Deleting those extra records will be easy, which brings me back to
showing
you the structure.

First, the table that I am using is "static". The data is updated
only
once
a year and I will simply replace the table with a new imported one at
that
time (due soon though). I did save the Excel source of the extra
records
if
I need to continue the workaround.

Anyway, the data consists of more fields, but I'll just limit it to
the
ones
in question here at this point.

ID BAND SPEC BFLY BSTA RFLY RSTA
1 058567904 1290 1 44 1 44
2 058567903 1290 1 44 1 44
3 060795381 1290 1 44 1 59
4 142710239 1290 1 44 1 18
5 142710231 1290 1 44 1 44

These fields are as follows:
ID - Field I let Access add as a Primary Key. Band should have been
unique
values, but prone to errors, not sequential, and this matches record
# for
adding/deleting.
BAND - Unique (?) value that I normally use to COUNT in crosstab
results.
By not putting a value in dummy records, they will not count but will
force
the rows to appear.
SPEC - Species ID Number. I am looking at only 17 (been rounding to
16 in
discussion) species. Note that these are four-digit numbers as the
past
above ran together with the BAND # and are the last four (eg. 1290).
BFLY - represents the "flyways" where birds were BANDED, an
administrative
categorization by USFWS, etc.. I am looking at 1-5, which are the 4
flyways
in the continental US, Alaska, and Canada.
BSTA - This is a two-digit code for the state/province where birds
were
BANDED. They should be unique, but there are actually more of these
than
2
digits will allow, so the RFLY must be combined as unique identifiers
(source
of my problem, perhaps).
RFLY - See BFLY, except that this pertains to where RECOVERED.
RSTA - See BSTA, except that this pertains to where RECOVERED.

The basic crosstab output that I want will show all of the recovery
locations (RFLY+RSTA) for each species (SPEC) as rows with the
Banding
locations (BFLY+BSTA) as columns. In other words, it will show how
many
of
each species were recovered in each location from each source.

Expr1 SPEC Total Of BAND 096 118 121 122
122 1320 13 0 0 0 0 1
122 1326 5 0 0 0 0 5
122 1330 11 0 0 0 0 1
122 1340 0 0 0 0 0 0

The "Expr1" is the combined RFLY+RSTA. In these rows, it means
Flyway 1
and
state 22. The SPEC shows the first four species IDs. Total is the
count
of
all recoveries in that location (D.C.) for each of those species. I
have
NO
idea what the ""column is and had never noticed it before. I think
it
came
from the forcing rows with dummy records? It will be easy to delete
when
viewed in Excel. The remaining columns (only the first ones shown)
indicate
the quantities from each banding location. (By the way, there are
several
obvious things that could be done to this table, but there is an
Excel
worksheet that performs all of the code lookups and regional filters
that
I
don't bother to do in Access, although I may in the long run.)

Note that this table does show even the rows with "0" values because
of
the
dummy records. When I started this question yesterday, it would not
have
shown: (1.) The 122/1340 row (2.) The 118 column (3.) The "0" values
in
the
data. Actually, I think I am only lucky that it is showing all of
the
columns because there are SOME recoveries in SOME row (not shown
here) for
each. As I learned form other posts, I can force columns, but it
would
require statements like the following with values for ALL 62
locations
instead of the years. As it is, I'm happy and adding a few columns
in the
Excel output does not compare with adding hundreds of rows that way.

PIVOT WORKTEST.RYR In ("2000","2001","2002","2003","2004","2005");

For the record, the SQL of the current crosstab query looks like
this:

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RFLY]+[RSTA] AS Expr1, RECV.SPEC, Count(RECV.[BAND]) AS
[Total Of
BAND]
FROM RECV
WHERE (((RECV.SPEC)="1320" Or (RECV.SPEC)="1410" Or
((RECV.SPEC)="1326" Or
(RECV.SPEC)="1420") Or ((RECV.SPEC)="1330" Or (RECV.SPEC)="1430") Or
((RECV.SPEC)="1340" Or (RECV.SPEC)="1440") Or ((RECV.SPEC)="1350" Or
(RECV.SPEC)="1460") Or ((RECV.SPEC)="1370" Or (RECV.SPEC)="1470") Or
((RECV.SPEC)="1390" Or (RECV.SPEC)="1480") Or ((RECV.SPEC)="1400" Or
(RECV.SPEC)="1490") Or (RECV.SPEC)="1500"))
GROUP BY [RFLY]+[RSTA], RECV.SPEC
ORDER BY [RFLY]+[RSTA], RECV.SPEC, [BFLY]+[BSTA]
PIVOT [BFLY]+[BSTA];

The WHERE statement obviously limits the species to the 17 in
question and
I
will probably do the same for the location fields as a kind of
"filter".
Doing this on my home PC with limited resources makes me balk at
creating
tables from all of these queries. It is easier to set up different
ones
from
the "source" data table ("RECV") as required.

That's all there is to the data and problem. I apologize for being
dense
concerning the Totals Query. It makes sense to me but it sounds like
a
"crosstabs" query in my ignorance and leaves me with the same
question of
how
to make it show all rows, since each row IS a unique combination of
the
values in two fields. I did work on it and created two Tables. One
shows
all of the SPEC that I need and one does show all of the locations as
the
combined RFLY+RSTA, but, as done, they have no common field to teach
me to
Join. Obviously they do, but it is NOT the ones that I want to Join
and I
am
doubtful it would work (tell me otherwise). I picked the RYR and
forced
that
year because that was the year that had recoveries in all locations
AND in
all species, but NOT all species IN all locations. of course. Maybe
the
Joining will be a two-step process and I can use these? I do want my
final
output from the RECV table to have every SPEC row from the second
table
below
shown as a row with every "Expr1" (location) from the first table.

Expr1 RYR SumOfID Total Of ID
118 1940 14343937 14343937
121 1940 8047170 8047170
122 1940 5375352 5375352

SPEC RYR SumOfID Total Of ID
1320 1940 2307862501 2307862501
1326 1940 2036170 2036170
1330 1940 2165790853 2165790853
1340 1940 2367846 2367846

I'm sure that is MORE than enough, but you did suggest that I show
the
data
and what I wanted. Thank you again for any and all thoughts. I am
learning.

Tuffy


"KARL DEWEY" wrote:

You are mixing my post with David's. I did not say to create a
table. I
said
to create a totals query. I reccomend the totals query over a table
in
that
a table is static but the query will always have the latest
information.

Why don't you post your table structure, a sample of 5-6 records,
and
what
you want out of the crosstab query.

"TuffyE" wrote:

Karl,

How do I create a table with ALL of those rows. I can/did create
a
table
with all of the rows that I need to show for EACH value in a
field, but
combining them escapes me still.

Tuffy


"KARL DEWEY" wrote:

You can get all of your rows by creating a totals query for your
rows
and
using it left join to the table in the crosstab query.
Something like this --
TRANSFORM Count(CAROL.Word) AS CountOfWord
SELECT [CAROL NAMES].TITLE, Count(CAROL.Word) AS [Total Of Word]
FROM [CAROL NAMES] LEFT JOIN CAROL ON [CAROL NAMES].SONG =
CAROL.Song
WHERE (((CAROL.What) Is Not Null))
GROUP BY [CAROL NAMES].TITLE
PIVOT CAROL.What;

The "haunted forest" is an understatement for me. I think of myself as a
quick learner, but I had never seen a single line of SQL until two days ago
and am "learning" that as an overlay over the logic of field management here.
I understand perfectly what you mean, but there is a resource/storage value
to my approach. There is only (before this exercise) a single data table
imported from FileMaker. There will be only a limited number of crosstab
queries run from it to be used as updating input to existing Excel/MapPoint
tables, charts, and maps. Much of the structure of these involves matching
up with codes, arrangement, and inclusions/exclusions from external data
sources that are associated, but not a part of this data. None of the
queries took more than 30 seconds on the desktop (before this exercise=5
Min+).

I could follow more standard database procedures by creating subsets of the
overall data for the 17 species, but that is just about all of the records
anyway (1.772 Million of 1.789 Million) and it's seemed easier to "screen"
out the others here than later in Excel OR have two separate source tables
with only that much difference.

Still, I think your thought DOES contain the solution here. If I were to
create a new Table (RECV2) that did contain a LOCATE field (RFLY+RSTA+SPEC)
exactly as in the STATES2 that we created, I suppose the failure of the Join
would no longer exist. Creating the row list in another table hasn't worked
because we have not had the proper field to Join with it in the data table?
I will continue to play with it; your thoughts are having a very positive
effect.

Tuffy

"David F Cox" wrote:

Trudging deeper into the haunted forest ....

I have, at least, been lost here before. We do not want a WHERE clause in
the crosstab. We have to create a query/queries to select just the records
we want to see and use those as the input to the crosstab. This should make
it run faster too. It is always an idea to do selections as early as
possible in a process, otherwise you are working with records that we are
going to discard. More later.

"TuffyE" wrote in message
...
I'm learning; I think I even found your typo. There was a
brackets-associated error that I think was really because of a field name
that you created in the first section. I made that change and adjusted it
for the name of the table that I actually created with the first part and,
VOILA, it ran. The only drawback is that I still have the dummy records
in
the main table. I'm thinking that I can now remove them and it will still
run. We'll see right now.

NOPE, that doesn't do it without the dummy records. Each row has to be a
unique combination of the RFLY_RSTA that this created and the SPEC. My
thinking wants to do the same thing again using either this table or
modify
the original line of:

SELECT [RFLY] & [RSTA] AS RFLY_RSTA, RECV.RFLY, RECV.RSTA INTO STATES2

into something like:

SELECT [RFLY] & [RSTA] & [SPEC] AS LOCATE, RECV.RFLY, RECV.RSTA INTO
STATES2

That looks good to me and results in a new Table (STATES2) with a field
that
combines all three necessary components. You'd think the only thing left
to
do is to modify the main query to change your RFLY_RSTA to LOCATE and the
general name change to this new Table (STATES2). What I have looks like
this, but leaves me right back where I started because it does not show
all
of the rows. There are 1566 rows in the STATES2 table that we created
here
and only 1098 in the final query. The output LOOKS good, though it takes
a
LONG time to run (Joins causing that? It even reruns moving from Design to
Dataset Views). I'm afraid it leaves me more stumped than ever. Heck, I
even found a problem with an earlier part of the fix Nz that I will
ask
as a separate thread.

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC] AS LOCATE
FROM STATES2 LEFT JOIN RECV ON (STATES2.SPEC = RECV.SPEC) AND
(STATES2.RFLY
= RECV.RFLY) AND (STATES2.RSTA = RECV.RSTA)
WHERE ((([RECV.SPEC]) In
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")))
GROUP BY [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC]
ORDER BY [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC]
PIVOT RECV.BFLY & " - " & RECV.BSTA;







"KARL DEWEY" wrote:

I used David's WHERE IN. The first query is the rollup I mentioned but
it
seems I was not clear to you.

RECV_RollUp ----
SELECT [RFLY] & [RSTA] AS RFLY_RSTA, RECV.RFLY, RECV.RSTA
FROM RECV
GROUP BY [RFLY] & [RSTA], RECV.RFLY, RECV.RSTA;

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA] AS [Recovery
Fly - Sta], RECV.SPEC
FROM RECV_RollUp LEFT JOIN RECV ON (RECV_RollUp.RSTA = RECV.RSTA) AND
(RECV_RollUp.RFLY = RECV.RFLY)
WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")
GROUP BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA], RECV.SPEC
ORDER BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA], RECV.SPEC
PIVOT [RECV].[BFLY] & " - " & [RECV].[BSTA];


"David F Cox" wrote:

I like your "I will fix this" attitude and your enterprise in your
work-around. Right now I am facing the choice between your query an 50
dancing girls, but I will leave you with something to be going on
with:-

WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")

more later.

"TuffyE" wrote in message
...
You have me laughing at myself because my little mind is mixing a lot
more
than just those two posts. I 'm rather compelled to understand this
now,
but
did decide that my "workaround" was just to add 1000 dummy records.
That
wasn't too difficult in Excel for Paste Append into the main Access
Table.
By leaving the field that is COUNTed blank, it doesn't harm my
results, I
don't think. There might be some impact on other queries, but I'll
deal
with
them and hope to understand how TO DO this before I have to worry
about
it.
Deleting those extra records will be easy, which brings me back to
showing
you the structure.

First, the table that I am using is "static". The data is updated
only
once
a year and I will simply replace the table with a new imported one at
that
time (due soon though). I did save the Excel source of the extra
records
if
I need to continue the workaround.

Anyway, the data consists of more fields, but I'll just limit it to
the
ones
in question here at this point.

ID BAND SPEC BFLY BSTA RFLY RSTA
1 058567904 1290 1 44 1 44
2 058567903 1290 1 44 1 44
3 060795381 1290 1 44 1 59
4 142710239 1290 1 44 1 18
5 142710231 1290 1 44 1 44

These fields are as follows:
ID - Field I let Access add as a Primary Key. Band should have been
unique
values, but prone to errors, not sequential, and this matches record
# for
adding/deleting.
BAND - Unique (?) value that I normally use to COUNT in crosstab
results.
By not putting a value in dummy records, they will not count but will
force
the rows to appear.
SPEC - Species ID Number. I am looking at only 17 (been rounding to
16 in
discussion) species. Note that these are four-digit numbers as the
past
above ran together with the BAND # and are the last four (eg. 1290).
BFLY - represents the "flyways" where birds were BANDED, an
administrative
categorization by USFWS, etc.. I am looking at 1-5, which are the 4
flyways
in the continental US, Alaska, and Canada.
BSTA - This is a two-digit code for the state/province where birds
were
BANDED. They should be unique, but there are actually more of these
than
2
digits will allow, so the RFLY must be combined as unique identifiers
(source
of my problem, perhaps).
RFLY - See BFLY, except that this pertains to where RECOVERED.
RSTA - See BSTA, except that this pertains to where RECOVERED.

The basic crosstab output that I want will show all of the recovery
locations (RFLY+RSTA) for each species (SPEC) as rows with the
Banding
locations (BFLY+BSTA) as columns. In other words, it will show how
many
of
each species were recovered in each location from each source.

Expr1 SPEC Total Of BAND 096 118 121 122
122 1320 13 0 0 0 0 1
122 1326 5 0 0 0 0 5
122 1330 11 0 0 0 0 1
122 1340 0 0 0 0 0 0

The "Expr1" is the combined RFLY+RSTA. In these rows, it means
Flyway 1
and
state 22. The SPEC shows the first four species IDs. Total is the
count
of
all recoveries in that location (D.C.) for each of those species. I
have
NO
idea what the ""column is and had never noticed it before. I think
it
came
from the forcing rows with dummy records? It will be easy to delete
when
viewed in Excel. The remaining columns (only the first ones shown)
indicate
the quantities from each banding location. (By the way, there are
several
obvious things that could be done to this table, but there is an
Excel
worksheet that performs all of the code lookups and regional filters
that
I
don't bother to do in Access, although I may in the long run.)

Note that this table does show even the rows with "0" values because
of
the
dummy records. When I started this question yesterday, it would not
have
shown: (1.) The 122/1340 row (2.) The 118 column (3.) The "0" values
in
the
data. Actually, I think I am only lucky that it is showing all of
the
columns because there are SOME recoveries in SOME row (not shown
here) for
each. As I learned form other posts, I can force columns, but it
would
require statements like the following with values for ALL 62
locations
instead of the years. As it is, I'm happy and adding a few columns
in the
Excel output does not compare with adding hundreds of rows that way.

PIVOT WORKTEST.RYR In ("2000","2001","2002","2003","2004","2005");

For the record, the SQL of the current crosstab query looks like
this:

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RFLY]+[RSTA] AS Expr1, RECV.SPEC, Count(RECV.[BAND]) AS
[Total Of
BAND]
FROM RECV
WHERE (((RECV.SPEC)="1320" Or (RECV.SPEC)="1410" Or
((RECV.SPEC)="1326" Or
(RECV.SPEC)="1420") Or ((RECV.SPEC)="1330" Or (RECV.SPEC)="1430") Or
((RECV.SPEC)="1340" Or (RECV.SPEC)="1440") Or ((RECV.SPEC)="1350" Or
(RECV.SPEC)="1460") Or ((RECV.SPEC)="1370" Or (RECV.SPEC)="1470") Or
((RECV.SPEC)="1390" Or (RECV.SPEC)="1480") Or ((RECV.SPEC)="1400" Or
(RECV.SPEC)="1490") Or (RECV.SPEC)="1500"))
GROUP BY [RFLY]+[RSTA], RECV.SPEC
ORDER BY [RFLY]+[RSTA], RECV.SPEC, [BFLY]+[BSTA]
PIVOT [BFLY]+[BSTA];

The WHERE statement obviously limits the species to the 17 in
question and
I
will probably do the same for the location fields as a kind of
"filter".
Doing this on my home PC with limited resources makes me balk at
creating
tables from all of these queries. It is easier to set up different
ones
from
the "source" data table ("RECV") as required.

That's all there is to the data and problem. I apologize for being
dense
concerning the Totals Query. It makes sense to me but it sounds like
a
"crosstabs" query in my ignorance and leaves me with the same
question of
how
to make it show all rows, since each row IS a unique combination of
the
values in two fields. I did work on it and created two Tables. One
shows
all of the SPEC that I need and one does show all of the locations as
the
combined RFLY+RSTA, but, as done, they have no common field to teach
me to
Join. Obviously they do, but it is NOT the ones that I want to Join
and I
am
doubtful it would work (tell me otherwise). I picked the RYR and
forced
that
year because that was the year that had recoveries in all locations
AND in
all species, but NOT all species IN all locations. of course. Maybe
the

Now, I really thought I had this, but.......

I created yet another table from the original crosstab query that has both a
field very similar to the LOCATE field in the STATES2 that we created. Those
two look like this:

RCROSS:
RLOC BLOC CountOfBAND Total Of BAND
0001320 118 1 1
0001320 121 9 9
0001320 144 13 13

STATES2
LOCATE RFLY RSTA SPEC
0001310 0 00 1310
0001320 0 00 1320
0001326 0 00 1326

I was certain that all I had to do was create a crosstab on RCROSS with RLOC
and LOCATE Joined.

RLOC Total Of CountOfBAND 096 118
0001320 2059 1
0001326 13
0001330 346 3

It looks good, but only shows 1,228 of the 1,566 records in STATES2. Yes, I
played with the Join, but the other two result in 1.099 records. The SQL:

TRANSFORM Sum(RCROSS.CountOfBAND) AS SumOfCountOfBAND
SELECT RCROSS.RLOC, Sum(RCROSS.CountOfBAND) AS [Total Of CountOfBAND]
FROM RCROSS LEFT JOIN STATES2 ON RCROSS.RLOC = STATES2.LOCATE
GROUP BY RCROSS.RLOC
PIVOT RCROSS.BLOC;

Maybe I can't get there from here? BTW, the "dummy records" are still in
here and the differences now are because we are looking at ALL RFLY+RSTA+SPEC
combinations; the dummies are only in some of the flyways. That doesn't
explain the above basic RCROSS vs. STATES2 record numbers, though.




"David F Cox" wrote:

Trudging deeper into the haunted forest ....

I have, at least, been lost here before. We do not want a WHERE clause in
the crosstab. We have to create a query/queries to select just the records
we want to see and use those as the input to the crosstab. This should make
it run faster too. It is always an idea to do selections as early as
possible in a process, otherwise you are working with records that we are
going to discard. More later.

"TuffyE" wrote in message
...
I'm learning; I think I even found your typo. There was a
brackets-associated error that I think was really because of a field name
that you created in the first section. I made that change and adjusted it
for the name of the table that I actually created with the first part and,
VOILA, it ran. The only drawback is that I still have the dummy records
in
the main table. I'm thinking that I can now remove them and it will still
run. We'll see right now.

NOPE, that doesn't do it without the dummy records. Each row has to be a
unique combination of the RFLY_RSTA that this created and the SPEC. My
thinking wants to do the same thing again using either this table or
modify
the original line of:

SELECT [RFLY] & [RSTA] AS RFLY_RSTA, RECV.RFLY, RECV.RSTA INTO STATES2

into something like:

SELECT [RFLY] & [RSTA] & [SPEC] AS LOCATE, RECV.RFLY, RECV.RSTA INTO
STATES2

That looks good to me and results in a new Table (STATES2) with a field
that
combines all three necessary components. You'd think the only thing left
to
do is to modify the main query to change your RFLY_RSTA to LOCATE and the
general name change to this new Table (STATES2). What I have looks like
this, but leaves me right back where I started because it does not show
all
of the rows. There are 1566 rows in the STATES2 table that we created
here
and only 1098 in the final query. The output LOOKS good, though it takes
a
LONG time to run (Joins causing that? It even reruns moving from Design to
Dataset Views). I'm afraid it leaves me more stumped than ever. Heck, I
even found a problem with an earlier part of the fix Nz that I will
ask
as a separate thread.

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC] AS LOCATE
FROM STATES2 LEFT JOIN RECV ON (STATES2.SPEC = RECV.SPEC) AND
(STATES2.RFLY
= RECV.RFLY) AND (STATES2.RSTA = RECV.RSTA)
WHERE ((([RECV.SPEC]) In
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")))
GROUP BY [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC]
ORDER BY [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC]
PIVOT RECV.BFLY & " - " & RECV.BSTA;







"KARL DEWEY" wrote:

I used David's WHERE IN. The first query is the rollup I mentioned but
it
seems I was not clear to you.

RECV_RollUp ----
SELECT [RFLY] & [RSTA] AS RFLY_RSTA, RECV.RFLY, RECV.RSTA
FROM RECV
GROUP BY [RFLY] & [RSTA], RECV.RFLY, RECV.RSTA;

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA] AS [Recovery
Fly - Sta], RECV.SPEC
FROM RECV_RollUp LEFT JOIN RECV ON (RECV_RollUp.RSTA = RECV.RSTA) AND
(RECV_RollUp.RFLY = RECV.RFLY)
WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")
GROUP BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA], RECV.SPEC
ORDER BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA], RECV.SPEC
PIVOT [RECV].[BFLY] & " - " & [RECV].[BSTA];


"David F Cox" wrote:

I like your "I will fix this" attitude and your enterprise in your
work-around. Right now I am facing the choice between your query an 50
dancing girls, but I will leave you with something to be going on
with:-

WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")

more later.

"TuffyE" wrote in message
...
You have me laughing at myself because my little mind is mixing a lot
more
than just those two posts. I 'm rather compelled to understand this
now,
but
did decide that my "workaround" was just to add 1000 dummy records.
That
wasn't too difficult in Excel for Paste Append into the main Access
Table.
By leaving the field that is COUNTed blank, it doesn't harm my
results, I
don't think. There might be some impact on other queries, but I'll
deal
with
them and hope to understand how TO DO this before I have to worry
about
it.
Deleting those extra records will be easy, which brings me back to
showing
you the structure.

First, the table that I am using is "static". The data is updated
only
once
a year and I will simply replace the table with a new imported one at
that
time (due soon though). I did save the Excel source of the extra
records
if
I need to continue the workaround.

Anyway, the data consists of more fields, but I'll just limit it to
the
ones
in question here at this point.

ID BAND SPEC BFLY BSTA RFLY RSTA
1 058567904 1290 1 44 1 44
2 058567903 1290 1 44 1 44
3 060795381 1290 1 44 1 59
4 142710239 1290 1 44 1 18
5 142710231 1290 1 44 1 44

These fields are as follows:
ID - Field I let Access add as a Primary Key. Band should have been
unique
values, but prone to errors, not sequential, and this matches record
# for
adding/deleting.
BAND - Unique (?) value that I normally use to COUNT in crosstab
results.
By not putting a value in dummy records, they will not count but will
force
the rows to appear.
SPEC - Species ID Number. I am looking at only 17 (been rounding to
16 in
discussion) species. Note that these are four-digit numbers as the
past
above ran together with the BAND # and are the last four (eg. 1290).
BFLY - represents the "flyways" where birds were BANDED, an
administrative
categorization by USFWS, etc.. I am looking at 1-5, which are the 4
flyways
in the continental US, Alaska, and Canada.
BSTA - This is a two-digit code for the state/province where birds
were
BANDED. They should be unique, but there are actually more of these
than
2
digits will allow, so the RFLY must be combined as unique identifiers
(source
of my problem, perhaps).
RFLY - See BFLY, except that this pertains to where RECOVERED.
RSTA - See BSTA, except that this pertains to where RECOVERED.

The basic crosstab output that I want will show all of the recovery
locations (RFLY+RSTA) for each species (SPEC) as rows with the
Banding
locations (BFLY+BSTA) as columns. In other words, it will show how
many
of
each species were recovered in each location from each source.

Expr1 SPEC Total Of BAND 096 118 121 122
122 1320 13 0 0 0 0 1
122 1326 5 0 0 0 0 5
122 1330 11 0 0 0 0 1
122 1340 0 0 0 0 0 0

The "Expr1" is the combined RFLY+RSTA. In these rows, it means
Flyway 1
and
state 22. The SPEC shows the first four species IDs. Total is the
count
of
all recoveries in that location (D.C.) for each of those species. I
have
NO
idea what the ""column is and had never noticed it before. I think
it
came
from the forcing rows with dummy records? It will be easy to delete
when
viewed in Excel. The remaining columns (only the first ones shown)
indicate
the quantities from each banding location. (By the way, there are
several
obvious things that could be done to this table, but there is an
Excel
worksheet that performs all of the code lookups and regional filters
that
I
don't bother to do in Access, although I may in the long run.)

Note that this table does show even the rows with "0" values because
of
the
dummy records. When I started this question yesterday, it would not
have
shown: (1.) The 122/1340 row (2.) The 118 column (3.) The "0" values
in
the
data. Actually, I think I am only lucky that it is showing all of
the
columns because there are SOME recoveries in SOME row (not shown
here) for
each. As I learned form other posts, I can force columns, but it
would
require statements like the following with values for ALL 62
locations
instead of the years. As it is, I'm happy and adding a few columns
in the
Excel output does not compare with adding hundreds of rows that way.

PIVOT WORKTEST.RYR In ("2000","2001","2002","2003","2004","2005");

For the record, the SQL of the current crosstab query looks like
this:

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RFLY]+[RSTA] AS Expr1, RECV.SPEC, Count(RECV.[BAND]) AS
[Total Of
BAND]
FROM RECV
WHERE (((RECV.SPEC)="1320" Or (RECV.SPEC)="1410" Or
((RECV.SPEC)="1326" Or
(RECV.SPEC)="1420") Or ((RECV.SPEC)="1330" Or (RECV.SPEC)="1430") Or
((RECV.SPEC)="1340" Or (RECV.SPEC)="1440") Or ((RECV.SPEC)="1350" Or
(RECV.SPEC)="1460") Or ((RECV.SPEC)="1370" Or (RECV.SPEC)="1470") Or
((RECV.SPEC)="1390" Or (RECV.SPEC)="1480") Or ((RECV.SPEC)="1400" Or
(RECV.SPEC)="1490") Or (RECV.SPEC)="1500"))
GROUP BY [RFLY]+[RSTA], RECV.SPEC
ORDER BY [RFLY]+[RSTA], RECV.SPEC, [BFLY]+[BSTA]
PIVOT [BFLY]+[BSTA];

The WHERE statement obviously limits the species to the 17 in
question and
I
will probably do the same for the location fields as a kind of
"filter".
Doing this on my home PC with limited resources makes me balk at
creating
tables from all of these queries. It is easier to set up different
ones
from
the "source" data table ("RECV") as required.

That's all there is to the data and problem. I apologize for being
dense
concerning the Totals Query. It makes sense to me but it sounds like
a
"crosstabs" query in my ignorance and leaves me with the same
question of
how
to make it show all rows, since each row IS a unique combination of
the
values in two fields. I did work on it and created two Tables. One
shows
all of the SPEC that I need and one does show all of the locations as
the
combined RFLY+RSTA, but, as done, they have no common field to teach
me to
Join. Obviously they do, but it is NOT the ones that I want to Join
and I
am
doubtful it would work (tell me otherwise). I picked the RYR and
forced
that
year because that was the year that had recoveries in all locations
AND in
all species, but NOT all species IN all locations. of course. Maybe
the

The procedure seems to be:

Create a table for each of the fields in the row header with all possible
values for that header. There will be only one field in each table if you
want every combination involving that field.

The species row header table might have:
Owl
Hawk
Swan
etc

The RFLY row header table might have:
001
002
003
etc

If you do not want all possible combinations of some fields e.g You have
"Recovered" and "Died" and "flew south" "flew north" you can create a table
of the combinations you want as in:
Recovered Flew south
recovered flew North
Died ""

Add these tables to the query view - do not attempt to join them.
add the fields to the query as one combined formatted string, this will be
your row header.
This query will generate all of the desired header combinations.

Create a query to select the data that you want displayed, and create a
formatted string that matches the row headers from the fields in it.

These two queries, headers and data, are then the input to the crosstab,
with a left join to include all of the headers.

I hope I have described the process right, and clearly enough.

David F. Cox



"TuffyE" wrote in message
news
Now, I really thought I had this, but.......

I created yet another table from the original crosstab query that has both
a
field very similar to the LOCATE field in the STATES2 that we created.
Those
two look like this:

RCROSS:
RLOC BLOC CountOfBAND Total Of BAND
0001320 118 1 1
0001320 121 9 9
0001320 144 13 13

STATES2
LOCATE RFLY RSTA SPEC
0001310 0 00 1310
0001320 0 00 1320
0001326 0 00 1326

I was certain that all I had to do was create a crosstab on RCROSS with
RLOC
and LOCATE Joined.

RLOC Total Of CountOfBAND 096 118
0001320 2059 1
0001326 13
0001330 346 3

It looks good, but only shows 1,228 of the 1,566 records in STATES2. Yes,
I
played with the Join, but the other two result in 1.099 records. The SQL:

TRANSFORM Sum(RCROSS.CountOfBAND) AS SumOfCountOfBAND
SELECT RCROSS.RLOC, Sum(RCROSS.CountOfBAND) AS [Total Of CountOfBAND]
FROM RCROSS LEFT JOIN STATES2 ON RCROSS.RLOC = STATES2.LOCATE
GROUP BY RCROSS.RLOC
PIVOT RCROSS.BLOC;

Maybe I can't get there from here? BTW, the "dummy records" are still in
here and the differences now are because we are looking at ALL
RFLY+RSTA+SPEC
combinations; the dummies are only in some of the flyways. That doesn't
explain the above basic RCROSS vs. STATES2 record numbers, though.




"David F Cox" wrote:

Trudging deeper into the haunted forest ....

I have, at least, been lost here before. We do not want a WHERE clause in
the crosstab. We have to create a query/queries to select just the
records
we want to see and use those as the input to the crosstab. This should
make
it run faster too. It is always an idea to do selections as early as
possible in a process, otherwise you are working with records that we are
going to discard. More later.

"TuffyE" wrote in message
...
I'm learning; I think I even found your typo. There was a
brackets-associated error that I think was really because of a field
name
that you created in the first section. I made that change and adjusted
it
for the name of the table that I actually created with the first part
and,
VOILA, it ran. The only drawback is that I still have the dummy
records
in
the main table. I'm thinking that I can now remove them and it will
still
run. We'll see right now.

NOPE, that doesn't do it without the dummy records. Each row has to be
a
unique combination of the RFLY_RSTA that this created and the SPEC. My
thinking wants to do the same thing again using either this table or
modify
the original line of:

SELECT [RFLY] & [RSTA] AS RFLY_RSTA, RECV.RFLY, RECV.RSTA INTO STATES2

into something like:

SELECT [RFLY] & [RSTA] & [SPEC] AS LOCATE, RECV.RFLY, RECV.RSTA INTO
STATES2

That looks good to me and results in a new Table (STATES2) with a field
that
combines all three necessary components. You'd think the only thing
left
to
do is to modify the main query to change your RFLY_RSTA to LOCATE and
the
general name change to this new Table (STATES2). What I have looks
like
this, but leaves me right back where I started because it does not show
all
of the rows. There are 1566 rows in the STATES2 table that we created
here
and only 1098 in the final query. The output LOOKS good, though it
takes
a
LONG time to run (Joins causing that? It even reruns moving from Design
to
Dataset Views). I'm afraid it leaves me more stumped than ever. Heck,
I
even found a problem with an earlier part of the fix Nz that I will
ask
as a separate thread.

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC] AS LOCATE
FROM STATES2 LEFT JOIN RECV ON (STATES2.SPEC = RECV.SPEC) AND
(STATES2.RFLY
= RECV.RFLY) AND (STATES2.RSTA = RECV.RSTA)
WHERE ((([RECV.SPEC]) In
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")))
GROUP BY [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC]
ORDER BY [STATES2].[RFLY] & " - " & [STATES2].[RSTA] & " - " &
[STATES2].[SPEC]
PIVOT RECV.BFLY & " - " & RECV.BSTA;







"KARL DEWEY" wrote:

I used David's WHERE IN. The first query is the rollup I mentioned
but
it
seems I was not clear to you.

RECV_RollUp ----
SELECT [RFLY] & [RSTA] AS RFLY_RSTA, RECV.RFLY, RECV.RSTA
FROM RECV
GROUP BY [RFLY] & [RSTA], RECV.RFLY, RECV.RSTA;

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA] AS
[Recovery
Fly - Sta], RECV.SPEC
FROM RECV_RollUp LEFT JOIN RECV ON (RECV_RollUp.RSTA = RECV.RSTA) AND
(RECV_RollUp.RFLY = RECV.RFLY)
WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")
GROUP BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA],
RECV.SPEC
ORDER BY [RECV_RollUp].[RFLY] & " - " & [RECV_RollUp].[RSTA],
RECV.SPEC
PIVOT [RECV].[BFLY] & " - " & [RECV].[BSTA];


"David F Cox" wrote:

I like your "I will fix this" attitude and your enterprise in your
work-around. Right now I am facing the choice between your query an
50
dancing girls, but I will leave you with something to be going on
with:-

WHERE [RECV.SPEC] IN
("1320","1410","1326","1420","1330","1430","1340", "1440","1350","1460","1370","1470","1390","1480"," 1400","1490","1500")

more later.

"TuffyE" wrote in message
...
You have me laughing at myself because my little mind is mixing a
lot
more
than just those two posts. I 'm rather compelled to understand
this
now,
but
did decide that my "workaround" was just to add 1000 dummy
records.
That
wasn't too difficult in Excel for Paste Append into the main
Access
Table.
By leaving the field that is COUNTed blank, it doesn't harm my
results, I
don't think. There might be some impact on other queries, but
I'll
deal
with
them and hope to understand how TO DO this before I have to worry
about
it.
Deleting those extra records will be easy, which brings me back to
showing
you the structure.

First, the table that I am using is "static". The data is updated
only
once
a year and I will simply replace the table with a new imported one
at
that
time (due soon though). I did save the Excel source of the extra
records
if
I need to continue the workaround.

Anyway, the data consists of more fields, but I'll just limit it
to
the
ones
in question here at this point.

ID BAND SPEC BFLY BSTA RFLY RSTA
1 058567904 1290 1 44 1 44
2 058567903 1290 1 44 1 44
3 060795381 1290 1 44 1 59
4 142710239 1290 1 44 1 18
5 142710231 1290 1 44 1 44

These fields are as follows:
ID - Field I let Access add as a Primary Key. Band should have
been
unique
values, but prone to errors, not sequential, and this matches
record
# for
adding/deleting.
BAND - Unique (?) value that I normally use to COUNT in crosstab
results.
By not putting a value in dummy records, they will not count but
will
force
the rows to appear.
SPEC - Species ID Number. I am looking at only 17 (been rounding
to
16 in
discussion) species. Note that these are four-digit numbers as
the
past
above ran together with the BAND # and are the last four (eg.
1290).
BFLY - represents the "flyways" where birds were BANDED, an
administrative
categorization by USFWS, etc.. I am looking at 1-5, which are the
4
flyways
in the continental US, Alaska, and Canada.
BSTA - This is a two-digit code for the state/province where birds
were
BANDED. They should be unique, but there are actually more of
these
than
2
digits will allow, so the RFLY must be combined as unique
identifiers
(source
of my problem, perhaps).
RFLY - See BFLY, except that this pertains to where RECOVERED.
RSTA - See BSTA, except that this pertains to where RECOVERED.

The basic crosstab output that I want will show all of the
recovery
locations (RFLY+RSTA) for each species (SPEC) as rows with the
Banding
locations (BFLY+BSTA) as columns. In other words, it will show
how
many
of
each species were recovered in each location from each source.

Expr1 SPEC Total Of BAND 096 118 121 122
122 1320 13 0 0 0 0 1
122 1326 5 0 0 0 0 5
122 1330 11 0 0 0 0 1
122 1340 0 0 0 0 0 0

The "Expr1" is the combined RFLY+RSTA. In these rows, it means
Flyway 1
and
state 22. The SPEC shows the first four species IDs. Total is
the
count
of
all recoveries in that location (D.C.) for each of those species.
I
have
NO
idea what the ""column is and had never noticed it before. I
think
it
came
from the forcing rows with dummy records? It will be easy to
delete
when
viewed in Excel. The remaining columns (only the first ones
shown)
indicate
the quantities from each banding location. (By the way, there are
several
obvious things that could be done to this table, but there is an
Excel
worksheet that performs all of the code lookups and regional
filters
that
I
don't bother to do in Access, although I may in the long run.)

Note that this table does show even the rows with "0" values
because
of
the
dummy records. When I started this question yesterday, it would
not
have
shown: (1.) The 122/1340 row (2.) The 118 column (3.) The "0"
values
in
the
data. Actually, I think I am only lucky that it is showing all of
the
columns because there are SOME recoveries in SOME row (not shown
here) for
each. As I learned form other posts, I can force columns, but it
would
require statements like the following with values for ALL 62
locations
instead of the years. As it is, I'm happy and adding a few
columns
in the
Excel output does not compare with adding hundreds of rows that
way.

PIVOT WORKTEST.RYR In ("2000","2001","2002","2003","2004","2005");

For the record, the SQL of the current crosstab query looks like
this:

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
SELECT [RFLY]+[RSTA] AS Expr1, RECV.SPEC, Count(RECV.[BAND]) AS
[Total Of
BAND]
FROM RECV
WHERE (((RECV.SPEC)="1320" Or (RECV.SPEC)="1410" Or
((RECV.SPEC)="1326" Or
(RECV.SPEC)="1420") Or ((RECV.SPEC)="1330" Or (RECV.SPEC)="1430")
Or
((RECV.SPEC)="1340" Or (RECV.SPEC)="1440") Or ((RECV.SPEC)="1350"
Or
(RECV.SPEC)="1460") Or ((RECV.SPEC)="1370" Or (RECV.SPEC)="1470")
Or
((RECV.SPEC)="1390" Or (RECV.SPEC)="1480") Or ((RECV.SPEC)="1400"
Or
(RECV.SPEC)="1490") Or (RECV.SPEC)="1500"))
GROUP BY [RFLY]+[RSTA], RECV.SPEC
ORDER BY [RFLY]+[RSTA], RECV.SPEC, [BFLY]+[BSTA]
PIVOT [BFLY]+[BSTA];

The WHERE statement obviously limits the species to the 17 in
question and
I
will probably do the same for the location fields as a kind of
"filter".
Doing this on my home PC with limited resources makes me balk at
creating
tables from all of these queries. It is easier to set up
different
ones
from
the "source" data table ("RECV") as required.

That's all there is to the data and problem. I apologize for
being
dense
concerning the Totals Query. It makes sense to me but it sounds
like
a
"crosstabs" query in my ignorance and leaves me with the same
question of
how
to make it show all rows, since each row IS a unique combination
of
the
values in two fields. I did work on it and created two Tables.
One
shows
all of the SPEC that I need and one does show all of the locations
as
the
combined RFLY+RSTA, but, as done, they have no common field to
teach
me to
Join. Obviously they do, but it is NOT the ones that I want to
Join
and I
am
doubtful it would work (tell me otherwise). I picked the RYR and
forced
that
year because that was the year that had recoveries in all
locations
AND in
all species, but NOT all species IN all locations. of course.
Maybe
the