Query treating subquery field as both text and numeric?

[Margarita]

Hello,
working in access 2003 and having a strange problem today. I need a query that selects a list of names from a table called EmployeeTask_FTE where thesum of the field FTE (numeric- double) is equal to 1 for at least one month in the specified 3-month range. I do this via a subquery that first sums the FTE values for the month:

SELECT LastName, FirstName, sum(FTE) AS TotalFTE, FTEMonthStart
FROM EmployeeTask_FTE
WHERE FTEMonthStart Between [startdate] And dateadd("m", 2,[startdate]) And WorkTimeType Like 'REG'
GROUP BY LastName, FirstName, FTEMonthStart;

and then use this in a query that selects only the ones that equal 1:

Select distinct LastName, FirstName from
(SELECT LastName, FirstName, sum(FTE) AS TotalFTE, FTEMonthStart
FROM EmployeeTask_FTE
WHERE FTEMonthStart Between [startdate] And dateadd("m", 2,[startdate]) And WorkTimeType Like 'REG'
GROUP BY LastName, FirstName, FTEMonthStart) 
where TotalFTE=1;

I then use the list of names as a source for another query to be used in a report.

Here is the problem: even though the first subquery works just fine and gives me all the right people with the right FTE values, the 'select distinct names' query does not display everyone who has sum(FTE)=1. A couple of people are missing. I checked these people in the EmployeeTask_FTE table- they each have a total of FTE=1. I even manually re-typed their FTE entries in the table. Everything is fine with them.
Another complication is that when I type "where TotalFTE like '1' " instead of "=1" the query displays the people that were missing- BUT a few other different records are now not being displayed. The rest 100 or so records with TotalFTE=1 are unaffected either way and continue to display.

Can someone tell me how a query can treat a field as both text and numeric and produce some correct and some incorrect results in either case? Please let me know if I'm missing something here- I've never seen a query do this before.
Thank you in advance!

 

[RainLover]

Try using this but it is untested.

Where Sum([FTE])=1;

 

[Margarita]

Try using this but it is untested.

Where Sum([FTE])=1;

Thank you for the suggestion, RainLover, but unfortunately that won't work because you can't have aggregate expressions in a where clause.
Thanks!

 

[RainLover]

Having (((Sum(EmployeeTask_FTE.FTE))>0));

I did test this one, but without the second condition.

 

[Margarita]

Hi RainLover, thanks- I tried this out. It runs just as my original 'where TotalFTE=1' condition did: the majority of people selected are correct (all have sum of FTE=1, but the few who were missing with the where clause applied are missing again.

I did a bit more investigating on the people that are missing from the results. I tried writing a select statement on one of the people, call him Smith, who has three FTE values in the table: .3, .1, and .6. Here is what I tried selecting:

select lastname, sum(FTE) from employeetask_FTE where lastname like 'smith' and worktimetype like 'reg' and ftemonthstart like #7/1/2011#
group by lastname;

The sum selected is correct- it equals 1. I also tried this:

select lastname, sum(FTE) from employeetask_FTE where lastname like 'smith' and worktimetype like 'reg' and ftemonthstart like #7/1/2011# and fte = 0.3
group by lastname;

I tired this for all three values .1, .3, and .6, and all of them worked. The query returned the right results. I also tried treating the values like text, like this:

select lastname, sum(FTE) from employeetask_FTE where lastname like 'smith' and worktimetype like 'reg' and ftemonthstart like #7/1/2011# and FTE like '0.3'
group by lastname;

The funny thing is that it also worked! The values are returned.
I also tried selecting the sum of the FTE values- and the summing works too. BUT when I build the outer layer of the query- the selecting only the names where total FTE is equal to 1, it fails and no records are returned.
This is really confusing me.

Thanks for any suggestions you can provide.

 

[spikepl]

Testing for exact value of a sum of decimal numbers is not reliable, because the numbers are not represented exactly. You could multiply all the values by, say, 1000, make them into integers , and test on a sum of that. Or test on a range ABS((1- YourSum))<0.001

 

[RainLover]

I am at a lost as I really do not have the original to work with. Perhaps Sum(NZ([FTE],0) might help.

What may be of better use is to build the Query in Design Mode.

Just build the Query one field at a time and Test each change.
Save each step as you go.

 

[Margarita]

Testing for exact value of a sum of decimal numbers is not reliable, because the numbers are not represented exactly. You could multiply all the values by, say, 1000, make them into integers , and test on a sum of that. Or test on a range ABS((1- YourSum))<0.001

Thank you, spikepl! That worked- all the records that I expect to see are returned. But here is what I don't understand- if the values have been re-typed as exactly .1, .3, and .6, why wouldn't the query pick them up before? And especially since there are a handful of other employees in the FTE table with the same exact values as this Smith guy that I used as an example- and they were being displayed. This is very, very weird.

Anyway, thank you for suggesting this workaround. I will stick with it since it works, even though I still can't understand where all the trouble was coming from in the first place.
Thanks again!

 

[spikepl]

WHat you see is not what you get. The display is formatted with a number of decimals. But that doeas not necessarily mean that that is the actual number. Also, the sum of 0.1 + 0.1 + 0.1 + 0.1 can, e.g., be 0.3999999999999999989 which may display as 0.4, but in an equation will differ from 0.4

 

[RainLover]

Well done Spike.

Margarita Try setting "Decimal" as the Field Size for you numbers. (In the Table Design)