Hi Shay,
I was wondering if you were going to see this one ;) you're welcome :)
Warm Regards,
Crystal
*
(: have an awesome day :)
*
--- On Tue, 7/12/11, Shay wrote:
> I'm keeping this in my future
> reference file :)
>
> Regards,
>
> Shay Buchanan
>
>
> Crystal 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
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
Tidak ada komentar:
Posting Komentar