Solved

Display sales performance with Pivot Table

Posted on 2006-07-10
5
393 Views
Last Modified: 2008-03-06
I would like to create a Pivot Table with MS Access for tracking the sales of my store.
I have a Table with the following fields:
ID (Autonumber)
Date (Date item was sold)
PLU (Item Code - similar to a UPC or SKU)
Quantity (Number sold on this day)
Price (Sell Price of Item)
Cost (Store Cost of Item)
Notes (Text description of Item)

I would like my report to show the sales of items on daily basis in a line graph over a date range I specify (Monthly, then yearly, but the year reports show sums of the monthly sales).

So, PLU 1 will be charted daily across the month, then PLU 2, etc, etc

I have tried playing around with the Pivot Tables, but cannot get the data to to display the way I want. I have also tried exporting the tablet to excel to see if I can get it there, but to no avil there too.

If possible, I would like to have all the PLUs of the month on a single graph, then have each month into it's own report.

If anyone is VB coder friendly, having a form where I can select the criteria, then auto generate a report would be a wonderful feature.

0
Comment
Question by:darklandsinc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17105902
darklandsinc,

<I would like to create a Pivot Table>
<I would like my report to show the sales of items on daily basis in a line graph >

So do you want a Pivot Table or a Pivot Chart?

A Pivot table is designed to summarize more than one field. You cannot "easily" filter them.

However, it seems like a standard Access chart could give you what you are asking for.

Does this have to "Pivot"?

<graph over a date range I specify (Monthly, then yearly, but the year reports show sums of the monthly sales>
Keep it simple...One graph for each. Otherwise it takes a lot of coding to change 1 report into various other reports.

0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 17105953
darklandsinc,

Here are the basic steps to create a simple Chart in Access.

You can play around with it to fit your needs.
Any question let me know.

Charts in Access are confusing, but if you do them enough, they are relatively straightforward.

The Chart will typically be based on the Field you "group by" in the Report.

For example: If you Group Orders by Month, then each Bar in the chart will represent a Month.

If You Group Orders By Employee, then each Bar in the chart will represent an Employee.

I will use the Orders Report in the Northwind sample database for this example. And Chart the Number of Orders per Employee.

Create your Report, Grouped by Employee.
Make the Report Header Section big enough to fit your chart. (at least 3 inches tall)
(Best if Report is landscape)
Click: Insert Chart
Draw in your chart to use as much room in the Report Header as possible.
Select the table/query you want to make your chart out of (Typically this will be the same table/query as the report)
Select EmployeeID and Order ID
Select the basic Column chart
EmployeeID should be at the bottom
Move OrderID from the middle box to the "data" box (It should now change to CountOfOrderID)
Set Both the Report Fields and the Chart fields to: No Fields (Blank)
Name your Chart something like: Count of Orders By Employee
Preview the Report
The chart will most likely be too small
Go back to design view and double-click the middle of the chart.
Stretch the chart to fill the Report Header
Click: File-->Print Preview
Now report will still not look great.
Here is the general rule to modify any chart element:
After double-clicking the chart (While in Design View) you are in "Edit" mode.
You can Right-Click any chart Object and edit it.

Hope this helps.
0
 

Author Comment

by:darklandsinc
ID: 17105977
Boag,
Thanks for your response.
It doesn't have to be pivot. A chart would be nice, but I'm fine if it's a table too.

I will check into the example you gave to see if it works for me. I'm using the Access 2007 Beta, so it will take some time, as they have totally redone the reports wizard.

I'll let you know in a few days if your solution works (It looks like it should though).

Thanks again!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17106087
darklandsinc


Some advice, Don't use 2007 if you don't have to. If something goes wrong you won't know if its a beta bug or user error.

Also at this time not that many "Power users" have switched over. Meaning you won't get much "Expert" advice for a few months.

So when you create your chart there will be somewhere, an option to set the grouping for your dates (Day, Week, Month, Qtr, Year)

Let me know.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17161776
:)
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question