0

I have a business need to export data from Access (2013) into Excel (2010\13) that will then be distributed to x number of users. This will happen on a regular basis. I want to perform as much of the formatting etc in Access and avoid creating macro's in Excel and calling them.

This is my data in Access:

Cat1   Cat2   Cat3   Item  Desc
AAA    PPP    QQQ    WID1  Widget1
AAA    RRR    KKK    WID2  Widget2
AAA    RRR    JJJ    WID3  Widget3
BBB    DDD    EEE    WID4  Widget4
BBB    FFF    UUU    WID5  Widget5

In Excel, the data needs to look like this

AAA
PPP
QQQ
WID1    Widget1
RRR
KKK
WID2    Widget2
JJJ
WID3    Widget3
BBB
DDD
EEE
WID4   Widget4
FFF
UUU
WID5   Widget5

Is there a way to loop through the data in Access, build a temp table to look like the above, then export the temp table to Excel? Or will have have to export the data as it originally is and then call a macro in Excel to perform the formatting?

Regards,

Michael

Gustav
  • 53,498
  • 7
  • 29
  • 55
Michael
  • 2,507
  • 8
  • 35
  • 71
  • 1
    You will have to loop through the records sorted as shown, comparing each record with the previous, and write the result (if any) to a temporary table. Then export the temporary table. – Gustav Apr 03 '17 at 13:29
  • How do I do that? – Michael Apr 03 '17 at 13:44
  • Open the two tables as two Recordsets and then loop the first while adding records to the other. – Gustav Apr 03 '17 at 14:10
  • I don't suppose you have an example - my VBA is quite limited – Michael Apr 03 '17 at 14:14
  • Sort of: [Copy records using DAO](https://stackoverflow.com/questions/36839667/ms-access-insert-into-slow-for-large-recordset-vba/36842264#36842264). The fun part is in the middle where you will have to adjust your conditions for copying a record. – Gustav Apr 03 '17 at 14:19

1 Answers1

1

First off, from your example, it seems that your Access table is not normalized. Should you choose to not correct that, based on your sample data, you might be able to do what you want with a union query. This, of course, depends on how you would actually determine which rows/columns to include.

SELECT Cat1, NULL AS Desc FROM <yourTable> WHERE Cat1='AAA'
UNION ALL
SELECT Cat2, NULL AS Desc FROM <yourTable> WHERE Cat1='PPP'
UNION ALL
SELECT Cat2, NULL AS Desc  FROM <yourTable> WHERE Cat1='QQQ'
UNION ALL
SELECT Item, Desc  FROM <yourTable> WHERE Item='WID1'
UNION ALL ...
AVG
  • 1,317
  • 8
  • 12