Countif type query

[cjcobra]

I've tried count(iif(expr)) and multiple variations, but can't seem get this to work.

Table one has 500 lines with a date in each line. Lets say line one = 5/19/15

Table two has a list of dates (holidays, vacation, etc.). This table has 10 dates, say:
1/1/15
2/8/15
5/19/15
...

I'm trying to update my table one line by line showing a count of the dates in Table two that are greater than the Table one. Another expression would be dates less than, and yet another if that date equals a date in table two.

Final Table One (separate columns)
My Date, Greater than My Date, Less than My Date, Equal to My Date
5/19/15, 7, 2, 1

Any help would be great!

 

[plog]

I'm trying to update my table

You don't store calculated values in a table, you calculate them. So this data shouldn't go into a table, but a query. You haven't given specific table and field names, so let me tell you how to accomplish this in general.

1. Bring both your tables into a query*.

2. Bring down the date field from the first table

3. Create a calculated field using this expression:
GreaterThan: Iif([Table1Date<Table2Date], 1, 0)

4. Make it an aggregate query (using the Sigma/Summation signal).

5. Change the 'Group By' under GreaterThan to 'Sum'.

6. Run the query.

That will give you all the dates in table 2 greater than the dates in table 1 for every date in table 1. Next, using the code in Step 3, add fields to calculate LessThan and EqualTo fields.

*Note--those two tables shouldn't be joined in the query.

 

[cjcobra]

Hello. After making the query, I was going to Update my main table. Still working out the kinks. This got me 95% of the way which is great. The Sum numbers are coming in very high (594, 300, etc). I think it's multiplying the lines in the tables?

 

[plog]

Again, you shouldn't store these values. Calculated values should be calculated not stored.

As for your problem, if you have duplicate dates in table 1 you will get duplicate values in the query. To get around this, you should create a query based on table 1 to show just the unique dates in table 1. Then in the query you have, replace table 1 with that query.

 

[cjcobra]

That's what I needed! Thank you!