store detail table record count in header table

[hfreedman1957]

Hi,
I have a main table called project, one record per project. I have a detailed table which hold many records per project. I want to count the records in the detail table for each project and store the count in the project table. There will be a couple extra criteria for selecting records from the detail file aside from project ID. There are at most 30 projects and hundreds of detail records per project. This is not a large database.
Should I do this entirely in VBA, looping through the details for each project record, or can/should this be accomplished with a combination of VBA and a query? Please provide an approach. The more detailed the more beneficial. Thanks so much!
Howard

[aytee111]

Do not store calculated values on tables. Create a standard query which is used each time you need the value.

Create a select query on the second table, select project id twice. Click on the Totals icon at the top, a new line will be added below. Select Group By for the first field and Count for the second. Add the additional criteria as needed.

[hfreedman1957]

Actually, my plan already was to run the query/program each time I need the values, as you suggested. The reason I wanted to update the project file with the count values is that I run a customized report (VBA) using the project file only, and it would be much easier for me of those count values were on the project records as well.
With that, I'll try your solution above and see if I can incorporate that query into my customized report. Thanks!

[aytee111]

Not sure what you mean by "customized report (VBA)", but instead of using only the project table join in the count query into the query statement.

[hfreedman1957]

For this particular report, I currently don't use the detailed table. The report starts as VBA code which is triggered from an event that runs an Access report which has a project table query as record source, and generates a separate report for each project record, converts it into PDF format, attaches it to an email to be sent to the Project Manager of that project (record). This loops through and repeats for each project record. I don't touch the detail table. Inside the Access report are many calculations and I now have a requirement to incorporate those counts into these calculations. It would just be much easier if the counts were present on each project record by running an update query and/or VBA code to update the project records with the counts prior to the running of this report.

[aytee111]

which has a project table query as record source

Add the counts query to the record source.

Either that or before you run the report, run a query to make a table with the counts and another query to update from that temp table. Or else loop thru the records in VBA and update them.

[hfreedman1957]

ok, I'll give that a shot. Thanks so much!