Count and Count

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hello all, I have a query where I need to count the number of records
and then I need to count the different number of instances within that
same column.. Here is the code that does not work.. I want to know the
total number of trees (44)from plotnum and then how many different
counts in plotnum (3).... so 10 records all might have 1 in the plotnum
and 10 records with 2 in plotnum etc etc..

SELECT tblAdminUnit.BAF, Count(tblPlots.PlotNum) AS NumberTrees,
Count(Select Distinct tblPlots.PlotNum FROM tblPlots ORDER BY
tblPlots.PlotNum) AS NumberPlots
FROM tblAdminUnit LEFT JOIN tblPlots ON tblAdminUnit.UnitID =
tblPlots.UnitID
WHERE (((tblPlots.UnitID)=[forms]![frmSummarize]![cboCruiseID]))
GROUP BY tblAdminUnit.BAF;
 
Try these two queries.
Brian_1 ----
SELECT Sum(tblPlots.PlotNum) AS NumberTrees
FROM tblPlots;

SELECT tblAdminUnit.BAF, tblAdminUnit.UnitID, tblPlots.PlotNum,
Brian_1.NumberTrees
FROM Brian_1, tblAdminUnit INNER JOIN tblPlots ON tblAdminUnit.UnitID =
tblPlots.UnitID;
 
Back
Top