Counting Disticnt Records

[Misha]

Hello,

Let me start off by saying thank you for any help. What I am trying to do
is to count the distinct amount of months that are in my table (tblInvoices)
based on the date they were recieved (rcvdBill).

rcvdBill format is mm/dd/yyyy


For instance:

Company 1:
Invoice A: rcvd 5/1/2006
Invoice B: rcvd 5/3/2006
Invoice C: rcvd 6/1/2006
Invoice D: rcvd 10/1/2006

What I would like is for the Query (or whatever else) to calculate the
disticnt months, which in this case is 3.

Again thank you for your help, and if you need me to clarify anything please
dont hesitate to ask.

 

[NathanJ]

Misha,

One way to do this would be to query your query. Make a query Select
DISTINCT month; save it Q1 then create a new query and in the design
view>Add table click the queries tab and select Q1 then do : Select
Count(DistinctMonths) from Q1;

This should give you a start on the results you're after.
Hope this helps

-Nathan

 

[Misha]

Nathan,

Thank you so much that worked perfectly..I have a follow up question
regarding that. What if I want to modify it for both month and year.


This is my current Query which does it for months only, how should I modify
it so that it selects the distinct month and year:

SELECT DISTINCT frmContract.ContractID, DatePart("m",frminvoice.recieve) AS
Expr1, Sum(frminvoice.[amount approved]) AS [SumOfamount approved]
FROM frmContract INNER JOIN frminvoice ON frmContract.[Contract
No]=frminvoice.[contract no]
GROUP BY frmContract.ContractID, DatePart("m",frminvoice.recieve)
HAVING (((frmContract.ContractID)=Forms.TestForm.cboVendor));


-Misha