Checking the date when records are entered

[dabowles]

Hi,
I wasn't really sure where to put this, if I should put this in Queries or the Reports section. My problem however is I have added a field to each of my tables called "RecordEntered". This field is a date field that has a default value of Date() on each form. I am trying to get an estimate of how many new records are entered each day. Does anybody have a simpler way of doing this? Whenever I try to create a query to pull the total number from each field I have to split the queries up into one per table. It will not let me put all of the lookups in one query. If that wasn't enough, it will not let me use the "Count" function to get a count of each date. So then I figured well if I can't get them all grouped into a query, I'll just create individual queries and make one master report to tie them all in together on one sheet, well it won't do that either because it says it cannot connect the queries. That is why I asked if anyone has any better way to do it. Real quick let me give you some background as to why I need this. My database is increasing in size, exponentially each and every day. If we add a couple hundred records, which are only 5 or 6 fields in length, the DB will increase approximately 10 to 20 MB. I have to compact it, it will then go back down to about 7.5MB. But, as soon as the next day comes along, and a couple hundred more records are entered, BOOM it goes right back up to about 35 to 40MB. Microsoft told me to update Jet on all of the workstations, did that but to no avail, it fixed nothing. Does anyone have any similar experiences with Access 2000?

Thanks,
Dave

 

[jebry]

Hi Dave!

I don't know if you are using any VBA, but if you are, check to be sure that you are closing all object variables that are set in the code and set the variables = nothing. Access is bad about getting rid of temporary items so, if you are making any temporary tables that might be where your problem is also.

hth
Jeff Bridgham

 

[dabowles]

Nope,
No temporary tables, I use a couple of macros to automate a few processes, other than that the only VB I use is to hide fields whenever certain conditions are met on forms.

Thanks,
Dave

 

[tempclerk]

For your estimate, can't you do

SELECT [RecordEntered]
From Table1

UNION SELECT [RecordEntered]
From Table2

UNION SELECT [RecordEntered]
From Table3

...

and then do a Group by and Count query on that?

 

[jebry]

Hi!

Another thought: Can you do a query on each table like this:

Select RecordEntered, Count(PrimaryKey) as NumberOfRecords From Table Group By RecordEntered

And then do a union query on these queries?

hth
Jeff Bridgham