COunt after date

[cbuechner]

I thought this would be easy to get, but realizing I still dont know so much.

I have two tables. Table 1 has CVD_Equipment, and a date. Each record would be a new run, and I have another table (table two) that has the CVD_Equipment, and a date.

In making a query, there I can count the occurrences of the CVD equipment, however, I want to count only after the date thats in table 2.

In the criteria in the query, I added this but it does not work.

count(if([Table1].[date]>[Table2].[date],1,0))



This did not work. Please help

[ranman256]

is there only 1 record in table 2?
if so:
in the query pull in both tables, do not join
show the fields from tbl1
and under tbl1.date ,put the criteria: >tbl2.date

[Gicu]

Maybe try this:

CVD_Count:Sum(Iif([Table1].[date]>[Table2].[date],1,0))

In the query you would join the two tables by CVD_Equipment field.

Cheers,
Vlad

[cbuechner]

Thank You!

I left joined CVD_Equipment, and

Days since rebuld: Sum(IIf([Table1].[Date]>[Table2].[Date],1,0))

I didn't consider making it one entry on table2. I will change that to just keep updating that record. I will have the historical data saved by a update to another table.

Thank you for your help.