If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can/How do you create a chart that can uses VBA code?
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 a 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. |
#2
|
|||
|
|||
Can/How do you create a chart that can uses VBA code?
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 "sheriff" wrote: 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 a 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. |
#3
|
|||
|
|||
Can/How do you create a chart that can uses VBA code?
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. "sheriff" wrote in message ... 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 a 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. |
Thread Tools | |
Display Modes | |
|
|