MS Access fields as columns

Question

How can you make a query in MS Access, so that the fields are grouped into columns?

It is easier to explain through an example.

Table:

        Brand   Quantity    Date
1.      MTZ     3           2012.03
2.      MTZ     1           2012.03
3.      Belor.  2           2012.04
4.      YTO     2           2012.04
5.      YTO     1           2013.03

I want a query like this:

Brand   2012.03     2012.04     2013.03
MTZ     4           0           0
Belor.  0           2           0
YTO     0           2           1

Use a PIVOT: http://office.microsoft.com/en-ca/access-help/designing-your-first-pivottable-pivotchart-views-in-access-HA001034580.aspx — Alexandre Santos, Aug 26 '14 at 06:34

score 1 · Accepted Answer · answered Aug 26 '14 at 07:01

1

Try like this

TRANSFORM Sum(tabl1.[Quantity]) AS SumOfQuantity
SELECT tabl1.[brand], Sum( tabl1.[Quantity]) AS [Total Of Quantity]
FROM tabl1
GROUP BY tabl1.[brand]
PIVOT tabl1.[Date];

answered Aug 26 '14 at 07:01

Sathish

4,419
4
30
59

score 0 · Answer 2 · answered Aug 26 '14 at 07:10

Or you can use 'Oleddbreader' for reading each record and formating it the way you want

command.excutereader for excuting query fill data in reader and use loop to print data the way you want, you can use column by column name you sum the numeric data, you can alot of tricks with Reader Command

MS Access fields as columns

2 Answers2

Linked

Related