Question about "Group By" Query without summing

bsmithenviro

New member
Local time
Today, 05:54
Joined
Jan 15, 2015
Messages
4
Hello all! I have what I think is a simple task, but I can't find the available hints on how to do it, so I had to post here.

I have a a dataset that has recorded encounters with a number of Banded (identified) animals. It is currently setup so that each encounter is its own row; with all the information, including "Band_Num" repeated, but "Encounter_Date" being unique. I wanted to query the table and get it to display the results as "Band_Num" on a row, followed by x columns for with each unique "Encounter_Date" represented. I thought a "Group By" function would help, but that requires a sum, which is not helpful.

Complicating this is the fact that not every individual is seen a set number of times, so Animal 1 might be seen 3 different dates, but Animal 2 only on 1 date.

Any help with this will be much appreciated
 
Thanks, that video was interesting; but I am having trouble implementing it. I currently have the query set up with 3 fields selected: "Band_Num" for Row Heading, "Encounter_Date" for Value (Total set to 'First'), and "Encounter_Date" as the "Column Heading".

However, since there are so many recorded encounters, its over the 796 columns. Really all I want is just generic columns beyond "Band_Num", such as "Encounter 1", "Encounter 2" etc etc.

Any ideas on how to get that to work?
 
You're going to need a subquery to order each encounter. It's essentially a running total query (search the forum for that term), that determines what position each encounter is in.

The data in that subquery will look like this:

Band_Num, Encounter_Date, Encounter_Number
1234, 1/1/2014, 1
1234, 1/2/2014, 2
999, 1/2/2014, 1
5544, 1/3/2014, 1
1234, 2/1/2014, 3
999, 2/2/2014, 2

Then you cross-tab that query using Encounter_Number as the Column Heading. The key is determine the appropriate Encounter_Number for each record. It basically is a Dcount call that sees how many of times that Band_Num has had an encounter prior to that record's Encounter_Date value.
 

Users who are viewing this thread

Back
Top Bottom