Senin, 11 Juli 2011

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

 

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
> > > > >
> > > >
> > > >
> > > >

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar