0

I am working on fish growth rates so I need to pull weight and date for each time a single fish was measured.

Right now each capture record is on a separate line. I basically want to pull pitcode, species, tagdate1, length1, weight1, tagdate2, length2, weight2, tagdate3, etc.. from this table, grouped by pitcode and sorted by tagdate. I'm using Microsoft Access. Thank you in advance for any suggestions.

pitcode         tagdate       length  weight    species
3D9.1C2D9C7FCE  7/26/2011   213 118 3
3D9.1C2D9F3AB2  7/26/2011   148 38  3
3D9.1C2D9F1627  7/26/2011   215 116 3
3D9.1C2D60CDC6  7/26/2011   165 58  3
3D9.1C2D9F0797  7/26/2011   244 204 3
3D9.1C2D9BA47F  7/26/2011   143 47  3
3D9.1C2D9FD674  7/27/2011   226 183 3
3D9.1C2DA1C597  7/27/2011   123 27.2    3
3D9.1C2D9FE09F  7/27/2011   241 182.3   3
3D9.1C2D9FE4D8  7/27/2011   286 301 3

I found this "self join" from another post, and it works: 

SELECT
  a.pitcode, 
  MIN(a.tagdate) AS td1, MIN(b.tagdate) AS td2, 
  MIN(a.weight) AS wt1, MIN(b.weight) AS wt2
FROM
  tagd a
  LEFT JOIN tagd b
  ON a.pitcode = b.pitcode AND b.tagdate > a.tagdate and b.weight>a.weight
GROUP BY
  a.pitcode;
fthiella
  • 48,073
  • 15
  • 90
  • 106
yakamafish
  • 1
  • 5
  • possible duplicate of [Combine rows in Access 2007](http://stackoverflow.com/questions/92698/combine-rows-in-access-2007) This is just one example with the same problem there are at least three others. – Fionnuala Jan 24 '13 at 16:17
  • I guess I'm wondering if it's possible entirely with sql. I'm not familiar with VB, which I think this is using. Maybe something similar to the lag function in SAS? – yakamafish Jan 24 '13 at 16:23
  • No it is not possible to group records in MS Access on to one line with SQL. – Fionnuala Jan 24 '13 at 16:34
  • okay. thank you. I'll have to try something else then. I appreciate the response. – yakamafish Jan 24 '13 at 19:54
  • I found this from another post, and it works: – yakamafish Jan 24 '13 at 20:37
  • It works for a predefined number of rows, not a variable number of rows. In the example you show for 2 rows, but in your question you refer to at least 3 rows. – Fionnuala Jan 24 '13 at 20:52
  • 3
    yakamafish, you should put your solution down below and answer your own question so people know it has been solved. – N1tr0 Jan 25 '13 at 20:43

0 Answers0