Link to home
Start Free TrialLog in
Avatar of pcoghlan

asked on

Access query needed to bring all vertical records for a given town into a single horizontal record.

I have the following table that lists the nearest 5 towns for a given starting point. Each [TownFrom] has multiple neighboring towns and a distance to it.

[TownFrom], [TownTo], [Distance]
Miami,Palm Beach,11
Miami,Fort Lauderdale,22
Miami, Delray Beach,33
and so on...

I am trying to create the following table with all neighors in a single memo field. Note inclusion of the ") - (".

[TownFrom],[Neighbor1], [Neighbors]
Miami, Palm Beach (11) - Fort Lauderdale (22) - Delray Beach (33)

Before I get the comments about this being bad design I know. The existing table and one I am trying to create are steps on the way to exporting this in a specific format to a static document.

Any input much appreciated.

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Avatar of pcoghlan


Capricorn, thanks for that. I did search but obviously not using the right keywords.

I will try the code and see how I fair

Thanks again.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK, ran the code and got the following message in a small dialog box:

"Compile Error: User-Defined type not defined."

The text highlighted is below:
=Dim rs as dao.Recordset==

dao appears to be the problem.
you may need to add the dao to your references
from the vba window  Tools > references
look for Microsoft DAO x.x Object library and tick it

then do a Debug>compile

It now sees dao.RecordSet as a valid type(?)

However, it now stops on the next line:
Set rs = CurrentDb.OpenRecordset("Select * from NearestTowns where TownFrom ='" & s & "'")

...saying Run-time erorr 3061: Too few paramters. Expected 1.

Can I assume the additional paramters Type, Options and LockEdit are not required?

When I type "Print s" in the immediate window I do see my first record's TownFrom value.

post your
Name of table ?
Field names  ?       Type of data Number or Text or ?
Capricorn, thanks but your prompter pointed me in the right direction, and reminded me of the fact I am an idiot.
I had 'TownFrom' in the code when the field is called 'FromTown'! Oh, and it is a Long which meant I had to remove the speech marks and change 's' from String to Long on the first line of the function.

Thanks for all your help, it is working like a dream now.