Pivot Table/Query

[Cowboy_BeBa]

Hi Guys

Hopefully thisll be an easy one

ive been asked to help prepare an excel spreadsheet for our office, its relatively simple (in theory), just a summary of all our products sold, the quantity sold and total cost, they want it to go according by Month and Year, starting from Jan 2013 and ending at Dec 2014, each month should have a different set of summaries

ive got 24 different excel spreadsheets (one for each month), full of data exported from MYOB, i am currently going through each one and formatting them (shoot me now), after im done with each one im throwing them into an access table i just threw together (the idea is when all the data is done ill be able to create a query thatll format the data exactly the way its required, then i can copy that query to excel, this should be far easier and quicker than manipulating the data and manually calculating the totals.... which is what they were expecting me to do... but i digress)

now my only issue is the format they want the data in

essentially products sold will be going down (each row), then pay periods across and under each period there is total qty and total price (see mock up attached)

Im not too familiar with "pivot" tables, but have a feeling this is what i need, anyone able to point me in the right direction with this?

the access file ive made is just a single table (tblData) and it has the following fields; Date, Description (essentially the name) QTY and Price, ive then added a Period field (which is just a combo box, has 24 lines, one for each month and year, im selecting the correct period as i copy data in from the spreadsheets, figured this would be easier for the query than fiddling with the date)

 

[JHB]

Sorry, but a crosstab query can only return one column, either the "QTY" or the "Price"!
To do what you want involved some code (a lot), where you insert each piece of data in an Excel-sheet, checking for the the right column and row.
It is out of my time scale.

 

[Cowboy_BeBa]

fair enough jhb, i can certainly understand that and wouldnt want anyone to put in too much effort on a job i should be doing
how about 2 crosstab queries? one for price one for qty? i can then manually combine the two in excel when the time comes, perhaps even just do one (qty), i can then look at what youve done, and figure out how to do price for myself

 

[namliam]

Creating a crosstab query isnt that hard....

Simply make a query based on your table
Add the fields you want, description, qty, period
Change the option of the query to a crosstab query, this adds a line in the designer where you simply select "Row header", "Column Header", "Data Element" (exact names can differ.
Also you will want to make totals setting a sum on your qty and group by the other two fields.

Note:
Since your data is in Excel already, why not make a single (big) excel file and create the crosstabs there?
Excel crosstabs are a lot more flexible and more user friendly.

 

[CJ_London]

how about 2 crosstab queries? one for price one for qty?

yes - that is easily doable, but you can combine them in access more easily than excel - simply link the two queries together on the fields designated as rows and columns.


or use a pivot table view of a table, query or form which works in much the same way as excel.

Note this is a view, not like a crosstab where you can save the results for further manipulation or combine with other data. If you export the table/query it will be in the normal datasheet view

To get the pivot view, simply open the table, query or form and select the pivot view from the view button to the left of the ribbon. To make it permanent, change the default view to pivot table.