Question Count records on subform to display in mainform listbox (1 Viewer)

josephbupe

Registered User
Joined
Jan 31, 2008
Messages
225
I am trying to help my office in tracking cases recorded on individual files.

1. tblFiles (FileID=PK)
2. tblCase (FileID=FK)
3. tblCase_Notes

Any one File can have many Cases on it; and one Case can have as many notes as possible.

In the table "tblCases" I have included a field to store status of the case (Open/Closed).

On the main form "frmFiles" is a listbox of the Files stored in the table "tblFiles" and I can open the details form of any given file by double-click in the listbox. Now in the same listbox I want to include a field "Open Cases" to show a total count of all cases on a particular File with the status "Open". This should be obviously DCount Function but never used it before.

The actual records to count are in the table "tblCases" and I intend to have counts stored in "tblFiles" so that the counts will show in the listbox.

I will appreciate your help.

Joseph
 

Attachments

DCrake

Remembered
Joined
Jun 8, 2005
Messages
8,634
If you create a new query call QryOpenCases and include you cases table

Group By File and Count(1) and set a filter where status = Open

Then create a new query that includes both the files table and cases table. Create a join on the file id and bring down the count from the QryOpenCases.
 

rainman89

I cant find the any key..
Joined
Feb 12, 2007
Messages
3,016
Check out this sample of your Db

I added a casestatus table and created a new query called open cases and based your listbox off of that


EDIT: Essentially I did what David suggested.
 

Attachments

josephbupe

Registered User
Joined
Jan 31, 2008
Messages
225
Hi David, and theRainman,

Thank you so much for all the help you have given me on this one.

The query counts all records. Is it possible to count only records (cases) with the status "Open" and if status is "Closed" the value should be "0".

Joseph
 
Last edited:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom