Concatenate text values in a many side of a relationship

[Lyn Mac]

Hello folks!

I have two tables tblVessel and tblEngine. At any given point in time a Vessel can have 1 or more Engines. So the table relationship is set-up that tblVessel is on the one-side while tblEngine is on the many-side bound by the pkVesselID . Typical field in the tblVessel is VesselName and typical fields in the tblEngine are EngineMake and EngineModel.

Suppose a vessel named MV "Access World" has 2 engines (representing 2 records of course) Mitsubishi, with model 4DR5, and Mazda, with model RF 200, how do I do this in a query to show a result like this:

MV "Access World" 2 Mitsubishi/Mazda 4DR5/RF 200

The 1st column refers to the VesselName (MV "Access World"), next is the count of engines (2), then the EngineMake (Mitsubishi/Mazda) and last is the EngineModel (4DR5/RF 200).

Eventually, this query will be a RecordSource in a report.
Advance Thanks for your help everyone.


Lyn

 

[Alexandre]

Although possible it is not advisable to try to get this format directly from queries, since concatenating the engine models on the many side of the relationship will involve code that will considerably slow down your query.
Try to get that result on a report (make a simple query gathering your info, then do the count and concatenation directly in textboxes in the report)

As to how to do the concatenation, have a look to:

Concatenate sub-records values

Re-post if needed

 

[Lyn Mac]

Many Thanks Alex. Your advice is always most valued.

I have looked and copied the code at the Access Web for study.

_________
Lyn

 

[Lyn Mac]

BTW,

how is this done in an Access Report? Any pointers please.

Anybody cares to respond?

TIA,

_____

Lyn

 

[Cosmos75]

Helpful threads.. maybe?

http://www.access-programmers.co.uk...d=13113&highlight=Combining+Fields+in+a+query

http://www.access-programmers.co.uk...d=13621&highlight=Combining+Fields+in+a+query

http://www.access-programmers.co.uk...d=12964&highlight=Combining+Fields+in+a+query

http://www.access-programmers.co.uk...d=12408&highlight=Combining+Fields+in+a+query

 

[Lyn Mac]

Thanks Cosmos75 and Alexandre for your efforts.



Lyn