Can/How do you create a chart that can uses VBA code?

[Guest]

I am having difficulties duplicating two of the graphs/charts in Microsoft
Access 2003 that were created in Minitab. The "Chart of Month, Number" and
the "Chart of Age, Region". They are both "stocked column" graph available in
Access but not easy to make.

The database tracks quality issues from medicines around the world made by
the Company in different counties. I have 24 fields for the main table
(Issues2) in my Access Database and the fields relevant to the graph are:


1. ID
2. Date Of Notification: date management was notified of issue
3. Date Issue Observed: date issue was first observed (not really used in
graphs)
4. Date Closed: date issue was closed
5. Current Status: status of issue--Open, Closed, New
6. Region: USA/Canada, Asia/Pacific, EU/Africa/Middle East, Latin America
7. Age: number of days open since Date of Notification--
**=DateDiff("d",[DateOfNotification],Date())**

*****"Chart of Age, Region"*****Its Age (x-axis) vs. Count-by Region
(Y-axis), Legend: Region
Fields that would be needed: 2, 4, 5, 6, 7 above How would I make the "Chart
of Age, Region". I am having a issue making the X-axis be in ranges (Closed,

60 days, 30-60 days, <30 days). How would I change the axis to group issues

by Age and the Count would be number of issues by Region.

*****"Chart of Month, Number"*****Its Month/Year (x-axis) vs.
Count--investigations open/new/closed (Y-axis), Legend=status--open/new/closed
Fields that would be needed: 2, 4, 5 above How would I make the "Chart of
Month, Number" given that when an issue is "open", its "open" month/year is
from the Date of Notification month/year. It is open until a date is entered
in the "Date Closed" field and the status switches to "Closed". An "open"
issue is reported in each month on the chart until it is "closed". In the
month that it is "closed" it is only counted as "closed" and not "open" (this
is obvious). Once an issue is "closed" it is no longer reported as "closed"
in the following months (so for example an issue "closed" in Mar 2007, will
not be counted as "closed" in April 2007 or any month after April, it is just
reported closed in one Month: Mar 2007).

Is it possible to make these graphs within Microsoft Access 2003? Thanks for
any help you can provide.

 

[Guest]

If you can display your information in a query, you can graph it. If you need
to group ranges of records, you should create a range table or a calculation.

Regarding your second graph, I think you need to first normalize your data
with a union query and then create a crosstab query for your graph Row
Source. For instance to normalize the Orders table dates you could create a
query with SQL of:
SELECT Orders.OrderID, Orders.OrderDate AS TheDate, "Order" AS EventType
FROM Orders
UNION ALL
SELECT Orders.OrderID, Orders.RequiredDate , "Required"
FROM Orders
WHERE RequiredDate is not Null
UNION ALL
SELECT Orders.OrderID, Orders.ShippedDate , "Shipped"
FROM Orders
WHERE ShippedDate is not Null;

Then create a crosstab for a row source like:
TRANSFORM Count(quniOrderDates.OrderID) AS CountOfOrderID
SELECT Format([TheDate],"yyyy-mm") AS YrMth
FROM quniOrderDates
GROUP BY Format([TheDate],"yyyy-mm")
PIVOT quniOrderDates.EventType;


--
Duane Hookom
Microsoft Access MVP

I am having difficulties duplicating two of the graphs/charts in Microsoft
Access 2003 that were created in Minitab. The "Chart of Month, Number" and
the "Chart of Age, Region". They are both "stocked column" graph available in
Access but not easy to make.

The database tracks quality issues from medicines around the world made by
the Company in different counties. I have 24 fields for the main table
(Issues2) in my Access Database and the fields relevant to the graph are:


1. ID
2. Date Of Notification: date management was notified of issue
3. Date Issue Observed: date issue was first observed (not really used in
graphs)
4. Date Closed: date issue was closed
5. Current Status: status of issue--Open, Closed, New
6. Region: USA/Canada, Asia/Pacific, EU/Africa/Middle East, Latin America
7. Age: number of days open since Date of Notification--
**=DateDiff("d",[DateOfNotification],Date())**

*****"Chart of Age, Region"*****Its Age (x-axis) vs. Count-by Region
(Y-axis), Legend: Region
Fields that would be needed: 2, 4, 5, 6, 7 above How would I make the "Chart
of Age, Region". I am having a issue making the X-axis be in ranges (Closed,

60 days, 30-60 days, <30 days). How would I change the axis to group issues

by Age and the Count would be number of issues by Region.

*****"Chart of Month, Number"*****Its Month/Year (x-axis) vs.
Count--investigations open/new/closed (Y-axis), Legend=status--open/new/closed
Fields that would be needed: 2, 4, 5 above How would I make the "Chart of
Month, Number" given that when an issue is "open", its "open" month/year is
from the Date of Notification month/year. It is open until a date is entered
in the "Date Closed" field and the status switches to "Closed". An "open"
issue is reported in each month on the chart until it is "closed". In the
month that it is "closed" it is only counted as "closed" and not "open" (this
is obvious). Once an issue is "closed" it is no longer reported as "closed"
in the following months (so for example an issue "closed" in Mar 2007, will
not be counted as "closed" in April 2007 or any month after April, it is just
reported closed in one Month: Mar 2007).

Is it possible to make these graphs within Microsoft Access 2003? Thanks for
any help you can provide.

 

[Pat Hartman \(MVP\)]

Once your data is properly structured to make the graph, you can use VBA to
tweak the settings. The Access wizard is less flexible than the Excel
wizard so if I can't figure out how to do something in Access, I create the
same chart in Excel with the macro recorder turned on. Then I take the code
generated by the macro recorder and pick out what I need and do the final
touch up with VBA after the chart is created.

I am having difficulties duplicating two of the graphs/charts in Microsoft
Access 2003 that were created in Minitab. The "Chart of Month, Number" and
the "Chart of Age, Region". They are both "stocked column" graph available
in
Access but not easy to make.

The database tracks quality issues from medicines around the world made by
the Company in different counties. I have 24 fields for the main table
(Issues2) in my Access Database and the fields relevant to the graph are:


1. ID
2. Date Of Notification: date management was notified of issue
3. Date Issue Observed: date issue was first observed (not really used in
graphs)
4. Date Closed: date issue was closed
5. Current Status: status of issue--Open, Closed, New
6. Region: USA/Canada, Asia/Pacific, EU/Africa/Middle East, Latin America
7. Age: number of days open since Date of Notification--
**=DateDiff("d",[DateOfNotification],Date())**

*****"Chart of Age, Region"*****Its Age (x-axis) vs. Count-by Region
(Y-axis), Legend: Region
Fields that would be needed: 2, 4, 5, 6, 7 above How would I make the
"Chart
of Age, Region". I am having a issue making the X-axis be in ranges
(Closed,

60 days, 30-60 days, <30 days). How would I change the axis to group
issues

by Age and the Count would be number of issues by Region.

*****"Chart of Month, Number"*****Its Month/Year (x-axis) vs.
Count--investigations open/new/closed (Y-axis),
Legend=status--open/new/closed
Fields that would be needed: 2, 4, 5 above How would I make the "Chart of
Month, Number" given that when an issue is "open", its "open" month/year
is
from the Date of Notification month/year. It is open until a date is
entered
in the "Date Closed" field and the status switches to "Closed". An "open"
issue is reported in each month on the chart until it is "closed". In the
month that it is "closed" it is only counted as "closed" and not "open"
(this
is obvious). Once an issue is "closed" it is no longer reported as
"closed"
in the following months (so for example an issue "closed" in Mar 2007,
will
not be counted as "closed" in April 2007 or any month after April, it is
just
reported closed in one Month: Mar 2007).

Is it possible to make these graphs within Microsoft Access 2003? Thanks
for
any help you can provide.