Solved

Display sales performance with Pivot Table

Posted on 2006-07-10
5
378 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
  • 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Combobox issue 4 25
Search field on a form 7 14
Sending Attachment via CDO 3 17
Access on thin client? 11 30
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now