CALCULATE TOTAL RECORD NUMBER IN 4 TABLES BY DATE RANGE

[HerickP]

Hi,<br><br>I have 4 tables, similar in aspect, and all 4 of them have a field named &quot;DATEENTERED', bounded to a control in a form, so everytime a record gets entered, Access flags the field of the specific record with the date it was entered. Now, what I need is to have a query that can calculate the total of each table, based on the &quot;dateentered&quot; field, where I can specify a date range, then access will tell me the total of records entered between the two dates I will specify.<br><br>Hope I could explain right. Thanks a lot!

 

[gol4]

record count based on a union query sounds like the easiest solution to me.

 

[Cornerstone]

Ok try this cool variation<br><br>Make a form. Add 3 text boxes text1. text2 and text3. (Two for the date range and 1 for the resulting total.) Add a command button. Name the form Form1. Substitute the names of your tables for Table1, Table2...Substitute the name of your primary key in your tables for ID. Change CountofID to Countof(yourprimarykey) Change SomeDateField to your date field in your table.<br><br>Put this code on the buttons Click Event.<br><br>Dim sql1, sql2, sql3, sql4 As String<br><br>Dim dbs As DAO.Database<br>Dim rst, rst2, rst3, rst4 As DAO.Recordset<br><br>Dim counter As Integer<br><br>Set dbs = CurrentDb<br><br>'the following 3 lines go on one line<br>sql1 = &quot;Select Count(Table1.ID) AS CountOfID FROM Table1 WHERE (((Table1.SomedateField) Between #&quot; & Forms!form1!Text1 & &quot;# And #&quot; & Forms!form1!Text2 & &quot;#));&quot;<br>Set rst = dbs.OpenRecordset(sql1)<br><br>'the following 3 lines go on one line<br>sql2 = &quot;Select Count(Table2.ID) AS CountOfID FROM Table2 WHERE (((Table2.Somedatefield) Between #&quot; & Forms!form1!Text1 & &quot;# And #&quot; & Forms!form1!Text2 & &quot;#));&quot;<br>Set rst2 = dbs.OpenRecordset(sql2)<br><br>'the following 3 lines go on one line<br>sql3 = &quot;Select Count(Table3.ID) AS CountOfID FROM Table3 WHERE (((Table3.SomedateField) Between #&quot; & Forms!form1!Text1 & &quot;# And #&quot; & Forms!form1!Text2 & &quot;#));&quot;<br>Set rst3 = dbs.OpenRecordset(sql3)<br><br>'the following 3 lines go on one line<br>sql4 = &quot;Select Count(Table4.ID) AS CountOfID FROM Table4 WHERE (((Table4.someDatefield) Between #&quot; & Forms!form1!Text1 & &quot;# And #&quot; & Forms!form1!Text2 & &quot;#));&quot;<br>Set rst4 = dbs.OpenRecordset(sql4)<br><br>'the following 2 lines go on one line<br>Me!Text3 = rst!countofID + rst2!countofID + rst3!countofID + rst4!countofID<br>