Combine master detail records in one query

[mariost]

Hi all,

I would like to create one query that will combine records linked in a master/detail fashion. The results must be returned in one row. The reason for this is that I want to use this query to update data in an Excel spreadsheet.

Specifically, I have a master table that keeps products (productID, productName, productDesc). I also have a details table with the batches of each product (batch_ID, batch_ProductID, batch_date). Each product can have multiple batches.

I want with one query to return all the product details from the products table and all the related batches, BUT IN ONE ROW.

Is this possible?

Thanks for helping.



Marios

[NoellaG]

Hi,

if you want the seperate detail data, you're stuck with several rows even in Excel, but you can use a grouping query to group all details sum, average or other grouping function within 1 record.

grNG

[mariost]

Hi ,

thanks for the response.

It is hard to accept that there is no way to achieve this. I mean it is not that uncommon. And I know you can do it with MS SQL server.

Anyone else to offer some help.

Marios

[ssanfu]

I can't do what you want using a query, but it can easily be done in code.

Open a recordset of the data you want, loop through the recordset adding the data to one or more string variables and write the variable(s) to a text file.
Then in Excel, import the file to a worksheet. Because the text file is tab delimited or comma delimited, each value in a row will be in put into a cell rather that all data in one cell.

[mariost]

Hi Steve,

thanks for the response. This is what I did at the end.

Thanks a lot