Re: TWO Crosstab Query Ouput Questions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

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

more later.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Tuffy


"KARL DEWEY" wrote:

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

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

"TuffyE" wrote:

Karl,

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

Tuffy


"KARL DEWEY" wrote:

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





.

Relevant Pages

  • RE: TWO Crosstab Query Ouput Questions
    ... locations for each species as rows with the Banding ... all recoveries in that location for each of those species. ... the SQL of the current crosstab query looks like this: ...
    (microsoft.public.access.queries)
  • Re: TWO Crosstab Query Ouput Questions
    ... that doesn't do it without the dummy records. ... and only 1098 in the final query. ... locations for each species as rows with the Banding ... all recoveries in that location for each of those species. ...
    (microsoft.public.access.queries)
  • Re: TWO Crosstab Query Ouput Questions
    ... There will be only a limited number of crosstab ... overall data for the 17 species, but that is just about all of the records ... and only 1098 in the final query. ... all recoveries in that location for each of those species. ...
    (microsoft.public.access.queries)
  • Re: convert to multiple columns
    ... Q.Species < t1.Species) AS Rank ... feed the table to the crosstab query. ... Site Transect Species Cover Density ...
    (microsoft.public.access.queries)
  • RE: TWO Crosstab Query Ouput Questions
    ... suggestion of running a Totals Query. ... as the first field of each row and the SPEC (Species) as the second field. ... How do I simply get the crosstab to show all rows/columns, ... Although I am working with waterfowl band recoveries, ...
    (microsoft.public.access.queries)