Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dynamically assign Distinct (a2000)

    In addition to being able to use a form to provide criteria to the query, is there also a way to use that form to make specific fields distinct or not? It would really cut down on the number of queries and forms I need to create, but though I've been scouting around I haven't seen anything suggesting an approach to doing this.

    E

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

    Re: dynamically assign Distinct (a2000)

    I'm not sure what you mean here. You can construct an SQL statement in code, including a WHERE clause (criteria), an ORDER BY clause (sorting) and a GROUP BY clause (summarizing), then set the record source of a form to the SQL statement. But as soon as you include a GROUP BY clause, the query and hence the form will not be updatable any more.
    Can you explain what exactly you want to do?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamically assign Distinct (a2000)

    I'll try to filling the blanks some. Forgive me if I belabor the obvious...

    I got a query that combines multiple tables related to Orders placed (tblOrder; tblOrderDetail, tblProduct, tblDiscount, tblConsumer, tblAddrHm, tblEmail, tblPhone, tblOrg, tblOrgType, etc. In order to get down to the details about the order and the purchaser like product code and purchaser email, lots and lots of the fields in the query now list the same information multiple times since it's the same consumer, ship to address, etc for each item ordered.

    I'd like to be able to use this query to generate a list of unique emails, or unique fax numbers, or unique addresses, etc. for all the consumers who bought a particular product, or purchased between specific dates, or have a certain kind of business, etc.

    I've been searching on the product code, using "is not null" for the field in question, creating a temporary take with a make table query and then manipulating the table to eliminate the duplicate values. Seems like there had to be a much better way. The recordset doesn't need to be updatable.

    E.

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

    Re: dynamically assign Distinct (a2000)

    You can put a combo box on the form to list the fields of a query - set the Row Source Type property to Field List and the Row Source property to the name of the query. You can then generate the SQL for a query that selects distinct values for this field.

    For example, to fill a list box with distinct values:

    Dim strSQL As String
    If IsNull(Me.cboFieldNames) Then
    MsgBox "Please select a field.", vbExclamation
    Me.cboFieldNames.SetFocus
    Exit Sub
    End If
    strSQL = "SELECT DISTINCT [" & Me.cboFieldNames & "] FROM qrySomething"
    Me.lboUniqueValues.RowSource = strSQL

    Or to fill a table:

    ...
    strSQL = "SELECT DISTINCT [" & Me.cboFieldNames & "] INTO tblTemp FROM qrySomething"
    CurrentDb.Execute strSQL

    You can, of course, add a WHERE part to the SQL.

Posting Permissions

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