dynamically assign Distinct (a2000)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote