Count function in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a frmMultProduction with a multi selection list box(mylist) that has a
RequiredDate, ModelNumber and Description the list row source = qrProdution

qrProduction has RequiredDate, ModelNumber, Description, spring, foam and so
on

i want to create a query that counts the springs accordingly to the
selection from mylist. so if i select two line items from mylist, count the
spring.

Can anyone help me with this.
 
You'll have to iterate through the list box to find which items are selected
and then build a totals query based on the selections.

Use Me.mylist.ListCount to find the number of items in the list.
Use Me.mylist.Selected(#) = True to find the selected items.
Use Me.mylist.ItemData(#) to find the value of the selected items.

Hopefully, the bound column in the list is a unique identifier so you can
construct an SQL statement like:
"SELECT spring FROM qrProduction WHERE [ID]=" & Me.mylist.ItemData(#) & " OR
[ID]=" & Me.mylist.ItemData(#2) & " OR [ID]="...

When you say "count the springs" do you mean count the unique spring values?

Someone else may be able to do this strictly with queries, but I would use VB
and the above expressions. hth
 
Thanks Kingston,

i am fairly new in this, can you tell me where or under what control do i
put the iteration under to come up with the result that i want this way. to
answer your other question of what i mean about count of spring, is that
every item on the list has a spring, if i select 2 items and the first item
has sp001 and the second item has a spring of sp001 then the count will be
sp001 = 2. i hope this is clear enough the bounded control on the list box is
a RequiredDate and it's not a primary key, any other suggestion? thanks
hugely for your help.
--
need help


kingston via AccessMonster.com said:
You'll have to iterate through the list box to find which items are selected
and then build a totals query based on the selections.

Use Me.mylist.ListCount to find the number of items in the list.
Use Me.mylist.Selected(#) = True to find the selected items.
Use Me.mylist.ItemData(#) to find the value of the selected items.

Hopefully, the bound column in the list is a unique identifier so you can
construct an SQL statement like:
"SELECT spring FROM qrProduction WHERE [ID]=" & Me.mylist.ItemData(#) & " OR
[ID]=" & Me.mylist.ItemData(#2) & " OR [ID]="...

When you say "count the springs" do you mean count the unique spring values?

Someone else may be able to do this strictly with queries, but I would use VB
and the above expressions. hth

Will said:
I have a frmMultProduction with a multi selection list box(mylist) that has a
RequiredDate, ModelNumber and Description the list row source = qrProdution

qrProduction has RequiredDate, ModelNumber, Description, spring, foam and so
on

i want to create a query that counts the springs accordingly to the
selection from mylist. so if i select two line items from mylist, count the
spring.

Can anyone help me with this.
Click to expand...
Click to expand...
 
You'll have to decide when you want this code to run and how to output the
results. In other words, you'll have to pick an event (e.g. the listbox's
AfterUpdate event or a button click event) and you'll have to write the
results to something for the user (e.g. a message box or a temporary table).

Let's assume you create a button and use its OnClick event. You'd use the
expressions I posted earlier to create a recordset. Then go through the
resulting recordset (For i = 1 to Recordset.RecordCount...) to identify the
different types of springs and count the number of times they appear in the
recordset.

As far as the default value of the listbox not being a PK, you'll have to
come up with a composite key that uniquely identifies the record (e.g.
RequiredDate and ModelNumber). Modify the SQL string's WHERE clause to match
all fields. You can refer to the other columns in the listbox with:

Me.mylist.Column(column, row)

Be careful, Columns and Rows start from 0, not 1.

Will said:
Thanks Kingston,

i am fairly new in this, can you tell me where or under what control do i
put the iteration under to come up with the result that i want this way. to
answer your other question of what i mean about count of spring, is that
every item on the list has a spring, if i select 2 items and the first item
has sp001 and the second item has a spring of sp001 then the count will be
sp001 = 2. i hope this is clear enough the bounded control on the list box is
a RequiredDate and it's not a primary key, any other suggestion? thanks
hugely for your help.
You'll have to iterate through the list box to find which items are selected
and then build a totals query based on the selections.
Click to expand...
[quoted text clipped - 24 lines]
Click to expand...
 
thanks a bunch for your help and patience....i am very new to this.
this is so far what i have, can you check it out and point out what's
missing and what's wrong. thanks again

Private Sub PO_Click()
Dim varItem As Variant
Dim strWhere As String
Dim strDelim As String
Dim lngLen As Long

strDelim = "#"

With Me.MyList
For Each varItem In .ItemsSelected
strWhere = strWhere & trDelim & .ItemData(varItem) & strDelim & ","
Loop
End With
strWhere = "SELECT spring FROM qrProduction WHERE [ID]=" &
Me.MyList.ItemData(varItem)& " "
DoCmd.OpenReport "rptSpring", acViewPreview, , strWhere
--
need help


kingston via AccessMonster.com said:
You'll have to decide when you want this code to run and how to output the
results. In other words, you'll have to pick an event (e.g. the listbox's
AfterUpdate event or a button click event) and you'll have to write the
results to something for the user (e.g. a message box or a temporary table).

Let's assume you create a button and use its OnClick event. You'd use the
expressions I posted earlier to create a recordset. Then go through the
resulting recordset (For i = 1 to Recordset.RecordCount...) to identify the
different types of springs and count the number of times they appear in the
recordset.

As far as the default value of the listbox not being a PK, you'll have to
come up with a composite key that uniquely identifies the record (e.g.
RequiredDate and ModelNumber). Modify the SQL string's WHERE clause to match
all fields. You can refer to the other columns in the listbox with:

Me.mylist.Column(column, row)

Be careful, Columns and Rows start from 0, not 1.

Will said:
Thanks Kingston,

i am fairly new in this, can you tell me where or under what control do i
put the iteration under to come up with the result that i want this way. to
answer your other question of what i mean about count of spring, is that
every item on the list has a spring, if i select 2 items and the first item
has sp001 and the second item has a spring of sp001 then the count will be
sp001 = 2. i hope this is clear enough the bounded control on the list box is
a RequiredDate and it's not a primary key, any other suggestion? thanks
hugely for your help.
You'll have to iterate through the list box to find which items are selected
and then build a totals query based on the selections.
Click to expand...
[quoted text clipped - 24 lines]
Can anyone help me with this.
Click to expand...
Click to expand...
Click to expand...
 
You've probably figured this out by now, but here's the syntax I think you're
looking for:

...
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
...
strWhere = "[ID] IN (" & Left(strWhere,Len(strWhere)-1) & ")"
...

However, I don't think that this will get you what you described earlier.
I'm assuming that this is based on RequiredDate, but you'd mentioned that
this wasn't a primary key. Also, I'm assuming that you're doing the counting
in the report.

Will said:
thanks a bunch for your help and patience....i am very new to this.
this is so far what i have, can you check it out and point out what's
missing and what's wrong. thanks again

Private Sub PO_Click()
Dim varItem As Variant
Dim strWhere As String
Dim strDelim As String
Dim lngLen As Long

strDelim = "#"

With Me.MyList
For Each varItem In .ItemsSelected
strWhere = strWhere & trDelim & .ItemData(varItem) & strDelim & ","
Loop
End With
strWhere = "SELECT spring FROM qrProduction WHERE [ID]=" &
Me.MyList.ItemData(varItem)& " "
DoCmd.OpenReport "rptSpring", acViewPreview, , strWhere
You'll have to decide when you want this code to run and how to output the
results. In other words, you'll have to pick an event (e.g. the listbox's
Click to expand...
[quoted text clipped - 31 lines]
Click to expand...
 
yes , RequiredDate is not a primary key, however, a composite primary key
might help. i like to base my output on two keys, the RequiredDate and
OrderNo......RequiredDate being a Date type and OrderNo a Number type....now
this is what i have and can you lead me on to adding the other side of the
code to include OrderNo........i would think an AND somewhere....thanks
again....

On Error GoTo Err_Handler
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
strDelim = "#" 'Delimiter appropriate to field type. See
note 1.
strDoc = "rptProduction"
'strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
End If
Next
End With
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[RequiredDate] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
strDescrip = "Order No" & Left$(strDescrip, lngLen)
End If
End If
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip
--
need help


kingston via AccessMonster.com said:
You've probably figured this out by now, but here's the syntax I think you're
looking for:

...
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
...
strWhere = "[ID] IN (" & Left(strWhere,Len(strWhere)-1) & ")"
...

However, I don't think that this will get you what you described earlier.
I'm assuming that this is based on RequiredDate, but you'd mentioned that
this wasn't a primary key. Also, I'm assuming that you're doing the counting
in the report.

Will said:
thanks a bunch for your help and patience....i am very new to this.
this is so far what i have, can you check it out and point out what's
missing and what's wrong. thanks again

Private Sub PO_Click()
Dim varItem As Variant
Dim strWhere As String
Dim strDelim As String
Dim lngLen As Long

strDelim = "#"

With Me.MyList
For Each varItem In .ItemsSelected
strWhere = strWhere & trDelim & .ItemData(varItem) & strDelim & ","
Loop
End With
strWhere = "SELECT spring FROM qrProduction WHERE [ID]=" &
Me.MyList.ItemData(varItem)& " "
DoCmd.OpenReport "rptSpring", acViewPreview, , strWhere
You'll have to decide when you want this code to run and how to output the
results. In other words, you'll have to pick an event (e.g. the listbox's
Click to expand...
[quoted text clipped - 31 lines]
Can anyone help me with this.
Click to expand...
Click to expand...
Click to expand...
 
To use a composite key, you'll have to go back to a standard WHERE clause.
My earlier suggestion of using a subquery with IN will not work. The WHERE
clause will need to look like:

"WHERE ([RequiredDate]=#" & ...mylist.Column(column0, row1) & "# AND [OrderNo]
=" & ...mylist.Column(column2, row1)) OR ([RequiredDate]=#" & ...mylist.
Column(column0, row2) & "# AND [OrderNo]=" & ...mylist.Column(column2, row2))
OR ([RequiredDate]=#" & ...mylist.Column(column0, row3) & "# AND [OrderNo]="
& ...mylist.Column(column2, row3))..."

This assumes that column0 contains the date data and column 2 contains a
number. The rows correspond to rows that have been selected in the listbox.

However, here's another idea that might make things easier. Instead of using
a list box, create a small subform with the same data and add a Yes/No column
to the underlying table. So rather than users selecting items in the listbox,
they'd simply check boxes. Then you'd sum over records with a check mark.


Will said:
yes , RequiredDate is not a primary key, however, a composite primary key
might help. i like to base my output on two keys, the RequiredDate and
OrderNo......RequiredDate being a Date type and OrderNo a Number type....now
this is what i have and can you lead me on to adding the other side of the
code to include OrderNo........i would think an AND somewhere....thanks
again....

On Error GoTo Err_Handler
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
strDelim = "#" 'Delimiter appropriate to field type. See
note 1.
strDoc = "rptProduction"
'strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
End If
Next
End With
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[RequiredDate] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
strDescrip = "Order No" & Left$(strDescrip, lngLen)
End If
End If
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip
You've probably figured this out by now, but here's the syntax I think you're
looking for:
Click to expand...
[quoted text clipped - 35 lines]
Click to expand...
 
Back
Top