Query not givin proper results in form (strange)

[Txl]

Hello

I have 6 queries which are counting records, 3 of them are counting all
records in the base and the 3 others are counting records between this
date and that date based on dates collected in a separate form.

All the queries are working correctly individually.

I have created a "result form" to display ALL results, first I open the
"date selection form" and I put in "startdate" and "enddate" then I open
the result form which has a query regrouping the result of the 6
individual queries as source.

The 3 queries without date are working perfectly and I can display their
results in the form but the 3 queries with the date are not, the funny
thing is that when I open the form properties, I go to the query
modificaion and click on the "display" view ALL results are there and
correct, what did i do wrong ?

In case of need I can put the SQL sources of all the queries and the
"grouping" query.

Thanks for the help

 

[John Vinson]

In case of need I can put the SQL sources of all the queries and the
"grouping" query.

Please do. No way to tell what you're doing wrong without knowing just
what you're doing!

John W. Vinson[MVP]
(no longer chatting for now)

 

[Txl]

Please do. No way to tell what you're doing wrong without knowing just
what you're doing!

John W. Vinson[MVP]
(no longer chatting for now)

Ok, here we go





SQL "main result" form query

SELECT rqoffrescrees.tr, rqoffressucces.ts, rqoffresxmises.te,
rqoffrescreesvariable.trv, rqoffressuccesvariable.tsv,
rqoffresxmisesvariable.tev, rqstatpml.pml FROM rqoffrescrees,
rqoffressucces, rqoffresxmises, rqoffrescreesvariable,
rqoffressuccesvariable, rqoffresxmisesvariable, rqstatpml;

In this one the tr, te and ts are displayed perfectly, but trv, tev and
tsv are all to 0, of course if I go to the form properties and then to the
query and to the visualization mode ALL results are displayed correctly,
and this is feeling quite strange to me....

"rqoffrescreesvariable"

SELECT Count(tbloffre.ref) AS trv
FROM tbloffre
HAVING (((tbloffre.datecreation)>=[forms]![frmstatdate]![datedebut] And
(tbloffre.datecreation)<=[forms]![frmstatdate]![datefin]));


"rqoffressuccesvariable"

SELECT Count(tbloffrecandidat.datetransmission) AS tsv
FROM tbloffrecandidat
HAVING (((tbloffrecandidat.datesucces)>=[forms]![frmstatdate]![datedebut]
And
(tbloffrecandidat.datesucces)<=[forms]![frmstatdate]![datefin]));


"rqoffresxmisesvariable"

SELECT Count(tbloffrecandidat.offre) AS tev
FROM tbloffrecandidat
HAVING (((tbloffrecandidat.datetransmission)>=forms!frmstatdate!datedebut
And
(tbloffrecandidat.datetransmission)<=forms!frmstatdate!datefin));



The fields from frmstatdate are OK since I can visualize them on the main
form afterwards by having 2 fields using as source

=Formulaires!frmstatdate!datedebut

and

=Formulaires!frmstatdate!datefin


Thanks for the help

 

[John Vinson]

SQL "main result" form query

SELECT rqoffrescrees.tr, rqoffressucces.ts, rqoffresxmises.te,
rqoffrescreesvariable.trv, rqoffressuccesvariable.tsv,
rqoffresxmisesvariable.tev, rqstatpml.pml FROM rqoffrescrees,
rqoffressucces, rqoffresxmises, rqoffrescreesvariable,
rqoffressuccesvariable, rqoffresxmisesvariable, rqstatpml;

This is a "Cartesian join" query. As such, it will display the PRODUCT
of all the records in each component table or query. That is, if each
of the seven rq queries returns five records, this query will return
EVERY SINGLE ONE of the 5^7 = 78125 records.

There are *occasional* instances where such a query is useful but
ordinarily it's a major problem! Even if each query returns only one
record it's a very inefficient way to combine the records.

In this one the tr, te and ts are displayed perfectly, but trv, tev and
tsv are all to 0, of course if I go to the form properties and then to the
query and to the visualization mode ALL results are displayed correctly,
and this is feeling quite strange to me....

"rqoffrescreesvariable"

SELECT Count(tbloffre.ref) AS trv
FROM tbloffre
HAVING (((tbloffre.datecreation)>=[forms]![frmstatdate]![datedebut] And
(tbloffre.datecreation)<=[forms]![frmstatdate]![datefin]));

The HAVING clause is applied *after* the total operation is complete.
Consider changing HAVING to WHERE for more efficiency and
predictability! Also just be aware (you may already be) that the Count
operation does not count values; it counts *records* - in this case a
count of those records in tbloffre for which the ref field is not
NULL. A count of 10 might mean that there are ten records all with the
same ref, or they might all be different.

"rqoffressuccesvariable"

SELECT Count(tbloffrecandidat.datetransmission) AS tsv
FROM tbloffrecandidat
HAVING (((tbloffrecandidat.datesucces)>=[forms]![frmstatdate]![datedebut]
And
(tbloffrecandidat.datesucces)<=[forms]![frmstatdate]![datefin]));


"rqoffresxmisesvariable"

SELECT Count(tbloffrecandidat.offre) AS tev
FROM tbloffrecandidat
HAVING (((tbloffrecandidat.datetransmission)>=forms!frmstatdate!datedebut
And
(tbloffrecandidat.datetransmission)<=forms!frmstatdate!datefin));

Without a clearer understanding of the structure of your tables, and
for that matter of what you're trying to accomplish, I'm not sure; but
you might want to consider - rather than creating all these totals
queries and then combining them into one query - using DCount() as the
control source of a form or report textbox for each sum. You could
have seven textboxes with control sources such as

=DCount("*", "[tbloffrecandidat]", "[datesucces] > #" &
[forms]![frmstatdate]![datedebut] & "# And
(tbloffrecandidat.datesucces)<= #" & [forms]![frmstatdate]![datefin] &
"#")


John W. Vinson[MVP]
(no longer chatting for now)

 

[Txl]

This is a "Cartesian join" query. As such, it will display the PRODUCT
of all the records in each component table or query. That is, if each
of the seven rq queries returns five records, this query will return
EVERY SINGLE ONE of the 5^7 = 78125 records.

There are *occasional* instances where such a query is useful but
ordinarily it's a major problem! Even if each query returns only one
record it's a very inefficient way to combine the records.

Thanbks for the input, i thought also it was a bit like having a dinosaur
trying to put a thread in a needle but I didn't think of anything better.

In this one the tr, te and ts are displayed perfectly, but trv, tev and
tsv are all to 0, of course if I go to the form properties and then to
the
query and to the visualization mode ALL results are displayed correctly,
and this is feeling quite strange to me....

"rqoffrescreesvariable"

SELECT Count(tbloffre.ref) AS trv
FROM tbloffre
HAVING (((tbloffre.datecreation)>=[forms]![frmstatdate]![datedebut] And
(tbloffre.datecreation)<=[forms]![frmstatdate]![datefin]));

The HAVING clause is applied *after* the total operation is complete.
Consider changing HAVING to WHERE for more efficiency and
predictability! Also just be aware (you may already be) that the Count
operation does not count values; it counts *records* - in this case a
count of those records in tbloffre for which the ref field is not
NULL. A count of 10 might mean that there are ten records all with the
same ref, or they might all be different.

Refs are autoincremental unique numbers so no worries from this point of
vue

"rqoffressuccesvariable"

SELECT Count(tbloffrecandidat.datetransmission) AS tsv
FROM tbloffrecandidat
HAVING
(((tbloffrecandidat.datesucces)>=[forms]![frmstatdate]![datedebut]
And
(tbloffrecandidat.datesucces)<=[forms]![frmstatdate]![datefin]));


"rqoffresxmisesvariable"

SELECT Count(tbloffrecandidat.offre) AS tev
FROM tbloffrecandidat
HAVING
(((tbloffrecandidat.datetransmission)>=forms!frmstatdate!datedebut
And
(tbloffrecandidat.datetransmission)<=forms!frmstatdate!datefin));

Without a clearer understanding of the structure of your tables, and
for that matter of what you're trying to accomplish, I'm not sure; but
you might want to consider - rather than creating all these totals
queries and then combining them into one query - using DCount() as the
control source of a form or report textbox for each sum. You could
have seven textboxes with control sources such as

I'll try the code you're putting below, for the general explanation what
I'm trying to do is quite simple, I have a table which is storing job
offers for an administration and they need to count how many offers they
have created (recues) how many times they have proposed an offer to anyone
(envoyees) and how many offers they have successfully provided (succes)
these are the tr ts and tv but I'd also like to have these values between
a start and finish date, let's says 1st januaury until 15th may or
something like that.

I'll try the code and post the results here tonight, thanks a lot for the
help, anyway the Dcount is "out of my league" so far, i really have to
improve the coding stuff...

=DCount("*", "[tbloffrecandidat]", "[datesucces] > #" &
[forms]![frmstatdate]![datedebut] & "# And
(tbloffrecandidat.datesucces)<= #" & [forms]![frmstatdate]![datefin] &
"#")


John W. Vinson[MVP]
(no longer chatting for now)

 

[John Vinson]

I'm trying to do is quite simple, I have a table which is storing job
offers for an administration and they need to count how many offers they
have created (recues) how many times they have proposed an offer to anyone
(envoyees) and how many offers they have successfully provided (succes)
these are the tr ts and tv but I'd also like to have these values between
a start and finish date, let's says 1st januaury until 15th may or
something like that.

Perhaps try a Sum instead of a Count: put in a calculated field equal
to 1 for each value you want counted, and 0 otherwise. For example,
join the personnel table to the tables of offers; put in a calculated
field like

IsSucces: IIF([succes] = True, 1, 0)

and similarly for the other things you want to count. Make it a Totals
query and *sum* these values.

John W. Vinson[MVP]
(no longer chatting for now)