Set up Countiif to count how many cells match with the corresponding data

[sergi117]

I'm trying to set up a column based on a count(iif select query which counts how many cells match with the single item. So essentially: count(iif(table1.column2<=table2.column3,1,0)
If anyone could help please, thanks! I've tried writing up a query, but then I get aggregate issues.

So if for example one
Table 1 __________________________Table2
Employee Pay _____________________column 1 __pay beg ______end
1 _______15 ________________________x ________12 ________15
2 _______20 ________________________x ________16 ________18


3 _______25 ________________________x ________19 ________21

The result should be
Employee Pay __count
1 _______15____2
2 _______20 ____1
3 _______25_____0

[June7]

How are the tables related?

[sergi117]

I just put the data up as an example, but essentially what I'm trying to do is related to payroll data. One table will contain hire dates, and the other will contain a set date from a beginning period to an end period. I need to get the total number of these periods where the hire date fits in. Like I said the table there was just to give an a more straight forward example.

[sergi117]

For excel it is rather simple just referencing data from another sheet, but I'm unsure about doing this for access.

[June7]

So tables do not have direct relationship. Options:

1. Cartesian query - this is a query with no JOIN clause so every record in each table associates with every record of other table, then do an aggregate query with Count and IIf

2. DCount() domain aggregate function

[CJ_London]

don't understand your example

count(iif(table1.column2<=table2.column3,1,0)

so result should be

The result should be
Employee Pay __count
1 _______15____3
2 _______20 ____1
3 _______25_____0

[sergi117]

that is right, I'm just stupid lol

[CJ_London]

ok - so as June says, you need a cartesian aggregate query - something like

SELECT Employee, Pay, Sum(iif(pay<=end,1,0)) as paycount
FROM Table1, Table2 
GROUP BY Employee, Pay

note that words like end and count are reserved words - they are used in coding, so don't use these words