Using Criteria in Query

  • Thread starter Thread starter Baz
  • Start date Start date
B

Baz

Hi there

I am trying to count the number of entries in a table with the value of $
98.00 in a table, when i run the following query i get a count of all
entries for a particular day between the dates

SELECT Count(tblPolicyDetail.TotalPremium) AS CountOfTotalPremium,
tblPolicyDetail.DateOfSale
FROM tblPolicyDetail
GROUP BY tblPolicyDetail.DateOfSale
HAVING (((tblPolicyDetail.DateOfSale) Between [1] And [2]));

But as soon as i put 98.00 in the query criteria and use the query below, I
get no results, the are $ 98.00 entries.

SELECT Count(tblCancerPolicyDetail.TotalPremium) AS CountOfTotalPremium,
tblPolicyDetail.DateOfSale
FROM tblPolicyDetail
GROUP BY tblPolicyDetail.DateOfSale
HAVING (((Count(tblPolicyDetail.TotalPremium))=98) AND
((tblPolicyDetail.DateOfSale) Between [1] And [2]));


Please help

Thanks


Barry
 
Hi,


In the query designer, change the COUNT under PolicyDetail.TotalPremium for
a WHERE. Same thing for the DateOfSale field. In SQL you should then have:

SELECT Count(tblCancerPolicyDetail.TotalPremium) AS CountOfTotalPremium,
tblPolicyDetail.DateOfSale
FROM tblPolicyDetail
GROUP BY tblPolicyDetail.DateOfSale
WHERE tblPolicyDetail.TotalPremium=98
AND
tblPolicyDetail.DateOfSale Between [1] And [2];


It is possible that the total premium is not exactly 98.00 if you used float
(rather than currency) data type. In this case, try


WHERE ABS( tblPolicyDetail.TotalPremium - 98 ) <= 0.005
AND
tblPolicyDetail.DateOfSale Between [1] And [2]



Hoping it may help,
Vanderghast, Access MVP
 
Thanks

Got me on the correct track

I used this


SELECT tblPolicyDetail.TotalPremium, Count(tblPolicyDetail.TotalPremium) AS
CountOfTotalPremium
FROM tblPolicyDetail
WHERE (((tblPolicyDetail.DateOfSale) Between [1] And [2]))
GROUP BY tblPolicyDetail.TotalPremium
HAVING (((tblPolicyDetail.TotalPremium)=98));


I had 2 columns in Query Designer for the premium, one on group by and the
other on count with the criteria.


Thanks again

Barry



Michel Walsh said:
Hi,


In the query designer, change the COUNT under PolicyDetail.TotalPremium for
a WHERE. Same thing for the DateOfSale field. In SQL you should then have:

SELECT Count(tblCancerPolicyDetail.TotalPremium) AS CountOfTotalPremium,
tblPolicyDetail.DateOfSale
FROM tblPolicyDetail
GROUP BY tblPolicyDetail.DateOfSale
WHERE tblPolicyDetail.TotalPremium=98
AND
tblPolicyDetail.DateOfSale Between [1] And [2];


It is possible that the total premium is not exactly 98.00 if you used float
(rather than currency) data type. In this case, try


WHERE ABS( tblPolicyDetail.TotalPremium - 98 ) <= 0.005
AND
tblPolicyDetail.DateOfSale Between [1] And [2]



Hoping it may help,
Vanderghast, Access MVP


Baz said:
Hi there

I am trying to count the number of entries in a table with the value of $
98.00 in a table, when i run the following query i get a count of all
entries for a particular day between the dates

SELECT Count(tblPolicyDetail.TotalPremium) AS CountOfTotalPremium,
tblPolicyDetail.DateOfSale
FROM tblPolicyDetail
GROUP BY tblPolicyDetail.DateOfSale
HAVING (((tblPolicyDetail.DateOfSale) Between [1] And [2]));

But as soon as i put 98.00 in the query criteria and use the query
Click to expand...
below,
I
get no results, the are $ 98.00 entries.

SELECT Count(tblCancerPolicyDetail.TotalPremium) AS CountOfTotalPremium,
tblPolicyDetail.DateOfSale
FROM tblPolicyDetail
GROUP BY tblPolicyDetail.DateOfSale
HAVING (((Count(tblPolicyDetail.TotalPremium))=98) AND
((tblPolicyDetail.DateOfSale) Between [1] And [2]));


Please help

Thanks


Barry
Click to expand...
Click to expand...
 
Back
Top