count in report

[kishanpatel50]

i have made a report from a criteria query. the report shows records since a date specified earlier.
a table holds a collection of articles and another table with studentID and ArticleID and BorrowedDate.

i have got the report thats shows the articles borrowed since a specified date.
i have a column in the report that i want to count the total number of articles the student has, but i can only count how many the student has since the specified date.

For example my Borrowed table hold on

StudentID ArticleID DateBorrowed
7759 ENG001 01/02/06
7759 MAT001 10/02/06
7759 ENG002 11/02/06

if i specified the date 05/02/06 the report will show the last 2 records..but i want the count column to show 3 not 2 because 7759 has a total of 3 article

 

[Rickster57]

count

You could use a subreport which uses your entire table and not just those dates used in the criteria.
Hope this helps.

 

[mhartman]

Hello:

=DCount("[ArticleID]","YourTableNameHere","StudentID = 7759")

Place this as the ControlSource in a text box on your form or report.

This should count all the articles student 7759 has.

Regards
Mark

 

[kishanpatel50]

the problem with that is that i dont only want 7759 i want it for every other student aswell

i tried this..a cutting is an article

so this i assume counts the number of cuttings in the studentcuttingtable when the studentid in the studentcuttingtable = the studentid in the report

=DCount("[CuttingID]","[StudentCuttingTable]","[!StudentCuttingTable]![StudentID] = [StudentID]")

 

[mhartman]

Hello:

=DCount("[ArticleID]","YourTableNameHere)

Place this as the ControlSource in a text box on your form or report.

This should count all the articles.

 

[kishanpatel50]

im sorry about this but thats not what i want

example StudentCuttingTable

StudentID CuttingID DateBorrowed
7790 MAT001 01/01/2006
7790 MAT002 01/01/2006
7762 MAT003 05/01/2006
7762 ENG001 06/01/2006
7790 ENG002 07/01/2006

ive made a report to show the records where BorrwedDate is >04/01/2006
so this is what my report will look like
it is grouped by StudentID
it also has a count column _ are spaces

StudentID_CuttingID___DateBorrowed___Count____What_count_should_be

7762_____ MAT003___05/01/2006_____2_____________2
__________ENG001___06/01/2006
7790_____ ENG002____07/01/2006_____1_____________3

 

[Wiz47]

im sorry about this but thats not what i want

example StudentCuttingTable

StudentID CuttingID DateBorrowed
7790 MAT001 01/01/2006
7790 MAT002 01/01/2006
7762 MAT003 05/01/2006
7762 ENG001 06/01/2006
7790 ENG002 07/01/2006

ive made a report to show the records where BorrwedDate is >04/01/2006
so this is what my report will look like
it is grouped by StudentID
it also has a count column _ are spaces

StudentID_CuttingID___DateBorrowed___Count____What_count_should_be

7762_____ MAT003___05/01/2006_____2_____________2
__________ENG001___06/01/2006
7790_____ ENG002____07/01/2006_____1_____________3

If you want to widen the search, specify a date between two dates (or for the year to date)

FieldDate BETWEEN beginningdate and Endingdate. I have a form where I specify the dates I need between two dates

(tblMyTable.Ending_Date Between Forms!frmReport!txtBegDate And Forms!frmReport!txtEndDate)

This returns all dates between and including those two dates. So in your case, you would specify a date between 1/1/2006 and 7/1/2006 and it would capture all three.