Query Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible in one query to count the total number of records in a table
AND count the number of records in the same table which meet a criteria THEN
append this information to another table? Or should it be done in steps?
 
Why do you need to store this values in a table?
There is no need to store it, you can always use query or code to retrieve
the total of records
*******************
DCount("*","TableNAme")

will return the total of records in the table

Or, in query

Select Count(*) As CountOfRecods From TableNAme
******************
The count of records including a criteria

DCount("*","TableNAme","FieldName=Criteria")

Or, using a query

Select Count(*) As CountOfRecods From TableNAme Where FieldNAme = SomeValue

**************
The value of the count chages all the time and storing this value might not
be valid the next time you view it.
 
SELECT DCount("[ID]","ASIF") AS TotalAll,
Count(ASIF.ID) AS TotalCriteria
FROM ASIF
WHERE ASIF.ID>1000;

You should be able to convert the above to an append query after you put in
the proper table and field names.
 
Another technique would be:

SELECT Count(*) as RecCount, SUM(IIF([someField] = [SomeValue], 1, 0) as
CountCriteriaMet
FROM your table

To append this, try:

INSERT INTO yourTable2 (Field1, Field2)
SELECT ...

HTH
Dale
 
Back
Top