A Dlookup alternative that will return all values in a string/array

[supertsik]

Hi

After a lot of research I didn't manage to solve the following problem:

I have a table called 'Autos' with fields [ID], [Owner], [Car]

1 ¦ Mike ¦ BMW
2 ¦ Mike ¦ Ford
3 ¦ Mike ¦ Toyota
4 ¦ Nick ¦ Audi
5 ¦ Nick ¦ Ford
7 ¦ Anna ¦ Honda

I want to be able to get the table 'AllCars' with fields [Owner], [Cars]:

Mike ¦ BMW Ford Toyota
Nick ¦ Audi Ford
Anna ¦ Honda


If I use VBA's Dlookup/DFirst/DLast I am only going to get a single value. How can I get all of them? I could use queries and/or VBA and we can assume that the Autos table is always sorted by the Owner. I don't want to use a crosstab query because it will limit the number of column headings.

I want to use the results in a report that will say "Mike now owns BMW Ford Toyota cars." I think this is something very useful and common and there must be an easy way to do it.

If you don't know the answer to this please just give me an alternative to the Dlookup function that will return an array/string of all the values, and I will experiment myself.

Thanks in advance!

[Michael R]

1st step:

Create a Crosstab query using Cross Tab Query wizard, with [Owner] for row heading and [Car] for column headings. Use [Id] with Count function for number to be calculated for each row and column intersection. Disable include sums.

Now view the query. You have each owners as rows and cars as columns, pretty neat heh?

Here's the SQL, in case you'll have trouble:
TRANSFORM Count(Autos.ID) AS CountOfID
SELECT Autos.Owner
FROM Autos
GROUP BY Autos.Owner
PIVOT Autos.Car;

2nd step: Add the following Function to your project (not form) module:

Function HasCars(strOwner As String) As Variant
 
    Dim db As Database
    Dim fld As Field
    Dim blnIsNull As Boolean
    Set db = CurrentDb
    For Each fld In db.QueryDefs!Autos_Crosstab.Fields
         If fld.Name <> "Owner" Then
            If IsNull(DLookup(fld.Name, "Autos_Crosstab", "[Owner] = '" & strOwner & "'")) Then _
                Flag1 = True
            If blnIsNull = False Then _
                If (DLookup(fld.Name, "Autos_Crosstab", "[Owner] = '" & strOwner & "'")) Then _
                HasCars = HasCars & fld.Name & ", "
          End If
    Next fld
    'cleans the output string
    If Right(HasCars, 1) = Chr(32) Then HasCars = Left(HasCars, Len(HasCars) - 2)
End Function

Save.

3rd step:

Now create a new query that selects distinct car names and utilizes the HasCars function we've created:

SELECT DISTINCT Autos.Owner, HasCars([Owner]) AS HasCars
FROM Autos;

That's all. The function however, can be simplified and improoved by accesing only to the relevant fields using some sort of index, but I'm not much knowladgabe in RecordSet programming. Maybe someone else could simplify it.

[Michael R]

Note: the crosstab query name should be: Autos_Crosstab

[Michael R]

Addition: In case some owner names might be entered as "Name ", for the HasCars to be calculated without extra commas you can replace this code:

'cleans the output string
    If Right(HasCars, 1) = Chr(32) Then HasCars = Left(HasCars, Len(HasCars) - 2)

with this one:

'cleans the output string
    If Right(HasCars, 2) = "," & Chr(32) Then HasCars = Left(HasCars, Len(HasCars) - 2)

[supertsik]

Thanks very much for your reply.

Unfortunately, I have thought about using your method, but as I mentioned in my question I cannot use crosstab queries simply because the column headings I need are 792! As you understand, Cars table was just a simple example. My real problem is not cars, it's the 792 construction areas for the venues of the Olympic games.

It's very bizarre that you cannot create a query in a simple way that will group your answers in a single field separated by commas. As you can use COUNT, SUM and all the other aggregate functions in SQL, a CSV function would be really useful. It seems to me that lots of people would want to do something like that.

If you, or anybody else has any better ideas I would really appreciate them!

[NeoPa]

Thanks very much for your reply.

Unfortunately, I have thought about using your method, but as I mentioned in my question I cannot use crosstab queries simply because the column headings I need are 792! As you understand, Cars table was just a simple example. My real problem is not cars, it's the 792 construction areas for the venues of the Olympic games.

It's very bizarre that you cannot create a query in a simple way that will group your answers in a single field separated by commas. As you can use COUNT, SUM and all the other aggregate functions in SQL, a CSV function would be really useful. It seems to me that lots of people would want to do something like that.

If you, or anybody else has any better ideas I would really appreciate them!

Why people continually seem to expect this to be a natural concept in either SQL or any RDBMS is entirely beyond me. This and numbering lines in a query, both entirely foreign to SQL concepts.
However, if you need it there is something that can approximate to what you need (Combining Rows-Opposite of Union ).
Let me know if you have any trouble getting it to work in your environment.

[ADezii]

Hi

After a lot of research I didn't manage to solve the following problem:

I have a table called 'Autos' with fields [ID], [Owner], [Car]

1 ¦ Mike ¦ BMW
2 ¦ Mike ¦ Ford
3 ¦ Mike ¦ Toyota
4 ¦ Nick ¦ Audi
5 ¦ Nick ¦ Ford
7 ¦ Anna ¦ Honda

I want to be able to get the table 'AllCars' with fields [Owner], [Cars]:

Mike ¦ BMW Ford Toyota
Nick ¦ Audi Ford
Anna ¦ Honda


If I use VBA's Dlookup/DFirst/DLast I am only going to get a single value. How can I get all of them? I could use queries and/or VBA and we can assume that the Autos table is always sorted by the Owner. I don't want to use a crosstab query because it will limit the number of column headings.

I want to use the results in a report that will say "Mike now owns BMW Ford Toyota cars." I think this is something very useful and common and there must be an easy way to do it.

If you don't know the answer to this please just give me an alternative to the Dlookup function that will return an array/string of all the values, and I will experiment myself.

Thanks in advance!

It took a little thought, but here is code which will accomplish exactly what you want without the use of a Crosstab Query:
__1 Create a Query called qryUniqueValues. This Query will contain only the [Owner] Field from the Autos Table in Ascending Order. It will also have its Unique Values property set to Yes. This will establish an ascending list of 'Unique' Owners.
__2 Create the AllCars Table with 2 Fields [Owner] (Text 50) and [Cars] (Memo). All the Data will be Appended to this Table: a Unique Owner to the [Owner] Field, and a comma-delimited list of all cars that the [Owner] has will be appended to the [Cars] Field.
__3 Run the following code from wherever you so desire. If you have any other questions, feel free to ask.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim RSAutos As DAO.Recordset, strCars As String
Dim RSAllCars As DAO.Recordset
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("qryUniqueOwners", dbOpenDynaset)
Set RSAutos = MyDB.OpenRecordset("Autos", dbOpenDynaset)
Set RSAllCars = MyDB.OpenRecordset("AllCars", dbOpenDynaset)
 
Do While Not MyRS.EOF
  Do While Not RSAutos.EOF
    If MyRS![Owner] = RSAutos![Owner] Then
      strCars = strCars & RSAutos![Car] & ","
    End If
    RSAutos.MoveNext
  Loop
    With RSAllCars
      .AddNew
         RSAllCars![Owner] = MyRS![Owner]
         RSAllCars![Cars] = Left$(strCars, Len(strCars) - 1)
      .Update
    End With
  strCars = vbNullString
  RSAutos.MoveFirst
  MyRS.MoveNext
Loop
 
MyRS.Close
RSAutos.Close
RSAllCars.Close

Sample Output (the AllCars Table), after single execution:

Owner    Cars
 
Anna    Honda
Mike    BMW,Ford,Toyota
Nick    Audi,Ford
Pete    Cadillac,Porsche,Lamborghini,Presada,Saturn

[supertsik]

Thanks A Lot!!! It Really Helps!!!