Unanswered: Concatenate related records in a access table?
I am new to the forum and looking for some assistance please.
I have an access table that has been imported straight from Excel. The table has records that should really be related through 2 tables, but they are all in the one table at the moment.
The table contains individual property lists and each property is allocated to a vehicle which goes to that property. I.e there is a [RoundNumber] field which details which vehicle visits the property. I also have a [BuildingNumber] field and a [StreetName] field which details the individual property number and street address for each record. The street address field may contain the same value. I also have a [NoItems] field which details the number of items to be picked up from that particular property.
What I want to do is identify all the records with matching [RoundNumber] and [StreetAddress] values and for each record that matches I want to concatenate the [BuildingNumber] data and separate in by a comma into a separate field called [HouseNoList]. I also want to total the number of items [NoItems] field to detail the total number of items to be picked up for that particular street. I then want to transfer the concatenated records to another table within MS Access.
Can anyone advise me of the best way to do this in Access?