Sum IIF date comparison issue

[sergi117]

Hey everyone, so I was writing an expression meant to check if a hire date is <= a pay period end date. So I wrote up sum(iif([hire_date]<=[pay_end],1,0)) and the numbers it gives me are rather ridiculous. So I have name selected, hire date, and the other table with pay end dates in the from, but not necessarily part of the query. The results are a bit over a thousand despite there being only 200 periods, so you'd assume the max would be 200. By including the [pay_end] in the select I notice rather than 1 for the hire date I am getting 12 which explains why the sum is so high.


A solution I found was to select the name, hire date, pay end, and only testing the iif first. Then on a separate query sum the previous expression. This does work, but i'm wondering what the issue is that I can't seem to write up a sum(iif but instead must work around with 2 separate queries. If anyone has any ideas let me know. The sql is below:

SELECT Query2.Name, Query2.[Hire Date], Sum(IIf([Query2].[Hire Date]<=[sheet1].[END],1,0)) AS expr2
FROM Query2, sheet1
GROUP BY Query2.Name, Query2.[Hire Date];

See below was my solution:

SELECT Query2.Name, Query2.[Hire Date], sheet1.[PP END], IIf([Query2].[Hire Date]<=[sheet1].[END],1,0) AS expr2
FROM Query2, sheet1
GROUP BY Query2.Name, Query2.[Hire Date], sheet1.[END];

SELECT Query5.Name, Query5.[Empl ID], Sum([expr2]) AS Expr1
FROM Query5
GROUP BY Query5.Name, Query5.[Empl ID];

Again the 2 separate query work, but in attempting to do the full query it just doesn't seem to work. If anybody has some suggestions, it'd be much appreciated.

[Micron]

Your 1st 2 examples indicate you have 2 tables (a select query is just a form of table) but have not joined them. This gives a Cartesian product. 20 records in 1 and 15 records in the other will result in 15 x 20 or 300 records.

[sergi117]

There isn't really a location to join the two tables. One is meant to hold name and hire while the other simply contains the end periods. I was hoping for it to simply check without a join as there again isn't a real location to join. The iif alone works and summing the result gets my answer, so if there isn't a workaround for not having a join it may be all I can do.

[Micron]

then I think you have arrived at the only solution possible.