Count records in query

[mafhobb]

Hello

How do I count the number of records that this query brings up?

SELECT SubCalls.SubCallID, SubCalls.CallID, Calls.ContactID
FROM Calls INNER JOIN SubCalls ON Calls.CallID = SubCalls.CallID
GROUP BY SubCalls.SubCallID, SubCalls.CallID, Calls.ContactID
HAVING (((Calls.ContactID)=5678));

Thanks

mafhobb

 

[RainLover]

I found the following in Access Help files.

Count the number of records in an underlying query (MDB)Show All
Hide All
Use the Count and DCount functions to count the number of records in a domain, such as a query. Use the Count function when writing SQL statements, and the DCount function when writing Visual Basic for Applications (VBA) code, macros, and expressions. Note that you can't use these functions in a data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.). Additionally, you can't use the DCount function in an Access project (.adp).
ExpressionDescriptionCount([OrderID])Uses the Count function to display the number of records in the current query.Count(*)Uses the Count function to count the number of records in the query, including records with Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) (blank) fields.DCount("[ShippedDate]", "Orders", "[ShipCountry] = '" & strCountry & _ "' AND [ShippedDate] > #" & dteShipDate & "#")Returns the number of orders in the Orders table that were shipped to a specified country/region (strCountry) after a specified ship date (dteShipDate). The domain is the Orders table.

 

[mafhobb]

I had been using dcount until now, but this involves two different tables and so a query seemed to be the way to go.

I've got the query returning the correct results so I have to believe that I can do some sort of count on the query, right?

mafhobb

 

[mafhobb]

The only way I have found to do this for now is to use this query:

SELECT SubCalls.SubCallID, SubCalls.CallID, Calls.ContactID
FROM Calls INNER JOIN SubCalls ON Calls.CallID = SubCalls.CallID
GROUP BY SubCalls.SubCallID, SubCalls.CallID, Calls.ContactID;

And then add a dcount calling this query such as:

DCount("SubcallID", "Stats Customer Subcalls", "ContactID=" & CID)

(The CID is a variable so I can search different customer IDs)

It seems kind of cumbersome...

mafhobb

 

[RainLover]

Using a Query as your Record Source is fine.

But if you had 500 records then you would have 500 calculations.

Suggest you use a Text Box on the Form or Report and use the DCount Function. This will require only one calculation.

 

[mafhobb]

Hi RainLover.

I do have a textbox in the form and I did try to use Dcount, but I could not get the syntax right to get the correct result. What would be the Dcount syntax that would do the same as the code above?

mafhobb

 

[RainLover]

Does this work;

DCount("SubcallID", "Stats Customer Subcalls", "ContactID=" & CID)

If it does then add it to the record source of the Text Box. Just add an = sign at the beginning.

This part of the DCount, namely the where clause, varies depending on the Data type. ie Number, Text, Date etc.

 

[mafhobb]

This is what I ended up with:

SELECT TOP 1 SubCalls.SubCallID, Calls.CallID, Calls.ContactID, SubCalls.SubCallDate
FROM Calls INNER JOIN SubCalls ON Calls.CallID = SubCalls.CallID
GROUP BY SubCalls.SubCallID, Calls.CallID, Calls.ContactID, SubCalls.SubCallDate
HAVING (((Calls.ContactID)=[forms]![frmstatsSpecCustomer]![txtContactID]))
ORDER BY SubCalls.SubCallDate;

followed by this:

FDate = DLookup("SubcallDate", "Stats Spec Customer First Date")

Thanks!

mafhobb

 

[RainLover]

Change this

Stats Customer Subcalls

to

[Stats Customer Subcalls]

Better still don't use spaces.