By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,082 Members | 2,108 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,082 IT Pros & Developers. It's quick & easy.

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

P: 7
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!
Feb 22 '07 #1
Share this Question
Share on Google+
7 Replies


100+
P: 176
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:
Expand|Select|Wrap|Line Numbers
  1. Function HasCars(strOwner As String) As Variant
  2.  
  3.     Dim db As Database
  4.     Dim fld As Field
  5.     Dim blnIsNull As Boolean
  6.     Set db = CurrentDb
  7.     For Each fld In db.QueryDefs!Autos_Crosstab.Fields
  8.          If fld.Name <> "Owner" Then
  9.             If IsNull(DLookup(fld.Name, "Autos_Crosstab", "[Owner] = '" & strOwner & "'")) Then _
  10.                 Flag1 = True
  11.             If blnIsNull = False Then _
  12.                 If (DLookup(fld.Name, "Autos_Crosstab", "[Owner] = '" & strOwner & "'")) Then _
  13.                 HasCars = HasCars & fld.Name & ", "
  14.           End If
  15.     Next fld
  16.     'cleans the output string
  17.     If Right(HasCars, 1) = Chr(32) Then HasCars = Left(HasCars, Len(HasCars) - 2)
  18. 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.
Feb 23 '07 #2

100+
P: 176
Note: the crosstab query name should be: Autos_Crosstab
Feb 23 '07 #3

100+
P: 176
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:
Expand|Select|Wrap|Line Numbers
  1. 'cleans the output string
  2.     If Right(HasCars, 1) = Chr(32) Then HasCars = Left(HasCars, Len(HasCars) - 2)
with this one:
Expand|Select|Wrap|Line Numbers
  1. 'cleans the output string
  2.     If Right(HasCars, 2) = "," & Chr(32) Then HasCars = Left(HasCars, Len(HasCars) - 2)
Feb 23 '07 #4

P: 7
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!
Feb 23 '07 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 26 '07 #6

ADezii
Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim RSAutos As DAO.Recordset, strCars As String
  3. Dim RSAllCars As DAO.Recordset
  4.  
  5. Set MyDB = CurrentDb()
  6. Set MyRS = MyDB.OpenRecordset("qryUniqueOwners", dbOpenDynaset)
  7. Set RSAutos = MyDB.OpenRecordset("Autos", dbOpenDynaset)
  8. Set RSAllCars = MyDB.OpenRecordset("AllCars", dbOpenDynaset)
  9.  
  10. Do While Not MyRS.EOF
  11.   Do While Not RSAutos.EOF
  12.     If MyRS![Owner] = RSAutos![Owner] Then
  13.       strCars = strCars & RSAutos![Car] & ","
  14.     End If
  15.     RSAutos.MoveNext
  16.   Loop
  17.     With RSAllCars
  18.       .AddNew
  19.          RSAllCars![Owner] = MyRS![Owner]
  20.          RSAllCars![Cars] = Left$(strCars, Len(strCars) - 1)
  21.       .Update
  22.     End With
  23.   strCars = vbNullString
  24.   RSAutos.MoveFirst
  25.   MyRS.MoveNext
  26. Loop
  27.  
  28. MyRS.Close
  29. RSAutos.Close
  30. RSAllCars.Close
Sample Output (the AllCars Table), after single execution:
Expand|Select|Wrap|Line Numbers
  1. Owner    Cars
  2.  
  3. Anna    Honda
  4. Mike    BMW,Ford,Toyota
  5. Nick    Audi,Ford
  6. Pete    Cadillac,Porsche,Lamborghini,Presada,Saturn
Feb 26 '07 #7

P: 7
Thanks A Lot!!! It Really Helps!!!
Mar 2 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.