Is there any way to count the number of entries

[thesuggs76]

Hi There
I'm working with a table that has a list of the different referrals. They have a start date and an end date. I'm trying to work a way to count all the number of referrals that have been received during a certain time period for each individual. The data only looks at those who have been discharged during a certain time period.
If I'm looking at the number of referrals for the period of 01/01/12 -> 31/12/12 some of the referrals made would have been before the 01/01/12 I don't want to include those it is only where the referral date is between the time period. I also need to count the number of days between start and end date. However if the referral was made before the start date I need to start the count from the start of the period. For example if Joe Bloggs was referred on the 10/12/11 his start date would be 01/01/12. So it would be 0 referrals and 365 days.
Any suggestions would be much appreciated??

 

[alansidman]

Create an aggregate query. Look at this link.

MS Access: Count Function

 

[thesuggs76]

Create an aggregate query. Look at this link.

MS Access: Count Function

Thanks for replying. The problem that I have is that some of the referrals are before the due date and they have no discharge date so it would mean 0 no. of referrals and 365 days. However some of the referalls may be before the start date but have a discharge date of October, they'll then be referred again. So here it would be 1 referral and then I would need the total of both days. How can I do the count function where it will sum the number of days but not include if referral date is before the period start

thanks

 

[xenou]

Take it in steps. First query for the data that is relevant -- push it to a temp table. Then update dates or delete records as needed so they will work correctly for the counting, either directly updating the fields in the temp table or updating extra fields for the adjusted values. Then when everything is as it should be to get the right answer, you count the records and get your sums. Sometimes you need to be creative with more than one query that are joined together for the final result "as if" you had done it all at once.

 

[thesuggs76]

Take it in steps. First query for the data that is relevant -- push it to a temp table. Then update dates or delete records as needed so they will work correctly for the counting, either directly updating the fields in the temp table or updating extra fields for the adjusted values. Then when everything is as it should be to get the right answer, you count the records and get your sums. Sometimes you need to be creative with more than one query that are joined together for the final result "as if" you had done it all at once.

Thanks. I just got it working myself there. With the count I added a field so if the referral date was before a certain period, it was 1 otherwise 0. I then grouped them together and in a seperate query did a simple subtract between the count and the new field i added.