select and count record with VB

[stefanocps]

Hello need help to find the righjt instructions for this
From my TABLEA i have all users, birth, sex, study, job sex ecc and date of treatmntes
Need to select all record that under a range of date i supply supply me the number of all the user according to a filter, that can be kind of job, age range,studies carriers
And i need to have them separate, Female count and Male count
I have developed several queries, 2 for each filter, because one is fot the F and another is for M
Then from a table wher ei have all the queires name i read the queries name and count th number of record
this

Dim DBCorrente As DAO.Database
Dim rsRiepilogo As DAO.Recordset
Set DBCorrente = CurrentDb
Set rsRiepilogo = DBCorrente.OpenRecordset("Riepilogo", dbOpenDynaset)
rsRiepilogo.MoveFirst
Do Until rsRiepilogo.EOF
     rsRiepilogo.Edit
     rsRiepilogo!CountQuery = DCount("*", rsRiepilogo!NameQuery)
     rsRiepilogo.Update
     rsRiepilogo.MoveNext
Loop
rsRiepilogo.Close
DBCorrente.Close

i would like to have less queries, not to have 2 for each filter for m and f
and i think i could add an IIF inside the loop to check the Sex fields and according adding to the proper field of the RIEPILOGO table
SOmething like
IIf ((Riepilogo.Namequery.SEX) = "m"), TotM = TotM+1, TotF=TotF+1
instead of the DCOUNT line

but don t work


any help?
thans a lot

[pbaldy]

There's likely a better solution, but I don't know enough about what you're doing to suggest it. To answer your question directly more like:

TotM = DCount("*", rsRiepilogo!NameQuery, "Sex = 'M'")
TotF = DCount("*", rsRiepilogo!NameQuery, "Sex = 'F'")

The more common solution would involve a totals query where you group on the sex field.

[stefanocps]

i canceled this because of a mistake

[stefanocps]

i have error
it say element not found in this collection
look like can t find that filed SEX in the query selected from
rsRiepilogo!NameQuery

i repeat, NameQuery is a variabile, queroies name are in RIEPILOGO

[pbaldy]

Is the field Sex in every query? Based on the reply you deleted, I'd try a totals query that grouped on the two fields, the sex field and the filter field (study, job, etc). Can you attach the db here?

[stefanocps]

sex is in every query, is the main filter, after the date one
sure i attach, yiou might find some difficulties as it is in italian, and even the code i wrote i made some variation at the name for better understanding
if oyu open RICERCA mask associated to the button there is the code
The 2 main tables are ANAGRAFICA and date_treattamenti
http://stefanocapasso.net/ambulatorio good.accdb

[stefanocps]

also in RIEPILOGO now there are only few queries
because i was doing tens of queries with count included...too much job for the final report

[pbaldy]

I was thinking of this type of thing, adapted to use the date query which doesn't work for me (does Maschere mean "Forms", making that a form reference?).

SELECT anagrafica.[TITOLO DI STUDIO], anagrafica.SESSO, Count(anagrafica.Id) AS CountOfId
FROM anagrafica
GROUP BY anagrafica.[TITOLO DI STUDIO], anagrafica.SESSO;

[stefanocps]

dont understand you
Maschere meand Forms yes
the date is essentials
Every 3 months we need to collect all thoise infoHow mani Female with xx study degree
how many male with xx study degress
how many female with yyy study degree, how many male with yyy study degree
and so on
then the same with job
then the same with age range..and more thingsif you open MASCHERE RICERCHE
everything should start form there (leave fornow the submask and the search filed, just consider the 2 date fileds and the button
Majority of the query have already the count in it...but you see how many? not even the half and so many
That s why somehow want to avoid the double query F and M
if you receive input mask they are date from date to
dd/mm/yyyy

[stefanocps]

the code you suggested me at the beginning seemed a good path...

[orange]

cross posted with responses at https://www.utteraccess.com/forum/in...ic=2052219&hl=

[stefanocps]

cross posted with responses at https://www.utteraccess.com/forum/in...ic=2052219&hl=

do i need to do something about?

[orange]

stefanocps,
When you post the same request on more than one forum, it is considered Best Practice / Good Etiquette to
advise the readers that you have done so, and place a link(s) to the other forum(s) within your post.

This is the rationale for cross posting in this manner.