Concatenate data from field where one-to-many relationship

[Henryp]

There are three tables to allow individual addresses to have multiple types. What I would like is to produce output that has one address per line, with the types listed for each.

Here are the table structures:

Table 1: Addresses

AddressID (123,124,125,126 etc)
AddressLine1
AddressLine2
AddressTown
etc

Table 2: Address_AddressType

AddressID
TypeID

Table 3: AddressType

TypeID (1,2,3,4 etc)
TypeDescription (A,B,C,D etc)

Table 1 has one address per record. Table 2 has a record for each combination of AddressID and TypeID. Table 3 is just a type lookup table.

The output that I would like would read as follows:

AddressID AddressLine1 AddressLine2 AddressTown Types
123 Tech House 12 No street NoVille A, D
124 Next House 41 Other St NewTown B, C
125 Big Shop 1 The Place BigTown D

This seems simple enough, but I can't get Access to play ball.

cheers
HP

 

[LittleSmudge]

What you seem to have is a classic many-to-many relationship with a bridging table in the middle.

If that's the case then it'll save a lot of confusion if you use the Ref suffix on Foreign Keys and keep the Id suffix for Primary Keys
Table 2:
Address_AddressType
AddressRef
TypeRef


By "Output" do you mean this is heading for a Report ?

You won't get Access to 'automatically' concatenate the Type fields into a string for you - but it can be done in code.

In the On_Current ( for Form ) or On_Format ( for Reports ) event open a recordset on the complete structure
"SELECT TypeDescription FROM Address INNER JOIN ( Address_AddressType INNER JOIN AddressType ON Address_AddressType.TypeRef = AddressType.TypeId ) ON Address.AddressId = Address_AddressType.AddressRef WHERE AddressId = " & ControlNameOnForm

WHILE Not rst.EOF
AddrTypeControl = AddrTypeControl & rst.TypeDescription & ", "
Wend
AddrTypeControl = Left(AddrTypeControl, Len(AddrTypeControl) - 2 )


Where
ControlNameOnForm hold the AddressId of the current address.
AddrTypeControl is the name of the text box control where you want the Type info to appear.



'ope-that-'elps.

G LS