Updating field of one table with count of matching fields in another

[claytonkerr]

Ok, I have been trying to figure this one out all afternoon. Here is the database Structure.

Table_Master
     Key        PrimaryKey
     Date       Date/Time
     HseKey     Short Text(255)
     Comp       Yes/No
     

Table_Export
    Key         PrimaryKey
    HseKey      Short Text(255)
    DataCo      LongText

Example
Table_Master
Key    Date            HseKey         Comp        
3317   1/17/2013    3126488508       True 
3318   7/15/2016    3126488508       True
3319   2/19/2017    3126488508       False

Table_Export
Key   HseKey           DataCo
77     3126488508     7/15/2016 2 Times

What I need to do is count the total number of times that Table_Export.HseKey shows up in Table_Master.HseKey if Table_Master.Comp=True, then insert that number along with the most recent date from Table_Master.Date into Table_Export.DataCo



From what I have read, using count may be faster then Dcount based on the number of records involved, (400k+ in Table_master, 50K in Table_Export), but I'm open to any and all ideas.

[pbaldy]

Do you need the export table? A totals query gets you the result, which you could export:

SELECT HseKey, Max(Table_Master.Date) AS LastDate, Count(*) AS HowMany
FROM Table_Master
WHERE Comp = True
GROUP BY HseKey

Date is a poor choice of field name, as it can conflict with the built-in Date() function.

[claytonkerr]

Yea, the Table_Export table is needed. That table is populated by a excel import, with a lot more data then listed here.

After Table_Export.DataCo is updated, the data will be used in another process.

Only about half the records in Table_export will match Table_Master.HseKey. The other records would be left alone.