count records in tbl based on frm cbo criteria

[ppoindexter]

how do i count records in a table based on 3 user selected filters (combo box) on form and then display the record counts on form and have it update when filters are changed on form? any help is greatly apprecitated!

 

[PNGBill]

Do the filters reduce the number of records in the datasource and on the form?

If so, wouldn't the forms record count get this value and update it as the form is updated?

 

[ppoindexter]

no, the filters have two purposes..filter tbl1 and show results in the subfrm (got this working fine) and i am also trying to use cbo's to filter tbl2 and count records based on records that meet criteria in the filters...i tried dcount and have also tried a query which does count the records but i dont know how to fire it after the combo update and i cant figure out how to display the query on the subfrm (in a textbox)

 

[PNGBill]

A text box on the subform should get the data from the query with DLookup("[fieldname]", "QueryName"). May need to use =DL... as the form field datasource.

Can the filter command also include a line to run the query or may not be needed if the form refresh action when you apply the filter gets a new value from the query - test and see how it works.

 

[ppoindexter]

i am getting "error" in the textbox on the subfrm with this code in the control source =DLookUp("[NoTimesUsed]","qyrcountstrand_frmscope")

below is the sql statement for the query ...any ideas on the error message??

SELECT Count(*) AS ["NoTimesUsed"], IIf(IsNull([Forms]![frmscope]![cbostrand]),[tbllevel1]![fldlevel1id]=[tbllevel1]![fldlevel1id] Or ([tbllevel1]![fldlevel1id])=IsNull([tbllevel1]![fldlevel1id]),[tbllevel1]![fldlevel1id]=[Forms]![frmscope]![cbostrand]) AS Strand, IIf(IsNull([Forms]![frmscope]![cbograde]),[tbllevel2]![fldlevel2id]=[tbllevel2]![fldlevel2id] Or ([tbllevel2]![fldlevel2id])=IsNull([tbllevel2]![fldlevel2id]),[tbllevel2]![fldlevel2id]=[Forms]![frmscope]![cbograde]) AS Grade
FROM (tblsequencetplan INNER JOIN tbllevel1 ON tblsequencetplan.fldlevel1id = tbllevel1.fldlevel1id) INNER JOIN tbllevel2 ON tblsequencetplan.fldlevel2id = tbllevel2.fldlevel2id
GROUP BY IIf(IsNull([Forms]![frmscope]![cbostrand]),[tbllevel1]![fldlevel1id]=[tbllevel1]![fldlevel1id] Or ([tbllevel1]![fldlevel1id])=IsNull([tbllevel1]![fldlevel1id]),[tbllevel1]![fldlevel1id]=[Forms]![frmscope]![cbostrand]), IIf(IsNull([Forms]![frmscope]![cbograde]),[tbllevel2]![fldlevel2id]=[tbllevel2]![fldlevel2id] Or ([tbllevel2]![fldlevel2id])=IsNull([tbllevel2]![fldlevel2id]),[tbllevel2]![fldlevel2id]=[Forms]![frmscope]![cbograde])
HAVING (((IIf(IsNull([Forms]![frmscope]![cbostrand]),[tbllevel1]![fldlevel1id]=[tbllevel1]![fldlevel1id] Or ([tbllevel1]![fldlevel1id])=IsNull([tbllevel1]![fldlevel1id]),[tbllevel1]![fldlevel1id]=[Forms]![frmscope]![cbostrand]))<>False) AND ((IIf(IsNull([Forms]![frmscope]![cbograde]),[tbllevel2]![fldlevel2id]=[tbllevel2]![fldlevel2id] Or ([tbllevel2]![fldlevel2id])=IsNull([tbllevel2]![fldlevel2id]),[tbllevel2]![fldlevel2id]=[Forms]![frmscope]![cbograde]))<>False));

 

[PNGBill]

Not sure if you answered the question - does the query work without worrying about the form and DLookup ?

Try SELECT Count(*) AS [NoTimesUsed] instead of SELECT Count(*) AS ["NoTimesUsed"]

 

[PNGBill]

It may need to be Select Count(*) AS "fieldName" (remove [])

 

[PNGBill]

Looking at my sql text book you don't need to set a field name.
Select Count(*) From TableName Where .... should return a value.

If you still have a problem, Make 2nd query that just reads Select Count (*) from QryName and use this as your DLookup - if possible

Other text book example uses Select Count(*) "newfieldname" from tablename where criteria

Which implies your ["fieldname"] should have been "fieldname"

 

[ppoindexter]

thanks for all your help ..the query is working fine now....i just need to somehow match its results with each recordset showing on the subfrm..i have the subfrm set to continuous and would like the query results to show for each recordset (see below)...i am probably confusing you with this question...

subfrm qry shows records for first two fields...2nd query counts records found for and needs to display for 3rd field
(see below) matched with the appropriate subject

fldsubject
math
english

fldnumberofskillstoteach
8
5

fldnumberoftimesskilltaught
4
3

 

[PNGBill]

Your continuous subform has a record for each subject ? and you want a record count related to this subject ?

Build into the query that supplies data to the subform the record count, don't do the count but join a query that groups on the same field that is your key field in your subform.

eg, if the subform data is assembled around a subject name then have a query sum the records by subject name and join this into your query with the sum field included.

Run the query and you should have your record count for each subject - I think

 

[ppoindexter]

eureka!! that did the trick...thanks so much pngbill!!!