• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

Display sales performance with Pivot Table

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
darklandsinc
Asked:
darklandsinc
  • 4
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
darklandsincAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now