Independent Columns? (1 Viewer)

[Beerbrewer]

i've three tables.
1. Customer (concerns customer data)
2. Conversations (concerns conversations with customer)
3. Invoices (concerns outstanding invoices of customer)
Last two tables got the key of table Customer but have an arbitrary lenght to one another.
I would like to export this data to excel in a way that both Conversations and Invoices are shown per customer. In a report this is manageable by creating two subreports both linked with Customer. In a query however the result is multiplied and i get Conversations per Invoice per Customer.
Exporting the report to Excel causes problems because i get a lot of unwanted headers (above each subreport per customer). So i tried to create a query to export to excel.
Is it possible to create a recordset that somehow unites two fields that are not linked to one another but both to one field?

 

[Pat Hartman]

As you have discovered, joining tables that have no relationship to each other just creates nonsence recordsets. Sort of like taking the pages from two books and shuffling them together.

Using TransferSpreadsheet, you don't get to specify where a particular recordset gets put. therefore, since you have two separate recordsets that you want to export, you'll need to use OLE so that you can control how they are placed.

 

[Beerbrewer]

Thanx, but how do i use OLE to achieve this ?
Remain both fields linked to Customer so that a number of rows is inserted equal to the maximum number of records of one of those two tables?
Example:
Customer1 has 3 conversations & 4 invoices
Customer2 has 3 conversations & 2 invoices
etc
It should look like this
Customer1 Conversation1 Invoice1
.............. Conversation2 Invoice2
.............. Conversation3 Invoice3
.............. ................... Invoice4
Customer2 Conversation1 Invoice1
.............. Conversation2 Invoice2
.............. Conversation3
etc.

 

[Pat Hartman]

There is no way that you're going to get a recordset that looks like that unless you write VBA to produce it and you will have trouble trying to replicate that format with two separate recordsets since for any given customer, you cannot tell in advance whether the comments or the invoices column will contain the most rows.

You can get something that looks similar by creating a report that contains two subreports. One subreport for comments and the second which can be displayed next to it, for invoices.

What is the purpose of exporting this data to Excel?

 

[Beerbrewer]

Thanks for your help but after a Holiday I'm no longer on the job. My apologies for this late reply.