Count Query based on Criteria

[txgeekgirl]

I have a query which contains the following:

Input criteria for Year and Qtr
Staff ID
Week
Code

There are 96 code entries per day so staff ID "2818" for week "1" has 672 listings for that week.

I need a query that will add how many of a certain code they had in that week without writing 33 different code entries. Is that possible?

I am needing a query that looks like this:

Staff ID
Week
Code
Units (per that code)

So that I can make another query by week and count.

Thank you in advance for any help.

 

[John Big Booty]

Create a query, whilst you are in design view you will see in the top menu bar the Sigma sign (Greek letter {looks like a capital M laying on it's side} used in maths to mean Sum/Total) click on this and you will in the query now have a row called Total Select Group By for Staff Id and Week. Select Count for Code. This should return the result you are looking for.

 

[boblarson]

Create a query, whilst you are in design view you will see in the top menu bar the Sigma sign (Greek letter {looks like a capital M laying on it's side}

A visual:

 

[txgeekgirl]

Thanks Big John Booty - I wish it were that easy.

I have attached the results - it would mean that the employee had the same exact codes each day of that week. It doesn't tell me how many of each code they had.

Oh well - looking for a short cut but it looks like I will be writing all day!

 

[boblarson]

Thanks Big John Booty - I wish it were that easy.

I have attached the results - it would mean that the employee had the same exact codes each day of that week. It doesn't tell me how many of each code they had.

Oh well - looking for a short cut but it looks like I will be writing all day!

No, you forgot to include a column for Code as well as the Code Count. If you include the column for Code (as a GROUPED BY) then it should work fine.

 

[txgeekgirl]

Thanks Bob - still innaccurate data... I attached my (personal) data - because I know it's wrong. But I think if I write an 5 Expressions based on week where [Week]=1 (etc) into the count it will fix my problem. It is adding the entire first month of the both Qtrs even though there is a choice of the year and Qtr.

We took a basically simple DB and made it compliant to State standards (of course taking away its simplicity) for reporting and I have to add the electronic batch pull which I know will take me 3 queries to get to and this data is the basis of the three subsequent queries.

 

[boblarson]

Thanks Bob - still innaccurate data... I attached my (personal) data - because I know it's wrong. But I think if I write an 5 Expressions based on week where [Week]=1 (etc) into the count it will fix my problem. It is adding the entire first month of the both Qtrs even though there is a choice of the year and Qtr.

We took a basically simple DB and made it compliant to State standards (of course taking away its simplicity) for reporting and I have to add the electronic batch pull which I know will take me 3 queries to get to and this data is the basis of the three subsequent queries.

I believe it is because you have grouped by year and quarter and in this case you want to change those last two columns there to WHERE instead of GROUP BY.

 

[txgeekgirl]

Hey Bob - That didn't change the results. Thanks for trying.

 

[boblarson]

Hey Bob - That didn't change the results. Thanks for trying.

Can you post the db here so I can play with it a bit?

 

[txgeekgirl]

I will in a bit - I need to strip it out (confidentials)

 

[txgeekgirl]

Hi Bob - I have stripped it down to where only my info shows. But it gives you structural relativity.

One thing I have noticed that I am working on is the Activities Report Data (which I was using as a bridge for Record_No and Task Code) on my query DTSbyWeek repopulates by the week the report is run. I will actually build a table for the crossover.

Thanks for your help.

 

[txgeekgirl]

I don't see where the file went - I checked - it is 4MB zipped. It is a huge DB.

 

[txgeekgirl]

OK - so I had my boss - who I call Database Yoda - take a look and we decided that building a function that passes the info into an array and making a temptable would best pull what we need to build the crossover table.

I have an issue though - I am getting an OverFlow error on run. I changed AMax and Indx to Long because Amax feeds off the Record_No field which is defined as a Long Int and Indx feeds off AMax. But I still have an Overflow error. I thought perhaps it could be the size of the Array, but it mismatches when I set it to anything other than Integer. Here is the code:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
' Set AMax to long because of Overflow error and since Indx is defined by AMax -
' it also becomes a long.
Dim RS As Object
Dim stSql, MyStaff As String
Dim con As Object
Dim MyYear, MyQtr As Integer
Dim Indx, AMax As Long

MyYear = DLookup("[Year]", "[Project Parameters]", "[Active] = True")
MyQtr = DLookup("[Quarter]", "[Project Parameters]", "[Active] = True")
AMax = DMax("[Record_No]", "[Activities]", True)

Dim MyArray(1000) As Integer

MyStaff = ""

For Indx = 1 To AMax
MyArray(Indx) = 0
Next Indx
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE TempExport.* FROM TempExport;"
DoCmd.SetWarnings True

Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Daily Time Study Log]"
stSql = stSql & " WHERE [Year] = " & MyYear & " AND [Quarter] = " & MyQtr
stSql = stSql & " ORDER BY [Staff ID#];"

Set RS = CreateObject("ADODB.Recordset")
RS.Open stSql, con, 1 ' 1 = adOpenKeyset
While (Not (RS.EOF))
If MyStaff <> RS(1) Then
If MyStaff <> "" Then

' DUMP ARRAY
For Indx = 1 To AMax
If MyArray(Indx) > 0 Then
My_SQL = "INSERT INTO [TempExport] ([Staff],[Index],[Units]) " + _
"SELECT '" & MyStaff & "', " & Str(Indx) & "," & Str(MyArray(Indx))
DoCmd.SetWarnings False
DoCmd.RunSQL My_SQL
DoCmd.SetWarnings True
End If
Next Indx

For Indx = 1 To AMax
MyArray(Indx) = 0
Next Indx
End If
MyStaff = RS(1)
End If

' PROCESS LINE
For Indx = 6 To 102
If Not IsNull(RS(Indx)) Then
MyArray(RS(Indx)) = MyArray(RS(Indx)) + 1
End If
Next Indx

RS.MoveNext
Wend





Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[boblarson]

I've heard that you can't declare variables like you can in VB where you can nest them on the same line without naming the type. So try changing it to:

' it also becomes a long.
Dim RS As Object
Dim stSql [COLOR="red"]As String[/COLOR], MyStaff As String
Dim con As Object
Dim MyYear, MyQtr As Integer
Dim Indx [COLOR="Red"]As Long[/COLOR], AMax As Long

 

[boblarson]

And actually, you don't need a function using an array; just the right query. You tried to post but did you compact and repair first and then zip it using WinZip or something (to get at or below the size limit here)?

 

[txgeekgirl]

Hi Bob - Thanks - that helped and I forgot to flush the array all the way. I grabbed 1-end instead of 0-end. The 0 hit 32700 real fast and got too full.

 

[txgeekgirl]

BTW - I did try to compress and zip - that was 4.4mb. Original size is 17.1mb for the structure and 65mb for the dataset. I dumped all the dataset except for mine. I have 300+ employees using this database every day for one month per qtr per year.

 

[boblarson]

I see. Well, if the array and function work for you then we don't have to visit it. But if you wind up with problems, perhaps we'll have you email it to me. Let me know how it goes.

 

[txgeekgirl]

Thanks for the help.

 

[txgeekgirl]

Hey Bob - this is a different subject - but I have a feeling if anyone knows, it's you....

I have made my array, populated a table, built my three queries with the last one being called from the end of my code and it's all beautiful. I used a docmd.openquery to run the final query. It asks for the Quarter to be defined and I need the Quarter to = MyQtr (a variable in code).

Now - I tried to have it run in SQL instead so I could define the criteria in a Where clause but I am getting a missing operator error and for the life of me cannot find it.

SO... I made the final query a Make Table query. It works - but I need that query, in VBA code, to make a text file, comma delimited output to a relative path \\rbas\Prod\outbox\2818\filename.txt

Here's my SQL - cause I know you'll ask for it....

Final_Line = "SELECT '02^' & [Quarter] & '^' & [Year] & '^MHMR^170^' & [SSN] & '^'" & _
"Trim([JOB #]) & '^' & [Wk1_Start_Date] & '^' & IIf(IsNull([Wk5_Stop_Date]),[Wk4_Stop_Date],[Wk5_Stop_Date])" & _
"'^' & [SumOfSumOfUnits] & '^' & [SumOfSumOfUnits] & '^I^N^' &

 & '^'" & _
    "[SumOfUnits]" & _
"FROM [Project Parameters], (Users INNER JOIN DTSCodeCount ON Users.[User ID] = DTSCodeCount.Staff) INNER JOIN DTSQtrSum ON Users.[User ID] = DTSQtrSum.Staff" & _
 "WHERE ((([Project Parameters].Quarter) = MyQtr))" & _
 "ORDER BY Users.[SSN ], DTSCodeCount.Code;"
  
    DoCmd.RunSQL Final_Line
     
 
Here's my alternative call:   DoCmd.OpenQuery "DTSFinalLine" which makes a table called Final_Line.
 
Thank you again.