Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    counting from query (2000-SR1)

    Having trouble getting a query to do what I want. I have a table where each student can order one or more from about 15 choices. Each of those choices is in a check box filed if that item is ordered. So...say I have fifteen fields, each record (each student) could have a checked box in any or all of the fifteen fields. I want to have a total for each field in a report. Is there a way to get the query to do this (seems easier than doing the counts and totals fifteen different times in the report itself--and is there a limit to the number of groupings that can be done in the report?)?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting from query (2000-SR1)

    Can you still change the design of the database? It would be much better not to have a separate Yes/No field for each item, but instead a table with fields StudentID and ItemID (or something similar), with a separate record for each item chosen by a student. Such a setup would make it very easy to tally various ways.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting from query (2000-SR1)

    Just to be sure I understand...the table then might look something like this?
    StudentID ItemID ItemQty ItemColor
    123 002 1 White
    123 004 2 Red
    254 002 1 Black
    254 004 3 Gold
    254 012 1 White
    322 002 1 Red

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting from query (2000-SR1)

    If color is not linked to ItemID, that looks right.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting from query (2000-SR1)

    I would like a form that would have an option button for each of the possible items to order...so, the code behind the option button would need to generate the OrderID (autonumber field) and specify the ItemID to match each particular item to be stored in the "tblOrders". Is my thinking correct? If so, how would that code look?

    (I would like the form to look simply like the long list of the item names with option buttons to select (and some of the items also require the fields to select the color and indicate the quantity).)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting from query (2000-SR1)

    The "standard" way of doing this in Access is to use a continuous subform on the main form with combo boxes for the item and the color, and a text box (or perhaps a combo box) for the quantity. The subform would initially be empty, and each time the user selects and item from the item combo box, a new record will be created automatically.

    A form that lists all items would be more work, because there are no records yet for that order. If it is allowed to order a quantity of item 001 in red, and also a quantity of item 001 in blue, it becomes virtually impossible.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting from query (2000-SR1)

    I was visualizing a series of labels (one for each item) with a series of unbound option buttons with code that would generate a new record each time one of the buttons is clicked (that is complicated by the fact that there would also be combo boxes to select the color and text box to indicate the number of that item to order). If that is not do-able, I can use the continuous form as you recommended.
    The main reason I was pursuing this path is that resembles the order form that people use in handwritten form, then it is transferred by the user to the database, and works well visually to make that transfer.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting from query (2000-SR1)

    You could create an unbound form with controls for each item, with an OK button that would create records as needed, and a Cancel button that would simply close the form without saving anything. Downside is that you'd have to modify the design each time the collection of items changes.

    Alternatively, you could generate a form in code, but that is not very attractive, or create a temporary table to act as record source for a continuous form, but that quickly leads to database bloat.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting from query (2000-SR1)

    It is unlikely that the item selection would change much. It has been used in one form or another for about three years without any changes in the possible items... what would the code need to look like to generate a new record for each item (and corresponding quantity and color)?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting from query (2000-SR1)

    You could create a form with for each item:

    - a label describing the item
    - a combo box for selecting the color
    - a text box or combo box for selecting the quantity.

    It's most convenient if the items have ItemID 1 through (say) 20, and to number the controls correspondingly: lblItem1 ... lblItem20, cboColor1 ... cboColor20, txtQuantity1 ... txtQuantity20. The code for the OK button could look like this:

    Private Sub cmdOK_Click()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim i As Integer
    Dim intQuantity As Integer

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    rst.Open "tblSelectedItems", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    For i = 1 To 20
    intQuantity = Val(Me.Controls("txtQuantity" & i))
    If intQuantity > 0 Then
    rst.AddNew
    rst!StudentID = Me.StudentID
    rst.ItemID = i
    rst.ColorID = Me.cboColor
    rst.Quantity = intQuantity
    rst.Update
    End If
    Next i

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    You'll have to substitute the correct names, and work out the details.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: counting from query (2000-SR1)

    If you use this solution, I think you need to be aware that the form you use to create new orders cannot be used to look at old ones.

    You would then have to create a different form for looking at old orders. The easy solution would be a main form with a continuous subform as Hans suggested in an earlier post. If you wanted that to have tick boxes for each product then you will have more work to do. If you also use an unbound form for looking at old orders you then need to deal with any changes made to an order.

    Personally I think it would have been much easier just to build the 15 queries back at the start of this thread. You could have had them finished and the report done in 10 minutes.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •