ACCESS FEED: Re: [MS_AccessPros] How to make line graph charts -- OT: Report Missing Data

I'm keeping this in my future reference file :)

Regards,

Shay Buchanan

On Mon, Jul 11, 2011 at 11:06 AM, Crystal <strive4peace2008@yahoo.com>wrote:

> **
>
>
> Hi Shay,
>
> instead of thinking specifically about reporting data for missing months,
> think more generically.
>
> How to show missing numbers:
>
> Make a Numbers table:
>
> Numbers
> - Num, number, long integer, PrimaryKey
>
> to make records in the table, run this code:
>
> '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CreateRecords_NumbersTable
> Sub CreateRecords_NumbersTable()
> '
> ' Crystal strive4peace
> '
> ' use this table to to get all records when data is missing
> ' for instance, to construct particular years, months, days, ...
> ' to print a copies of a report
> ' to find missing numbers
>
> ' click HERE
> ' press F5 to Run
>
> '~~~~~~~~~~~~~~~~~~
> 'NEEDS reference to Microsoft DAO Library
> 'or
> 'Microsoft Office ##.0 Access Database Engine Object Library
> '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> ' tablename --> Numbers
> ' fieldname --> Num
>
> On Error GoTo Proc_Err
>
> Dim db As DAO.Database _
> , r As DAO.Recordset
>
> Dim lngStart As Long _
> , lngStop As Long _
> , i As Long
>
> ' every now and then, you might need to run this again
> ' to change the max number created
> lngStop = 100
>
> Set db = CurrentDb
> Set r = db.OpenRecordset("Numbers", dbOpenDynaset)
>
> 'figure out the last number in the table
> 'and create numbers from there
>
> lngStart = Nz(DMax("Num", "Numbers"), 0) + 1
>
> For i = lngStart To lngStop
> r.AddNew
> r!Num = i
> r.Update
> Next i
>
> MsgBox "Done creating records in Numbers"
>
> Proc_Exit:
> On Error Resume Next
> If Not r Is Nothing Then
> r.Close
> Set r = Nothing
> End If
> Set db = Nothing
> Exit Sub
>
> Proc_Err:
> MsgBox Err.Description, , _
> "ERROR " & Err.Number _
> & " CreateNumbersRecords"
>
> Resume Proc_Exit
> Resume
> End Sub
> '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> if you want to report all months, make a cartesian query (no join -- tables
> are separated with comma) with the Numbers table and your data table. For
> instance, something like this:
>
> SELECT DataTable.*
> , 9999 as Yr
> , Month( (DataTable.DateField) as Mo
> , Numbers.Num as MnthNbr
> FROM
> DataTable, Numbers
> WHERE
> Numbers.Num >=1 and Numbers.Num <=12
> AND Year(DataTable.DateField) = 9999
> OR Year(DataTable.DateField) Is Null
>
> WHERE
> 9999 is the 4-digit year you want
>
> in this case, since the numbers table is only being used to get all months
> for one year, the criteria needs to include year.
>
> If you report on a monthly basis and want to have a 20-year reporting
> period, you could do something like this:
>
> query --> qRptMonths_1995_2015
>
> SELECT (NumbersForYear.Num + 1994) as RptYr
> , (NumbersForMonth.Num ) as RptMo
> FROM
> Numbers as NumbersForYear, Numbers as NumbersForMonth
> WHERE
> NumbersForMonth.Num >=1 and NumbersForMonth.Num <=12
> AND
> NumbersForYear.Num >=1 and NumbersForYear.Num <20
>
> you could make it dynamic by using Year(Date()) and Month(Date()) instead
> of hard-coding the years, which I did to make the logic easier to
> understand.
>
> Now you can do something like this:
>
> SELECT
> DataTable.*, qRpt.RptYr, qRpt.RptMo
> FROM
> DataTable RIGHT JOIN qRptMonths_1995_2015 as qRpt
> ON
> qRpt.RptYr = Year(DataTable.DateField)
> AND
> qRpt.RptMo = Month(DataTable.DateField)
> ORDER BY
> whatever;
>
> DataTable RIGHT JOIN qRptMonths_1995_2015
> means that all records will be displayed from the table on the right, which
> is the cartesian the query
>
> ~~~
> Make the query to get everything you want, which will probably come from
> more than just DataTable. don't worry about the criteria -- just get the
> columns that you want to report. Save this as -->
> q_Descriptive_Name__Source
>
> Then edit the ON clause of the SQL statement to link on year and month
> pulled from the date. Save the query before you do this as you will not be
> able to use the designer on it again. Once you edit the SQL, save the query
> with a different name.
>
> Anytime you want to change it, go to the source query and use the designer.
> Then save the source query again. Then edit the SQL. Then save with a new
> name (maybe just add __Source to the end of the source query and nothing on
> the end of the production query name)
>
> Warm Regards,
> Crystal
>
> Access Basics by Crystal (Bill Mosca's site)
> http://thatlldoit.com
> Free 100-page book that covers essentials in Access
>
> *
> (: have an awesome day :)
> *
>
> --- On Mon, 7/11/11, Shay wrote:
>
> > I'll try the table of months and let
> > you know how that works :)
> >
> > Regards,
> >
> > Shay Buchanan
> >
> >
> > On Sun, Jul 10, 2011 at 11:40 AM, Duane Hookom <duanehookom@hotmail.com
> >wrote:
> >
> > > **
> > >
> > >
> > >
> > > Shay,
> > > It seems your problem occurs when you don't have data
> > to display for all
> > > months yet you want all months to display. If this is
> > the case, create a
> > > query to use as a row source that guarentees
> > displaying of all months. You
> > > can create a simple table of all months and use it in
> > an outer join so all
> > > months are displayed.
> > >
> > > It might help if you provided the SQL view of your row
> > source and tell us
> > > what is missing or wrong with your chart.
> > >
> > > Duane Hookom
> > > MS Access MVP
> > >
> > >
> > >
> > > > To: shay.buchanan@gmail.com
>
> > >
> > > >
> > > > The table i'm pulling from tracks when a document
> > was submitted and when
> > > it
> > > > was processed. I am basing the charts off a query
> > which returns the
> > > > processor, the month of submission, the number of
> > submissions, the
> > > processed
> > > > month & number processed. I have pie charts
> > for the processors that are
> > > > working right, and I'm trying to get an overall
> > picture from the same
> > > query.
> > > >
> > > > The chart I'm having trouble with is supposed to
> > display the number of
> > > > submissions in each month and another chart shows
> > the number of those
> > > > documents processed by month.
> > > >
> > > > So, for instance, I could have documents that
> > start getting submitted in
> > > > January, but processing may not start until
> > March. Ultimately,I want both
> > > > charts to show the same reference points for both
> > axises, with the bottom
> > > > one showing all 12 months. Combining the charts
> > is only an option after
> > > > I've ruled out the separate charts.
> > > >
> > > > The report was originally built in Excel, and i'm
> > comfortable with those
> > > > right-click menus. I can't seem to get the chart
> > wizard to display real
> > > data
> > > > so I went with the pivot charts, and with those I
> > can't get either axis
> > > to
> > > > change reference points via menu options.
> > > >
> > > > Regards,
> > > >
> > > > Shay Buchanan
> > > >
> > > >
> > > > On Fri, Jul 8, 2011 at 7:30 PM, Crystal <strive4peace2008@yahoo.com>
> > > wrote:
> > > >
> > > > > **
> > > > >
> > > > >
> > > > > Hi Shay,
> > > > >
> > > > > the trick to getting exactly what you want
> > on a graph is to modify the
> > > > > RowSource (and everything else!) after the
> > wizard creates it ...
> > > > >
> > > > > sounds like maybe you want an X-Y plot with
> > lines between the markers,
> > > 1-12
> > > > > for X-values and something else for Y? What
> > are you charting?
> > > > >
> > > > > If the x-axis values are month numbers, you
> > can use the Month function
> > > on a
> > > > > date to pull out the month as a number (or
> > Format to get it as a
> > > string)
> > > > >
> > > > > field --> MonthNbr: Month( [datefield])
> > > > >
> > > > > The wizard always wants to aggregate
> > something ... you can see this in
> > > the
> > > > > RowSource it creates, which you modify :)
> > > > >
> > > > > what does a chart need? X and Y values ...
> > sorting ... criteria.
> > > > >
> > > > > Just use the wizard to get the chart object
> > > > >
> > > > > then change the RowSource to be anything you
> > want. If you are
> > > comfortable
> > > > > with charting in Excel, you can use that
> > knowledge to become a master
> > > with
> > > > > Access charts. Right-click is your best
> > friend ;)
> > > > >
> > > > > RowSource -->
> > > > > SELECT x-field, y1-field, y2-field, etc
> > > > > FROM TableOrQuery
> > > > > ORDER BY sort-field
> > > > >
> > > > > WHERE
> > > > > y1-field is the field for the first series
> > > > > y2-field is the field for the second series
> > > > >
> > > > > the wizard always wants to aggregate the
> > data and it is not necessary.
> > > > >
> > > > > Warm Regards,
> > > > > Crystal
> > > > >
> > > > > *
> > > > > (: have an awesome day :)
> > > > > *
> > > > >
> > > > > --- On Fri, 7/8/11, Shay > wrote:
> > > > >
> > > > > > From: Shay
> > > > >
> > > > > >
> > > > > > I want to make my line graph charts
> > display from 1 to 12 on
> > > > > > the bottom
> > > > > > category axis even when I don't have
> > data for that
> > > > > > category. The category
> > > > > > axis is from a month([event date])
> > field in the query.
> > > > > >
> > > > > > I created the line graph as a pivot
> > chart, mainly because I
> > > > > > couldn't get the
> > > > > > chart wizard control to work.
> > > > > >
> > > > > > Any ideas? Anything more I should add?
> > > > > >
> > > > > > Regards,
> > > > > >
> > > > > > Shay Buchanan
> > > > > >
> > > > >
> > > > >
> > > > >
>
>