Calculating the number of records per user

[JohnLee]

Hi Experts,

I'm hoping someone may be able to assist me here. I have two tables, one which has data imported into it and the other that will store statistics based on the information in the imported data. My tables are named as follows:

tblTextFilesImported
tblIndividualStats

In the tbleTextFiles Imported I have three fields that I want to target as follows:

dtmScannedBatchDate
strUserName
strScannedBatchNo

I want to calculate the number of records per strUserName per strScannedBatchNo and the dtmScannedBatchDate.

All of the fields above can have variable data in them, that is to say for instance the strUserName "forda" can appear multiply times as can the strUserName "markss". The same applies to the strScannedBatchNo and dtmScannedBatchDate.

So what I need to do is to count up the number of records in the tblTextFilesImported table for each UserName by Batch No and date, so I should get a record in each case something like this:

strUserName strScannedBatchNo dtmScannedBatchDate lngTotal
forda 00000167 17/11/2008 45
markss 00000167 17/11/2008 51
skidmoa 00000168 17/11/2008 78
etc
etc

I have written the following code:

==============
BEGIN CODE
==============

Dim rst As DAO.Recordset
Dim DB As Database

Set DB = CurrentDb 'Set DB to Equal the CurrentDB [current database]
'Count up the total records for each individaul and assign the value to the
'appropriate fields in the tblIndividualStats table
Set rst = DB.OpenRecordset("tblIndividualStats")
rst.AddNew
rst!dtmDateIndStats = Me![dtmScannedBatchDate]
rst!strUserNameIndStats = Me![strUserName]
rst!strScannedBatchNoIndStats = Me![strScannedBatchNo]
rst!lngTotalProcessed = DCount("[strScannedBatchNo]", "tblTextFilesImported", "[strUserNameIndStats] = Me![strUserName] And [strScannedBatchNoIndStats] = Me![strScannedBatchNo]")
rst.Update
rst.Close 'Close the Recordset

============
END CODE
============

This does'nt work, so I've clearly got it wrong. Any assistance would be much appreciated.

John Lee

 

[DCrake]

rst!lngTotalProcessed = DCount("[strScannedBatchNo]", "tblTextFilesImported", "[strUserNameIndStats] = '" & Me![strUserName] & "' And [strScannedBatchNoIndStats] = '" & Me![strScannedBatchNo] & "'")

You need to seperate the arguments from the criteria, by using the &'s and 's you will parse the where condition correctly

Try using Debug.Print for both variations to view the difference

 

[JohnLee]

Hi,

Thanks for your response, I've put in your suggestion but alas nothing happening.

I've attached a copy of the tables and form the triggers the code, Perhaps you could have a look and see where I'm going wrong.

Thanks once again

John

 

[DCrake]

First of all there is an ActiveX control call Microsoft ProgressBar Control ver 6.0 (SP6) that you can add to your form instead of using the method you have done.

Secondly the code refers to controls that do not appear on the form so the On Error Resume Next does not trap these. This is why you are not getting any records in your table. I suggest you rem out the On Error line, and place a breakpoint on the first Set command and F8 through the code.

If this form is a snippet of a main form then unfortunately I cannot test it properly for you.

David

 

[JohnLee]

Hi,

Thanks for your response. I see what you mean, what I am trying to do is obtained data from the fields in the tblTextFilesImported which have the following names:

dtmScannedBatchDate
strUserName
strScannedBatchNo

When I originally set up my code I wrote this:

rst!dtmDateIndStats = dtmScannedBatchDate
rst!strUserNameIndStats = strUserName
rst!strScannedBatchNoIndStats = strScannedBatchNo

However nothing was happening, so I thought by putting in the "Me!" it would find where I wanted to get the data from, clearly this is not the case.

So perhaps this approach is the wrong one. What I need to do is to count up the number of records for each user "strUserName" that they have processed in each Batch "strScannedBatchNo" and to populate the stats table as follows:

Batch No, UserName, Scanned Date Total Records. where it would look something like this:

00000167 forda 17/11/2008 54
00000178 markss 17/11/2008 39
etc etc

Because as you can see from the supplied example DB the User Name and Batch No along with the date will change on a regular basis, because each set of data is allocated a new batch number once scanned along with its scanned date and then the user is recorded according to when the process it, I need to be able to deal with it in that way.

Do you have any suggestions of how I can achieve this or what code that I could employ to do what's needed.

The data in the DB are exactly what will be received.

Your assistance would be appreciated.

John

 

[Brianwarnock]

I have obviously totally missed the boat here because on reading post1 and looking at the DB I thought simple SQL would do the job, and it appears to, so what have I missed?

Brian

SELECT tbleTextFilesImported.strUserName, tbleTextFilesImported.strScannedBatchNo, tbleTextFilesImported.dtmScannedBatchDate, Count(*) AS Expr1
FROM tbleTextFilesImported
GROUP BY tbleTextFilesImported.strUserName, tbleTextFilesImported.strScannedBatchNo, tbleTextFilesImported.dtmScannedBatchDate;

 

[JohnLee]

Hi Brian,

No you've not missed the boat, I've got two posts on the go which you are reponding to. The SQL bit is to do with my other post, which I will be trying out shortly.

This is a different aspect of the same DB that I'm working on.

Sorry to cause confusion.

John

 

[JohnLee]

Hi Brian,

where should I deploy that SQL code that you supplied, whilst I have some knowledge of VB, I'm not all that familiar with SQL.

Thanks in advance

John

 

[Brianwarnock]

Open a new query in design view and select the table, then select SQL view and paste in the code. You cannot just open a new query in SQL view, an ACCESS quirk.

Brian