How to export some info to a spreadsheet in certain format

[DashDash]

I need to export order information to spreadsheet in certain format. As shown in following table.

How can I combine one to many relationship item with quantity in to one cell?

Thanks in advance!

Now

Order Customer
OrderA John Item Qty

sugar 2
​

Pepsi 4
​

coke 2
​

OrderB Bill Item Qty

fish 3
​

chips 3
​

OrderC Tom Item Qty

cup 1
​

cable 4
​

Wanted
Order Customer Items
OrderA John Sugarx2,Pepsix4,Cokex2
OrderB Bill Fishx3, Chipsx3
OrderC Tom Cupx1, Cablex4

 

[HiTechCoach]

I would use something like this:

Return a concatenated list of sub-record values

 

[DashDash]

I would use something like this:

Thanks for the reply.
I will try to understand it but for now Im kinda newbie in Access, I am thinking a more entry level way to do it, is it possible to create a query to achieve this? say create a qurey to list all items for each order, then use report or continuous forms to display the summary and create a macro to export that report or forms as spreadsheet. Is this achievable? or is there any simper way to do it? and any build-in function I should consider to use to concatenate the items.

Thanks for any help!

 

[HiTechCoach]

Thanks for the reply.
I will try to understand it but for now Im kinda newbie in Access, I am thinking a more entry level way to do it, is it possible to create a query to achieve this? say create a qurey to list all items for each order, then use report or continuous forms to display the summary and create a macro to export that report or forms as spreadsheet. Is this achievable? or is there any simper way to do it? and any build-in function I should consider to use to concatenate the items.

Thanks for any help!

The problem, is that you said you wanted:

How can I combine one to many relationship item with quantity in to one cell?

To achieve this take a lot of extra effort. This is not normally how a relational database does stuff.

If you wanted each item in a separate cell and each with quantity separate cell on the same row, that is easy.

is it possible to create a query to achieve this?

If you use the code I provided in the link then yes you can do it with a single query and then export tit to excel. This will allow you to combine the data from the related records into one cell.

say create a qurey to list all items for each order, then use report or continuous forms to display the summary and create a macro to export that report or forms as spreadsheet. Is this achievable?

Yes it will work but the Excel file will look just the the report. You will not get the items with quantity in to one cell.

 

[DashDash]

Thank you HitechCoach, I think I omitted some information here, actually I have many to many relationship, I normailiesed it in following way, One Order to Many Orderlink, One Item to Many Orderlink. Just wondering if this add more complexity to my original question? and How do I resolve it?

Again, thank for any help.

 

[HiTechCoach]

Thank you HitechCoach, I think I omitted some information here, actually I have many to many relationship, I normailiesed it in following way, One Order to Many Orderlink, One Item to Many Orderlink. Just wondering if this add more complexity to my original question? and How do I resolve it?

Again, thank for any help.

I do not see an issue with those relatoinmhip.

The only issue I see is that you said:

How can I combine one to many relationship item with quantity in to one cell?

Taking data from multiple records and putting it into a single cell can be done. But it does require extra work. See the code I provided in my original reply.

Am I understading correctly what you want to do?

 

[DashDash]

Hi Hitechcoach, thank you for your effort in answering my question. but I think my explaining is not so good, and I finally found something that is what i am trying to do. in this post " Combine multiple rows into one cell " (sorry, cant post link due to my account status)

 

[HiTechCoach]

Is this it: Combine multiple rows into one cell

Curious, did you go the method of creating a temp table?

Note: The link to the solution I posted has the same end results. It just does it in memory without the extra reading and writing to a table. Usually in memory operations will be is a lot faster. The method I recommended only makes one trip to the table to read the data. The method in the other post you found has to read the data, write the data, then read it again. I have not tested it but I would think it would be a lot slower method using all the Disk I/O than in memory I/O.