TWO Crosstab Query Ouput Questions

[Guest]

These are probably simple and I appreciate the patience of everyone. Yes, I
am trying the "search" with no success so far. I'll try to explain in more
detail below, but the basic questions are as follows:

1. How do I simply get the crosstab to show all rows/columns, even if there
are not values?

2. How do I get the crosstab to output values of 0 in those cells where it
is?

Although I am working with waterfowl band recoveries, the clear analogy
would be something like this:

There are 62 salesmen selling 16 products in each of 12 months. I want to
generate a crosstabe query to COUNT the quantities of each product sold by
each salesman in each month. The setup is no problem and SHOULD result in
992 (16X62) rows and 12 (Jan-Dec) value columns. Keeping those row numbers
in mind, we can even think of the simpler version as if there were only 3
salesmen (A/B/C) and 3 products (A/B/C) sold in 4 quarters. That SHOULD look
like this:

Salesman Product Q1 Q2 Q3 Q4
A A 2 3 0 1
A B 0 0 0 0
A B 2 3 0 1
B A 2 3 0 1
B B 2 3 0 1
B C 2 3 0 1
C A 2 3 0 1
C B 2 3 0 1
C C 2 3 0 1

If this were the value results, the crosstab query (as I am seeing it now)
would NOT show the row with Salesman "A" and Product "B" because there are no
values. It would also not show the Q3 column for the same reason. That is
the first question. The second question is because none of the 0's would
show up at all; they would be blank (nulls?). That appears to be a problem
when I get it into Excel and try to perform calculations there. That part
HAS to be easy, but is not yet obvious to me.

I have even been thinking about "workarounds". Could I simply ADD records
with values in a "not-to-be-used "Q5" column for all 992 rows that I wanted
to appear and in an extra row for all columns. These could be easily deleted
in the output, leaving just what I wanted in terms of the number of rows and
columns. I would still have to figure out how to make it output zero-values,
though. I'm afraid it would mean a LOT of records if I had the value, one
for each value cell in the desired output?. I might even be able to leave
the field that is counted BLANK and get by with only the 992 records and 12
(actually 80) columns? Would the query show these, but not include them in
the count?

For the record, the objective is to be able to past the output into the data
area of a much larger spreadsheet that performs various calculations.

I will keep searching and studying the query options/properties/field
formats/etc., but wanted to post the question in the hopes that it is easy
and obvious to someone. Thanks for any and all assistance.

Tuffy

 

[Guest]

Okay, I found the answer to Question #2. I CAN output zero values thanks to
and old answer from "Allen Browne". I think the other answer is here, but
I'm having trouble with the translations of what I have found so far. I'm
not afraid of the SQL side, but very inexperienced at this point.

 

[Guest]

1. How do I simply get the crosstab to show all rows/columns, even if there
are not values?
In the lqst line of the SQL -- PIVIOT fieldname ;
add a list of your output fields like this --
PIVIOT fieldname IN("Q1", "Q2", "Q3", "Q4");
List each in the order you want them to appear, in quotes, separated by
commas. You use the quotes if they have alpha characters and no quotes if
the output labels will contain only numerical digits -- 1, 2, 3, , , 11,
12 for numbering months.

 

[Guest]

Karl,

I'm making progress. Your advice does take care of the columns. Since
there is no "PIVIOT" line for the row titles, this only lets me have all
columns. I tried to add the "IN()" info to the line that does have the
columns, but that didn't seem to work. If I HAD to, I could switch the
rows/columns by combining the two fields that now make up the rows into a
single field as the column, use this advice to show all of those new columns,
and Paste/Transpose the results into a correct Excel spreadsheet. Even that
would leave me wondering if I can have an IN("","") line with 1000
values/commas in it? Come to think of it, I don't guess I could put that
many columns into Excel TO ever transpose and would have to try to
Paste/Transpose directly from the Access dataset and/or work on exporting
Transposed. I think that puts me back to: "How do I do this for the rows
now?". It might be a given that I will have to combine the two row fields
into a single one? Still, in what line do I add the IN("","") statement and
can it be that long?

It can only help to show the current SQL of my testing code. The Criteria
parts are only to limit the output for this learning exercise. Note that one
of the two current row labels already is a combined field. Remember that
this currently results in about 1,000 rows for the full dataset.

TRANSFORM Nz(Count(WORKTEST.[BAND]),0) AS CountOfBAND
SELECT [BFLY]+[BSTA] AS Expr1, WORKTEST.SPEC, Count(WORKTEST.[BAND]) AS
[Total Of BAND]
FROM WORKTEST
WHERE (((WORKTEST.RYR)>"1999") AND ((WORKTEST.BFLY)="4"))
GROUP BY [BFLY]+[BSTA], WORKTEST.SPEC, WORKTEST.BFLY
PIVOT WORKTEST.RYR IN ("2000","2001","2002","2003","2004","2005");

 

[David F Cox]

The technique is to set up a lookup table with all of the row values that
you want displayed in it and do a left join on it.

Please ask again if you run into problems, I regret I do not have time now
to go into detail.

Karl,

I'm making progress. Your advice does take care of the columns. Since
there is no "PIVIOT" line for the row titles, this only lets me have all
columns. I tried to add the "IN()" info to the line that does have the
columns, but that didn't seem to work. If I HAD to, I could switch the
rows/columns by combining the two fields that now make up the rows into a
single field as the column, use this advice to show all of those new
columns,
and Paste/Transpose the results into a correct Excel spreadsheet. Even
that
would leave me wondering if I can have an IN("","") line with 1000
values/commas in it? Come to think of it, I don't guess I could put that
many columns into Excel TO ever transpose and would have to try to
Paste/Transpose directly from the Access dataset and/or work on exporting
Transposed. I think that puts me back to: "How do I do this for the rows
now?". It might be a given that I will have to combine the two row fields
into a single one? Still, in what line do I add the IN("","") statement
and
can it be that long?

It can only help to show the current SQL of my testing code. The Criteria
parts are only to limit the output for this learning exercise. Note that
one
of the two current row labels already is a combined field. Remember that
this currently results in about 1,000 rows for the full dataset.

TRANSFORM Nz(Count(WORKTEST.[BAND]),0) AS CountOfBAND
SELECT [BFLY]+[BSTA] AS Expr1, WORKTEST.SPEC, Count(WORKTEST.[BAND]) AS
[Total Of BAND]
FROM WORKTEST
WHERE (((WORKTEST.RYR)>"1999") AND ((WORKTEST.BFLY)="4"))
GROUP BY [BFLY]+[BSTA], WORKTEST.SPEC, WORKTEST.BFLY
PIVOT WORKTEST.RYR IN ("2000","2001","2002","2003","2004","2005");

1. How do I simply get the crosstab to show all rows/columns, even if
there
are not values?
In the lqst line of the SQL -- PIVIOT fieldname ;
add a list of your output fields like this --
PIVIOT fieldname IN("Q1", "Q2", "Q3", "Q4");
List each in the order you want them to appear, in quotes, separated by
commas. You use the quotes if they have alpha characters and no quotes
if
the output labels will contain only numerical digits -- 1, 2, 3, , ,
11,
12 for numbering months.

 

[Guest]

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;

Karl,

I'm making progress. Your advice does take care of the columns. Since
there is no "PIVIOT" line for the row titles, this only lets me have all
columns. I tried to add the "IN()" info to the line that does have the
columns, but that didn't seem to work. If I HAD to, I could switch the
rows/columns by combining the two fields that now make up the rows into a
single field as the column, use this advice to show all of those new columns,
and Paste/Transpose the results into a correct Excel spreadsheet. Even that
would leave me wondering if I can have an IN("","") line with 1000
values/commas in it? Come to think of it, I don't guess I could put that
many columns into Excel TO ever transpose and would have to try to
Paste/Transpose directly from the Access dataset and/or work on exporting
Transposed. I think that puts me back to: "How do I do this for the rows
now?". It might be a given that I will have to combine the two row fields
into a single one? Still, in what line do I add the IN("","") statement and
can it be that long?

It can only help to show the current SQL of my testing code. The Criteria
parts are only to limit the output for this learning exercise. Note that one
of the two current row labels already is a combined field. Remember that
this currently results in about 1,000 rows for the full dataset.

TRANSFORM Nz(Count(WORKTEST.[BAND]),0) AS CountOfBAND
SELECT [BFLY]+[BSTA] AS Expr1, WORKTEST.SPEC, Count(WORKTEST.[BAND]) AS
[Total Of BAND]
FROM WORKTEST
WHERE (((WORKTEST.RYR)>"1999") AND ((WORKTEST.BFLY)="4"))
GROUP BY [BFLY]+[BSTA], WORKTEST.SPEC, WORKTEST.BFLY
PIVOT WORKTEST.RYR IN ("2000","2001","2002","2003","2004","2005");

1. How do I simply get the crosstab to show all rows/columns, even if there
are not values?
In the lqst line of the SQL -- PIVIOT fieldname ;
add a list of your output fields like this --
PIVIOT fieldname IN("Q1", "Q2", "Q3", "Q4");
List each in the order you want them to appear, in quotes, separated by
commas. You use the quotes if they have alpha characters and no quotes if
the output labels will contain only numerical digits -- 1, 2, 3, , , 11,
12 for numbering months.

 

[Guest]

Obviously, David and Karl are giving me the answer here, but I might be like
the character in an old Rutledge tale: I don't know enough to be told
anything. I'm learning fast, but still have to translate from
"what-I-want-to-do" to "Access Design View" (fairly clear on that) to "SQL
View" (Using the Greek translator as I go). In this case, I think I can
handle the Left-Join if I can create the second table with all rows in it.
My first thought was that it should be EASY, especially with Karl's
suggestion of running a Totals Query (crosstab?). Of course, I'm wrong
because that IS what I started out trying to do. I don't think I understand
what you mean by "Totals Query".

Perhaps I should go back and more directly layout the data involved, the
important fields, anyway. I am looking at a database of waterfowl band
recoveries with one record for each recovery and a total of a couple of
million records. Pertinent fields would be the Banding Flyway (BFLY),
Banding State (BSTA), Recovering Flyway (RFLY), Recovering State (RSTA) and
Species (SPEC). When I imported (from FileMaker via CSV) this Table (RECV),
I let Access create a "Key" field that is simply the number sequence of the
original records. That is the only numeric field. For clarity (and
sorting), I allow most queries to combine the Flyway and State fields into
(Expressions: RFLY+RSTA and BFLY+BSTA), which is, of course, no problem.

The two crosstabs that I want to create would have the RFLY+RSTA expression
as the first field of each row and the SPEC (Species) as the second field.
Thus, each of the 62 RFLY+RSTA Expressions should consist of a separate row
for each of the 16 species (SPEC) in question. The columns could be either
the BFLY+BSTA combo or the Recovered Year (RYR). As noted, every location
doesn't recover every species, so I am left with missing rows.

Now, when I try to create a table with every row, I am, in effect, trying to
create that same series of two-field-combination rows (Location and Species).
There simply are no records for many of those combinations. Yeah, THAT is
where I am being real dense here and I will be working on it, but could use
the bridge for that thought. If at all possible, the explanation is easier
in terms of "what-I-want-to-do" that I can later translate into SQL (via
Access Design View in my thinking?).

 

[Guest]

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

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;

Karl,

I'm making progress. Your advice does take care of the columns. Since
there is no "PIVIOT" line for the row titles, this only lets me have all
columns. I tried to add the "IN()" info to the line that does have the
columns, but that didn't seem to work. If I HAD to, I could switch the
rows/columns by combining the two fields that now make up the rows into a
single field as the column, use this advice to show all of those new columns,
and Paste/Transpose the results into a correct Excel spreadsheet. Even that
would leave me wondering if I can have an IN("","") line with 1000
values/commas in it? Come to think of it, I don't guess I could put that
many columns into Excel TO ever transpose and would have to try to
Paste/Transpose directly from the Access dataset and/or work on exporting
Transposed. I think that puts me back to: "How do I do this for the rows
now?". It might be a given that I will have to combine the two row fields
into a single one? Still, in what line do I add the IN("","") statement and
can it be that long?

It can only help to show the current SQL of my testing code. The Criteria
parts are only to limit the output for this learning exercise. Note that one
of the two current row labels already is a combined field. Remember that
this currently results in about 1,000 rows for the full dataset.

TRANSFORM Nz(Count(WORKTEST.[BAND]),0) AS CountOfBAND
SELECT [BFLY]+[BSTA] AS Expr1, WORKTEST.SPEC, Count(WORKTEST.[BAND]) AS
[Total Of BAND]
FROM WORKTEST
WHERE (((WORKTEST.RYR)>"1999") AND ((WORKTEST.BFLY)="4"))
GROUP BY [BFLY]+[BSTA], WORKTEST.SPEC, WORKTEST.BFLY
PIVOT WORKTEST.RYR IN ("2000","2001","2002","2003","2004","2005");

1. How do I simply get the crosstab to show all rows/columns, even if there
are not values?
In the lqst line of the SQL -- PIVIOT fieldname ;
add a list of your output fields like this --
PIVIOT fieldname IN("Q1", "Q2", "Q3", "Q4");
List each in the order you want them to appear, in quotes, separated by
commas. You use the quotes if they have alpha characters and no quotes if
the output labels will contain only numerical digits -- 1, 2, 3, , , 11,
12 for numbering months.


:

Okay, I found the answer to Question #2. I CAN output zero values thanks to
and old answer from "Allen Browne". I think the other answer is here, but
I'm having trouble with the translations of what I have found so far. I'm
not afraid of the SQL side, but very inexperienced at this point.

:

These are probably simple and I appreciate the patience of everyone. Yes, I
am trying the "search" with no success so far. I'll try to explain in more
detail below, but the basic questions are as follows:

1. How do I simply get the crosstab to show all rows/columns, even if there
are not values?

2. How do I get the crosstab to output values of 0 in those cells where it
is?

Although I am working with waterfowl band recoveries, the clear analogy
would be something like this:

There are 62 salesmen selling 16 products in each of 12 months. I want to
generate a crosstabe query to COUNT the quantities of each product sold by
each salesman in each month. The setup is no problem and SHOULD result in
992 (16X62) rows and 12 (Jan-Dec) value columns. Keeping those row numbers
in mind, we can even think of the simpler version as if there were only 3
salesmen (A/B/C) and 3 products (A/B/C) sold in 4 quarters. That SHOULD look
like this:

Salesman Product Q1 Q2 Q3 Q4
A A 2 3 0 1
A B 0 0 0 0
A B 2 3 0 1
B A 2 3 0 1
B B 2 3 0 1
B C 2 3 0 1
C A 2 3 0 1
C B 2 3 0 1
C C 2 3 0 1

If this were the value results, the crosstab query (as I am seeing it now)
would NOT show the row with Salesman "A" and Product "B" because there are no
values. It would also not show the Q3 column for the same reason. That is
the first question. The second question is because none of the 0's would
show up at all; they would be blank (nulls?). That appears to be a problem
when I get it into Excel and try to perform calculations there. That part
HAS to be easy, but is not yet obvious to me.

I have even been thinking about "workarounds". Could I simply ADD records
with values in a "not-to-be-used "Q5" column for all 992 rows that I wanted
to appear and in an extra row for all columns. These could be easily deleted
in the output, leaving just what I wanted in terms of the number of rows and
columns. I would still have to figure out how to make it output zero-values,
though. I'm afraid it would mean a LOT of records if I had the value, one
for each value cell in the desired output?. I might even be able to leave
the field that is counted BLANK and get by with only the 992 records and 12
(actually 80) columns? Would the query show these, but not include them in
the count?

For the record, the objective is to be able to past the output into the data
area of a much larger spreadsheet that performs various calculations.

I will keep searching and studying the query options/properties/field
formats/etc., but wanted to post the question in the hopes that it is easy
and obvious to someone. Thanks for any and all assistance.

Tuffy

 

[David F Cox]

->) let us suppose we have a status of "recovered" or "Died"
and a result of "flew south" or "flew North" or Null
and you want a crosstab with all combinations of rows
You might want to see the results in the combination "Died, flew south" to
quatify operator errors, and might want to show that row regardless to show
that you were open to that possibility.

I was suggesting that you build a table with all of the possibilities that
you wish to see in the rows, and link that to the matching records, if any.
It is because there will be some unmatched that you will have to use the
left join. It is the fields from the lookup table that you use as the row
source. If I have time I will try and dig out a previous thread where we
solved a similar problem, but now I have a meal to cook.

 

[Guest]

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.

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

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;

Karl,

I'm making progress. Your advice does take care of the columns. Since
there is no "PIVIOT" line for the row titles, this only lets me have all
columns. I tried to add the "IN()" info to the line that does have the
columns, but that didn't seem to work. If I HAD to, I could switch the
rows/columns by combining the two fields that now make up the rows into a
single field as the column, use this advice to show all of those new columns,
and Paste/Transpose the results into a correct Excel spreadsheet. Even that
would leave me wondering if I can have an IN("","") line with 1000
values/commas in it? Come to think of it, I don't guess I could put that
many columns into Excel TO ever transpose and would have to try to
Paste/Transpose directly from the Access dataset and/or work on exporting
Transposed. I think that puts me back to: "How do I do this for the rows
now?". It might be a given that I will have to combine the two row fields
into a single one? Still, in what line do I add the IN("","") statement and
can it be that long?

It can only help to show the current SQL of my testing code. The Criteria
parts are only to limit the output for this learning exercise. Note that one
of the two current row labels already is a combined field. Remember that
this currently results in about 1,000 rows for the full dataset.

TRANSFORM Nz(Count(WORKTEST.[BAND]),0) AS CountOfBAND
SELECT [BFLY]+[BSTA] AS Expr1, WORKTEST.SPEC, Count(WORKTEST.[BAND]) AS
[Total Of BAND]
FROM WORKTEST
WHERE (((WORKTEST.RYR)>"1999") AND ((WORKTEST.BFLY)="4"))
GROUP BY [BFLY]+[BSTA], WORKTEST.SPEC, WORKTEST.BFLY
PIVOT WORKTEST.RYR IN ("2000","2001","2002","2003","2004","2005");


:

1. How do I simply get the crosstab to show all rows/columns, even if there
are not values?
In the lqst line of the SQL -- PIVIOT fieldname ;
add a list of your output fields like this --
PIVIOT fieldname IN("Q1", "Q2", "Q3", "Q4");
List each in the order you want them to appear, in quotes, separated by
commas. You use the quotes if they have alpha characters and no quotes if
the output labels will contain only numerical digits -- 1, 2, 3, , , 11,
12 for numbering months.


:

Okay, I found the answer to Question #2. I CAN output zero values thanks to
and old answer from "Allen Browne". I think the other answer is here, but
I'm having trouble with the translations of what I have found so far. I'm
not afraid of the SQL side, but very inexperienced at this point.

:

These are probably simple and I appreciate the patience of everyone. Yes, I
am trying the "search" with no success so far. I'll try to explain in more
detail below, but the basic questions are as follows:

1. How do I simply get the crosstab to show all rows/columns, even if there
are not values?

2. How do I get the crosstab to output values of 0 in those cells where it
is?

Although I am working with waterfowl band recoveries, the clear analogy
would be something like this:

There are 62 salesmen selling 16 products in each of 12 months. I want to
generate a crosstabe query to COUNT the quantities of each product sold by
each salesman in each month. The setup is no problem and SHOULD result in
992 (16X62) rows and 12 (Jan-Dec) value columns. Keeping those row numbers
in mind, we can even think of the simpler version as if there were only 3
salesmen (A/B/C) and 3 products (A/B/C) sold in 4 quarters. That SHOULD look
like this:

Salesman Product Q1 Q2 Q3 Q4
A A 2 3 0 1
A B 0 0 0 0
A B 2 3 0 1
B A 2 3 0 1
B B 2 3 0 1
B C 2 3 0 1
C A 2 3 0 1
C B 2 3 0 1
C C 2 3 0 1

If this were the value results, the crosstab query (as I am seeing it now)
would NOT show the row with Salesman "A" and Product "B" because there are no
values. It would also not show the Q3 column for the same reason. That is
the first question. The second question is because none of the 0's would
show up at all; they would be blank (nulls?). That appears to be a problem
when I get it into Excel and try to perform calculations there. That part
HAS to be easy, but is not yet obvious to me.

I have even been thinking about "workarounds". Could I simply ADD records
with values in a "not-to-be-used "Q5" column for all 992 rows that I wanted
to appear and in an extra row for all columns. These could be easily deleted
in the output, leaving just what I wanted in terms of the number of rows and
columns. I would still have to figure out how to make it output zero-values,
though. I'm afraid it would mean a LOT of records if I had the value, one
for each value cell in the desired output?. I might even be able to leave
the field that is counted BLANK and get by with only the 992 records and 12
(actually 80) columns? Would the query show these, but not include them in
the count?

For the record, the objective is to be able to past the output into the data
area of a much larger spreadsheet that performs various calculations.

I will keep searching and studying the query options/properties/field
formats/etc., but wanted to post the question in the hopes that it is easy
and obvious to someone. Thanks for any and all assistance.

Tuffy

 

[Guest]

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

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.

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

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;

 

[David F Cox]

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.

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

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.

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


:

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;

 

[Guest]

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];

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.

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

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.

:

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


:

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;

 

[Guest]

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 re: 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;

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];

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.

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


:

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.

:

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


:

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;

 

[David F Cox]

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.

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 re: 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;

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];

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.

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


:

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.

:

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


:

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;

 

[Guest]

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

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.

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 re: 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;

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];


:

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.

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

 

[Guest]

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.

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.

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 re: 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;

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];


:

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.

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

 

[David F Cox]

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

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.

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.

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 re: 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;







:

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];


:

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.

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