Query...

[116]

I am looking for a count of items meeting certain criteria. I am using this
< Between DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()

to retrieve the data, but have been unable to get a count. I had to put

the above in a sub query, and create one just for the count. Is there a way
to do this in one query?

Thanks
David

 

[techrat]

I am looking for a count of items meeting certain criteria.  I am usingthis
< Between DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()> to retrieve the data, but have been unable to get a count.  I hadto put

the above in a sub query, and create one just for the count.  Is there a way
to do this in one query?

Thanks
David

Can you give me a little more information on what you currently have.
If you put the criteria statement shown above in a query against
t_Heat_Treat_Oven_Log does the query return all of the records that
you are looking to count? If so, you simply need to change the query
to a summary query and specify count in the total row for the correct
field on the QBE grid.

If you want the count to show up as one of the values in a different
query, you have a different problem altogether.

If you can expand on how you will be using the count, I will be glad
to attempt to address the issue more directly.

Hope this helps.

 

[orange]

I am looking for a count of items meeting certain criteria. I am using this
< Between DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()
> to retrieve the data, but have been unable to get a count. I had to put
the above in a sub query, and create one just for the count. Is there a way
to do this in one query?

Thanks
David

Please give us a little more info. What tables are involved? What items? What exactly are the criteria?
What have you tried so far?
Could you post you query code?

 

[Daryl S]

David -

If you posted your SQL, that would help. A couple things you can try in
your original query. First, in the totals row for the field with the
criteria, make that a WHERE. Then instead of the "*" to do the count on,
just select the key field from the table. That count should work in a single
query. If not, post your SQL (from SQL View), and tell us what the query
returns so we can help more.

--
Daryl S

I am looking for a count of items meeting certain criteria. I am using this
< Between DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()

to retrieve the data, but have been unable to get a count. I had to put

the above in a sub query, and create one just for the count. Is there a way
to do this in one query?

Thanks
David

 

[116]

Thanks for the assist. Here is the SQL:

SELECT t_Heat_Treat_Oven_Log.Date
FROM t_Heat_Treat_Oven_Log
GROUP BY t_Heat_Treat_Oven_Log.Date
HAVING (((t_Heat_Treat_Oven_Log.Date) Between
DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()));

I am currently doing this in a sub-query. I have another that I am using to
get the count. The both querys are 'Select Queries'. My sub merely has my
Date field with the above in the criteria.

I hope this is what you were asking for.

Thanks
David

David -

If you posted your SQL, that would help. A couple things you can try in
your original query. First, in the totals row for the field with the
criteria, make that a WHERE. Then instead of the "*" to do the count on,
just select the key field from the table. That count should work in a single
query. If not, post your SQL (from SQL View), and tell us what the query
returns so we can help more.

--
Daryl S

I am looking for a count of items meeting certain criteria. I am using this
< Between DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()

to retrieve the data, but have been unable to get a count. I had to put

the above in a sub query, and create one just for the count. Is there a way
to do this in one query?

Thanks
David

 

[orange]

Thanks for the assist. Here is the SQL:

SELECT t_Heat_Treat_Oven_Log.Date
FROM t_Heat_Treat_Oven_Log
GROUP BY t_Heat_Treat_Oven_Log.Date
HAVING (((t_Heat_Treat_Oven_Log.Date) Between
DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()));

I am currently doing this in a sub-query. I have another that I am using to
get the count. The both querys are 'Select Queries'. My sub merely has my
Date field with the above in the criteria.

I hope this is what you were asking for.

Thanks
David

"Daryl S" wrote:

> David -
>
> If you posted your SQL, that would help. A couple things you can try in
> your original query. First, in the totals row for the field with the
> criteria, make that a WHERE. Then instead of the "*" to do the count on,
> just select the key field from the table. That count should work in a single
> query. If not, post your SQL (from SQL View), and tell us what the query
> returns so we can help more.
>
> --
> Daryl S
>
>
> "116" wrote:
>
> > I am looking for a count of items meeting certain criteria. I am using this
> > < Between DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()
> > > to retrieve the data, but have been unable to get a count. I had to put
> > the above in a sub query, and create one just for the count. Is there a way
> > to do this in one query?
> >
> > Thanks
> > David
> >

I still don't see what your criteria are. In a simple sentence what are the criteria.

Seems that you have a field called PC, and you only want to include records where PC="THERM", but I don't understand what Dates are involved. Some Date and Today - but what Date? Please advise.

 

[Daryl S]

David -

Here is your query adjusted to just return the count of dates within your
criteria:

SELECT count(t_Heat_Treat_Oven_Log.Date)
FROM t_Heat_Treat_Oven_Log
HAVING (((t_Heat_Treat_Oven_Log.Date) Between
DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()));

Is that what you need?

--
Daryl S

Thanks for the assist. Here is the SQL:

SELECT t_Heat_Treat_Oven_Log.Date
FROM t_Heat_Treat_Oven_Log
GROUP BY t_Heat_Treat_Oven_Log.Date
HAVING (((t_Heat_Treat_Oven_Log.Date) Between
DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()));

I am currently doing this in a sub-query. I have another that I am using to
get the count. The both querys are 'Select Queries'. My sub merely has my
Date field with the above in the criteria.

I hope this is what you were asking for.

Thanks
David

David -

If you posted your SQL, that would help. A couple things you can try in
your original query. First, in the totals row for the field with the
criteria, make that a WHERE. Then instead of the "*" to do the count on,
just select the key field from the table. That count should work in a single
query. If not, post your SQL (from SQL View), and tell us what the query
returns so we can help more.

--
Daryl S

I am looking for a count of items meeting certain criteria. I am using this
< Between DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()
to retrieve the data, but have been unable to get a count. I had to put
the above in a sub query, and create one just for the count. Is there a way
to do this in one query?

Thanks
David

 

[orange]

Daryl S,
"Here is your query adjusted to just return the count of dates within your
criteria:

SELECT count(t_Heat_Treat_Oven_Log.Date)
FROM t_Heat_Treat_Oven_Log
HAVING (((t_Heat_Treat_Oven_Log.Date) Between
DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()));

Is that what you need?"
-----
Daryl, Could you explain what this query is trying to do?

As I see it

Count (Date) from t_Heat_Treat_Oven_Log
HAVING
Date is Between Date and Today and
PC = "THERM"

But Date will always be between Date and Today because Between is inclusive.

Note: I'm using Date to mean t_Heat_Treat_Oven_Log.Date
and Today to mean Date() function.

I'm confused with the query criteria.
It seems the answer could be

SELECT count(t_Heat_Treat_Oven_Log.Date)
FROM t_Heat_Treat_Oven_Log.Date
WHERE PC="THERM"

Thanks

 

[116]

Thank you much. I see my mistake. Was trying to do with just one field.

David

David -

Here is your query adjusted to just return the count of dates within your
criteria:

SELECT count(t_Heat_Treat_Oven_Log.Date)
FROM t_Heat_Treat_Oven_Log
HAVING (((t_Heat_Treat_Oven_Log.Date) Between
DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()));

Is that what you need?

--
Daryl S

Thanks for the assist. Here is the SQL:

SELECT t_Heat_Treat_Oven_Log.Date
FROM t_Heat_Treat_Oven_Log
GROUP BY t_Heat_Treat_Oven_Log.Date
HAVING (((t_Heat_Treat_Oven_Log.Date) Between
DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()));

I am currently doing this in a sub-query. I have another that I am using to
get the count. The both querys are 'Select Queries'. My sub merely has my
Date field with the above in the criteria.

I hope this is what you were asking for.

Thanks
David

David -

If you posted your SQL, that would help. A couple things you can try in
your original query. First, in the totals row for the field with the
criteria, make that a WHERE. Then instead of the "*" to do the count on,
just select the key field from the table. That count should work in a single
query. If not, post your SQL (from SQL View), and tell us what the query
returns so we can help more.

--
Daryl S


:

I am looking for a count of items meeting certain criteria. I am using this
< Between DLookUp("[Date]","t_Heat_Treat_Oven_Log","[PC]='THERM'") And Date()
to retrieve the data, but have been unable to get a count. I had to put
the above in a sub query, and create one just for the count. Is there a way
to do this in one query?

Thanks
David