Combine records into one row

[Back2Basics]

Hi



I have two tables; tbl_site and tbl_item. There is a link between the two tables, Site_ID, in such a way that several items can be links to one site. What I am hoping to achieve is to create a query that displays the site (and a few other fields, but I got those covered I believe) and the linked items - but instead of having a row for each item I would like to combine them so that it displays: "item1 and item2 and item3" or "item1 and item2" (there could be 1 or more item per site).

item field to display is called: "Item_Ref"

Is this possible to do and if so is there anyone out there that might know how to do it?

TIA

[ranman256]

make a query to pull the data you want.
then use the Query Wizard to create a CROSSTAB query on the above query.
it will ask what to use as row header,
the field for column headers
and the value (be sure to set it as FIRST ,not sum)

[Back2Basics]

Thanks ranman, I will have a play with this. I have had an initial try and it doesn't do quite what I was after, but it looks like I might be able to get each item_ref in a separate column and hopefully I can do something with that?? Ideally I am looking to get all of the relevant item_ref into one field such that I have a row header of 'item_ref' a column header of 'site_ref' and it just displays one column with all of the item_ref's in one place??

[Back2Basics]

Okay, I have created a crosstab query from the tbl_item table so it is has the site_ref as the row header and item_ref as column headers. It displays what I expect and has a row for each site and then a column for each item. Where the item isn't relevant for the site it displays a blank field, so that's good, and then the item reference where it is relevant.

I have then adjusted my other query so that in one field I have the following expression (at the moment I just have test data in there so it's not much and I am just using itemA, itemb, etc as references): Item Reference: [qry_item_crosstab]![ItemA] & " and " & [qry_item_crosstab]![ItemB] & " and " & [qry_item_crosstab]![ItemC]

This displays the following:
SiteA > "ItemA and and"
SiteB > "and ItemB and ItemC"

Any advice on how I can change this expression so that I don't have to put each item in the expression, as there will be hundreds later down the line and new ones added, and also to remove the 'and' where they won't make sense?

Again, thanks for the advice - it is greatly appreciated