Search code examples
excelvbams-accessrecordsetrecords

Export records to excel sheet

I've got a table in access looking like this,

Category | Subcategory | Userdate (mm/dd/yyyy) | Color

I want to export this to an excel file where the Categories and Subcategories will be placed in column A and B respectively. However the Colors will be placed by month (Userdate), 12 months meaning Columns from C to N. So what I want to do is place the records from colors in different columns depending on the month (Userdate).

What is the best way to go about doing this? Create a recordset and loop through it? I reckon this will be a bit slow when rows exceed the 40k which is possible.

I could also make the table have Month columns like:

Category | Subcategory | January | February | etc...

So I could just export it just like that but seems to me that's just a bad way of making a table.

Solution

  • it sounds like you want a crosstab query:

    TRANSFORM First(Table1.Colour) AS AColour
SELECT Table1.Category, Table1.Subcategory
FROM Table1
GROUP BY Table1.Category, Table1.Subcategory
PIVOT Format([Userdate],"mm-mmm");

    You can transfer to Excel programmatically with DoCmd.TransferSpreadSheet