Link to home
Start Free TrialLog in
Avatar of redpoppy

asked on

Pivot Table or Chart?

Hi Experts,
I am completely ignorant of Pivot Tables & Charts but, from what I've read, I think they may possible meet my requirements but I need some help putting it all together.

The app is a booking system for holidays/vacations and the requirement is to total up the number of travellers (no_trav) based on 2 criteria: the date they booked (book_date) and date of travel (dep_date), with options to view the data by month and week (even if there was no data for that month/week).

So, what I would like to output would be a chart with the 'y' axis showing 'no_trav' and the x-axis showing months or weeks with 2 different coloured bars for each of the date criteria 'book_date' and 'dep_date' depicting the 'no_trav'.

For example, if 100 people booked in Jan, 125 in Feb etc and 75 departed in Jan and 100 in Feb etc, I'd want a scale from 0 to 150 on the 'y' axis, months of the year starting in Jan along the 'x' axis and, say, a purple bar for 'book_date' numbers and a green bar for 'dep_date' numbers.

I hope that makes sense - I'm attaching a sample mock-up that I hope will make it a bit clearer.

All help much appreciated - I don't really know where to start.
Avatar of Markus Fischer
Markus Fischer
Flag of Switzerland image

You want a query showing three columns: months, total bookings, total departures. Once you have that:

* Insert a chart (a regular chart)
* Proceed any way you like until you can press [Finish]
* Put your query as Row Source
* Open the form/report once (to fill in some data)
* Return in design view
* Double-click the chart and adjust

Sample data needed for the Chart:
Jan   100    75
Feb   125   100
For example, based on a table "MonthTable" containing the numbers 1-12, you could use a query similar to this:
SELECT Monthname(MonthNumber),
   (Select Sum(no_trav) From YourTable
    Where book_date
        Between DateSerial(2008, MonthNumber, 1)
        And DateSerial(2008, MonthNumber+1, 0)
   ) As bookings,
   (Select Sum(no_trav) From YourTable
    Where dep_date
        Between DateSerial(2008, MonthNumber, 1)
        And DateSerial(2008, MonthNumber+1, 0)
   ) As departures
FROM MonthTable
ORDER BY MonthNumber

Open in new window

Avatar of redpoppy


Hi harfang, thanks for taking the time to reply - this is definitely along the lines of what I'm looking for.

I've played around with it today and discovered that I can get it working as you suggested, which is great, but I need the facility for the user to enter a range of dates on which the data is calculated.

eg it could be a financial year from 1st July 2007 to 30th June 2008, or a calendar year, or just a few consecutive months.

At the moment I can't see how to adapt the code to include this requirement - would it possible to do that?
For a clean interface, you will need a form, from which your report is launched. On that form, you will have an option group or a combo to choose: weeks or months? You will also have a start date and an end data. Of course, you can suggest appropriate default values (the current year, quarter, etc.).

Then you will change your code to use the values from that form, let's call it fmodParameters, to select the correct report, which in turns will use the dates provided on the same form.

There are other ways to do that, but two different reports for months and weeks seem the easiest to create. It would be more technical to use a single report and have it automatically adjust to the selected granularity.

Did you manage a working query yet? I would assume that's the complicated part in this case...

    If Forms!fmodParameters!grpGranularity = 1 Then   ' 1: months
        strReportName = "rptMonthlyChart"
    Else   ' assume weeks
        strReportName = "rptWeeklyChart"
    End If
    DoCmd.OpenReport strReportName
Query criteria:
        Between Forms!frmParameters!txtStartDate
        And Forms!frmParameters!txtEndDate

Open in new window

I used 2 queries, 1 to select the no of travellers , booking and departure dates, called qry_kpi_no_in_party:

SELECT quotations.Dep_Date, bookings.booking_date, quotations.No_in_Party
FROM quotations INNER JOIN bookings ON quotations.Quote_ID = bookings.Quote_ID;

then another based on your code above, called qry_kpi_chart_no_in_party, after creating a MonthTable as you suggested:

SELECT MonthName([MonthNumber],True) AS Expr1, (Select Sum(no_in_party) From qry_kpi_no_in_party
    Where booking_date
        Between DateSerial(2008, MonthNumber, 1)
        And DateSerial(2008, MonthNumber+1, 0)
   ) AS bookings, (Select Sum(no_in_party) From qry_kpi_no_in_party
    Where dep_date
        Between DateSerial(2008, MonthNumber, 1)
        And DateSerial(2008, MonthNumber+1, 0)
   ) AS departures, MonthTable.MonthNumber
FROM MonthTable
ORDER BY MonthTable.MonthNumber;

I was then able to produce a chart pretty much as I'd originally shown, but I didn't take into account the requirements for the user to enter variable date ranges.

So, I'm not sure of where the date filters would fit in with these queries and how to adjust the chart so that it would show the requested date range.

Thanks again for looking at this - you're help is much appreciated.
Avatar of Markus Fischer
Markus Fischer
Flag of Switzerland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Harfang - that's absolutely brilliant - I'm in total awe! Do you know of any books or links where I can learn a bit more about "advanced querying", it seems very powerful?

Just a couple of quick questions to round things up:

Formatting: I'm displaying the data table at the bottom of the chart and I can't see a way to format the output. For example, it's showing "SumOfDepartures" as one of the data labels - I've tried changing this in MS Graph, but it doesn't seem to save it - when I go back to Access, it's just as it was. I was also trying to format the numbers using currency. In MS Graph, I selected the column, right-clicked, selected "number" and "currency", but again, when I returned to Access, it hadn't been saved. Am I missing something here? I tried setting the formatting in the query, but it still doesn't display the currency sign.

Totalling: Is there a way to total up the data being displayed? For example, the total of "SumOfDepartures" and "SumOfBookings"?

Thanks again for your help - there's no way I would have got anywhere near this.
For books, I always give the same advice: find a large library specialized in computers and ample time. Then start to read, forcing yourself to change books every 10 minutes or so. You will find the one that's best suited for you.

The basics of special queries (not managed by the assistant, like UNION queries) and subqueries (the queries in brackets) are explained in the help file. It's also useful to study the sample database Northwind.mdb to get started.

As for the formatting of your data table, you are right in thinking you should format the data sheet. However, this gets erased each time the chart clears the data sheet to receive fresh data (this looks like a bug). You can adjust it through code, using snippet below as a template, and even change the data cell by cell.

The problem is which event should be used. For reports, it's easy: use the Format event of the section. For forms, you don't get an event that is fired *after* the chart is recalculated. You might need a button [Adjust format], play with the timer event, or find some other trick.

Finally, for the total, the Chart is all that you get. You might devise a way to insert more data, but remove it from the chart through code, or find another similar trick. There is nothing obvious.

Bottom like: forget about the data table. You have the full power of Access at your fingertips, why bother with something inferior. Create your own data table, and display it below, if you must.

Finally, a note for your future questions: once a question has been answered, don't keep it open for "a couple of quick questions". Instead, create a new question and paste the link with: "this is a related follow-up question if you are interested". Actually, in this case, it was three new questions, wasn't it? That way, you get an answer even if I don't have the time, it gives a chance to other experts, who might even have better ideas (say, a way to save the formatting of the data sheet; who knows?).

Private Sub AdjustFormat()
' Adjust formatting of the Chart's DataSheet
    With Me.graWeekly.Object.Application.DataSheet
        .Columns(1).NumberFormat = "d.m.yy"
        .Columns(2).NumberFormat = "#,##0,"
        .Cells(1, 2).Value = "Total:"
    End With
End Sub

Open in new window

Thanks, harfang, I very appreciate your efforts in helping me solve this problem.

With regards to your comments about asking extra questions, I'd like to say that I find it quite difficult to understand the protocol on this site. I pay an annual fee, so really it doesn't make any difference to me how many points the question is worth, therefore I usually offer 500 - maybe that's not the 'correct' way to go about it, but in ignorance it's what I usually do.

As far as this question was concerned, I stated at the outset that I didn't have any knowledge of charts etc, so therefore it's very difficult to be precise about forming the correct question and understanding what value to put on it.

The way I see it, a process was started by me asking the original question and this evolved as you gave your answers. I then thought about the users requirements more in light of your answers, so for me, this would inevitably lead to more questions.

So, would the site protocol be that I should have asked a new question each time some new thoughts/knowledge came to light, or should I have had a lower points value in the first place, then increased it as these events unfolded?
Regarding your grading comments (note to other readers: the Asker posted a long comment asking about the etiquette of this site):

I also have unlimited points, being a qualified expert, so I also tend to give maximal points. Also, when I do ask a question, it's often quite technical. For a quick question like: "for the life of me I can't remember the RunCommand constant to switch to subform datasheet view", I would offer less points, using it as an indicator of complexity. But, having unlimited points, I never hesitate to create a new question while my problem evolves.

I understand also that, as your development progressed, you became more aware of what you wanted to achieve. However, you original question is very precise: you wanted to create a certain type of chart, and were wondering how to proceed, and whether to try it with a Pivot (table or chart) or a regular Chart. I offed the advice to use the regular Chart, and helped you create the query needed. I wouldn't know how to create such a chart using pivoting, but other experts read your question, so it's fair to assume it's not trivial either. This is important: even if there no other answers, many experts read your question; but they did not monitor it for further developments!

Once you had it working, you had problems with the Chart's data table (formatting and totaling), which were not part of your original question. These would make an excellent new question, or perhaps two:

"MS-Graph Chart's data table: how to apply number formats?": exactly as you did, explaining what you tried and how it failed.
[125-250 points, raised to 500 if it's more complex than expected, or directly 500]

"MS-Graph Chart's data table: can I add a total row?": simple question, repeat the title in more words as question body.
[125 question, expecting basically a yes/no answer, again raised if a complex solution evolves, or directly 500]

This could be cross-posted to other zones, although the first is probably a problem only in Access, due to the additional Row Source property. Anyway, these questions would have been read by many experts, some of whom did not even read the first one (because they were not interested in Pivots); and you would get an answer even if I wasn't available for a couple of days.

It's also very simple to post the URL to your new question(s) here, if you want to draw my attention to them, as I already know some of the background.

This all works out to your benefit.

For me, I would have the gratification of a close question and a "thank you", and I would feel free whether I wanted to follow-up (read your next question) or not (perhaps I'm good with queries, but not so much with MS-Graph). For the site, the two new question -- once answered -- would be better PAQs, with a proper title, more to the point, and easier to find using search engines. Finally, other experts could monitor them because they don't know the answer, and are interested to see if there is one.

Please understand I was only trying to offer some helpful advice.

Harfang - I very much appreciate that you were trying to offer helpful advice and thank you for taking the time to do that. Because of the fact that I don't understand the protocol, I'm trying to tread carefully, so as not to upset anyone -  it sounded to me as though you felt "short-changed" by the fact that I asked more questions and I apologise if that was the case. To re-state my point - it was from ignorance of the system, not an attempt to get "free" information from you.

From my perspective, it's quite difficult to understand what's required of me as someone asking a question -  I've looked again at the guidelines for asking questions and it doesn't touch on any of the issues that have been raised here - I think it would be helpful if some of your guidelines above could be incorporated, so that the system becomes easier to comprehend for those of us who are fairly new to the site.

If I could grade questions, you would get an A+. Your question was well formed, you read the answers, I could feed the work you did between comments, you gave ample feedback, you are civil, your enthusiasm is apparent, and you expressed gratitude liberally. What more to ask?

Furthermore, you raise a valid point: I could not find any guidelines for follow-up questions. So I created a feedback question about this {http:/Q_23242110.html}. Feel free to participate if you are interested.