Creating a Line Chart

[fd1]

Hi all,
I'm trying to build a pivotchart based on a table that has 2 fields: version_number and date_created. How do I create an S curve pivotchart that would show the cumulitive sum of version numbers created as time progresses in weekly interval from min(date_create d) to Max(date_create d)

[ADezii]

Hi all,
I'm trying to build a pivotchart based on a table that has 2 fields: version_number and date_created. How do I create an S curve pivotchart that would show the cumulitive sum of version numbers created as time progresses in weekly interval from min(date_create d) to Max(date_create d)

1. Does each version_number have a corresponding date_created and are both these values sequential? Kindly post some sample data, so that we may get a clearer picture of what you are describing.
2. What does the cumulative sum of Version Numbers for a weekly period indicate?
3. It would probably be easier to create a Table consisting of Version Numbers, weekly intervals contained within a Version, and a Running Version Number Total. The data could then be easily plotted. Is this what you have in mind?

[fd1]

1. Does each version_number have a corresponding date_created and are both these values sequential? Kindly post some sample data, so that we may get a clearer picture of what you are describing.
2. What does the cumulative sum of Version Numbers for a weekly period indicate?
3. It would probably be easier to create a Table consisting of Version Numbers, weekly intervals contained within a Version, and a Running Version Number Total. The data could then be easily plotted. Is this what you have in mind?

ADezii, each [ver_number] is a unique text type field (ex. ABC-123-XYZ) and has a corresponding [date_created] date type field, and the're not sequential.
Cumulative sum of Version Numbers means the total number of [ver_number] created up until that date interval.
I guess what I want is to create a query out of that table that would give me 2 fields: Date field which has weekly interval dates and total number of [ver_number] created up until the corresponding date interval.

[ADezii]

ADezii, each [ver_number] is a unique text type field (ex. ABC-123-XYZ) and has a corresponding [date_created] date type field, and the're not sequential.
Cumulative sum of Version Numbers means the total number of [ver_number] created up until that date interval.
I guess what I want is to create a query out of that table that would give me 2 fields: Date field which has weekly interval dates and total number of [ver_number] created up until the corresponding date interval.

Could you post some sample data, say 12 Records?

[fd1]

Could you post some sample data, say 12 Records?

Here's some data sample:

ver_number: date_created:
------------------ --------------------
FAE-326-TTL 5/2/2007
FAE-451-TTS 5/4/2007
FAM-000-AZS 5/8/2007
CCQ-973-YRE 5/23/2007
DRW-001-ZZA 6/6/2007
MMN-664-EEQ 6/20/2007
FSS-421-XDV 7/12/2007
FSS-344-XDW 7/13/2007
CAQ-016-TSS 7/26/2007
FAE-729-SRN 8/2/2007
WIS-211-TTI 8/9/2007
AJD-489-ROZ 8/16/2007

[ADezii]

Here's some data sample:

ver_number: date_created:
------------------ --------------------
FAE-326-TTL 5/2/2007
FAE-451-TTS 5/4/2007
FAM-000-AZS 5/8/2007
CCQ-973-YRE 5/23/2007
DRW-001-ZZA 6/6/2007
MMN-664-EEQ 6/20/2007
FSS-421-XDV 7/12/2007
FSS-344-XDW 7/13/2007
CAQ-016-TSS 7/26/2007
FAE-729-SRN 8/2/2007
WIS-211-TTI 8/9/2007
AJD-489-ROZ 8/16/2007

Let me play around with it a little and see what I can come up with. In the meantime, someone may have already arrived at a solution. Please be patient since I am very busy at the moment.

[fd1]

Let me play around with it a little and see what I can come up with. In the meantime, someone may have already arrived at a solution. Please be patient since I am very busy at the moment.

Thanks ADezii.
So the query I'm looking for should produce this ouput based on the 12 records sample above:

Total: Date:
------- -------
1 5/2/2007
3 5/9/2007
3 5/16/2007
4 5/23/2007
4 5/30/2007
5 6/6/2007
5 6/13/2007
6 6/20/2007
... and so on

[ADezii]

Thanks ADezii.
So the query I'm looking for should produce this ouput based on the 12 records sample above:

Total: Date:
------- -------
1 5/2/2007
3 5/9/2007
3 5/16/2007
4 5/23/2007
4 5/30/2007
5 6/6/2007
5 6/13/2007
6 6/20/2007
... and so on

I ran my Test Code against the Sample Data which you provided. Before I proceed any further, are these the results you are looking for?
Assumptions:

1. Table Name: tblVersions
   1. Field #1 Name: Ver_Number [TEXT]
   2. Field #2 Name: Date_Created: [DATE]

[CODE=vb]Dim dteStartDate As Date, dteEndDate As Date, intNoWeekInterv als As Integer
Dim intCounter As Integer
Dim arrDates() As Date 'Dimension an Array to hold the Interval Date Values

dteStartDate = DMin("[Date_Created]", "tblVersion s") 'Variable to hold the Start Date
dteEndDate = DMax("[Date_Created]", "tblVersion s") 'Variable to hold the End Date

intNoWeekInterv als = DateDiff("ww", dteStartDate, dteEndDate) 'returns 15
ReDim arrDates(0 To intNoWeekInterv als + 1) 'Redimension the Array, since we now know the proper
'size. Must be intNoWeekInterv als + 2 (1 for Begin Date,
'1 for End Date - the Index at 0 will hold the Begin Date,
'Index at intNoWeekInterv als + 1 will hold the End Date.

arrDates(0) = dteStartDate
For intCounter = 1 To intNoWeekInterv als 'store the Start Date in the 1st element
arrDates(intCou nter) = DateAdd("ww", intCounter, dteStartDate) 'Add sequential weeks to the Start Date (ww),
'1 to intNoWeekInterv als.
If arrDates(intCou nter) > dteEndDate Then Exit For 'eliminates the situation where the last Date in the
'Array is > End Date

Next
arrDates(intNoW eekIntervals + 1) = dteEndDate 'store the End Date in the last element

Debug.Print "Interval Date Total Versions to Date"
Debug.Print "--------------------------------------------"

For intCounter = LBound(arrDates ) To UBound(arrDates )
'Logically, the cumulative number of Versions up to the Interval Date is the total number of Records in
'tblVersions where [Date_Created] <= each Interval Date (weekly). I'm making this assumption since each
'Version is unique and each Version has a corresponding [Date_Created].
Debug.Print Format$(arrDate s(intCounter), "mm/dd/yyyy") & _
" =============== ====> " & Format$(DCount( "*", "tblVersion s", "[Date_Created] <= #" & arrDates(intCou nter) & "#"), "000")
Next[/CODE]
OUTPUT:
[CODE=text]Interval Date Total Versions to Date
--------------------------------------------
05/02/2007 =============== ====> 001
05/09/2007 =============== ====> 003
05/16/2007 =============== ====> 003
05/23/2007 =============== ====> 004
05/30/2007 =============== ====> 004
06/06/2007 =============== ====> 005
06/13/2007 =============== ====> 005
06/20/2007 =============== ====> 006
06/27/2007 =============== ====> 006
07/04/2007 =============== ====> 006
07/11/2007 =============== ====> 006
07/18/2007 =============== ====> 008
07/25/2007 =============== ====> 008
08/01/2007 =============== ====> 009
08/08/2007 =============== ====> 010
08/15/2007 =============== ====> 011
08/16/2007 =============== ====> 012[/CODE]

[fd1]

Thanks ADezii, that's exactly the output I'm looking for.
Now how can I use this code in a query to have the output fed in a line graph.

[ADezii]

Thanks ADezii, that's exactly the output I'm looking for.
Now how can I use this code in a query to have the output fed in a line graph.

I'm going to adapt the code so that it creates a Table consisting of the results. This will give you the most flexibility to do whatever you like.

[ADezii]

Thanks ADezii, that's exactly the output I'm looking for.
Now how can I use this code in a query to have the output fed in a line graph.

1. Create a New Table and name it tblFinalResults . This Table will contain the calculated data. All Records will initially be deleted at the beginning of the code segment, then new data will be added. This Table will only consist of 2 Fields:
   1. [Interval Date] - DATE
   2. [Total Versions to Date] - INTEGER
2. Replace your existing code with this modified code:
   [CODE=vb]
   Dim dteStartDate As Date, dteEndDate As Date, intNoWeekInterv als As Integer
   Dim intCounter As Integer, MyDB As DAO.Database, MyRS As DAO.Recordset
   Dim arrDates() As Date 'Dimension an Array to hold the Interval Date Values
   
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecord set("tblFinalRe sults", dbOpenDynaset)
   
   If DCount("*", "tblFinalResult s") > 0 Then
   'Delete all existing Records from tblFinalResults , if any exist - they may/may not be valid
   DoCmd.SetWarnin gs False
   DoCmd.RunSQL "Delete * From tblFinalResults ;"
   DoCmd.SetWarnin gs True
   End If
   
   dteStartDate = DMin("[Date_Created]", "tblVersion s") 'Variable to hold the Start Date
   dteEndDate = DMax("[Date_Created]", "tblVersion s") 'Variable to hold the End Date
   
   intNoWeekInterv als = DateDiff("ww", dteStartDate, dteEndDate) 'returns 15
   ReDim arrDates(0 To intNoWeekInterv als + 1) 'Redimension the Array, since we now know the proper
   'size. Must be intNoWeekInterv als + 2 (1 for Begin Date,
   '1 for End Date - the Index at 0 will hold the Begin Date,
   'Index at intNoWeekInterv als + 1 will hold the End Date.
   
   arrDates(0) = dteStartDate
   For intCounter = 1 To intNoWeekInterv als 'store the Start Date in the 1st element
   arrDates(intCou nter) = DateAdd("ww", intCounter, dteStartDate) 'Add sequential weeks to the Start Date (ww),
   '1 to intNoWeekInterv als.
   If arrDates(intCou nter) > dteEndDate Then Exit For 'eliminates the situation where the last Date in the
   'Array is > End Date
   
   Next
   arrDates(intNoW eekIntervals + 1) = dteEndDate 'store the End Date in the last element
   
   'Debug.Print "Interval Date Total Versions to Date"
   'Debug.Print "--------------------------------------------"
   
   For intCounter = LBound(arrDates ) To UBound(arrDates )
   'Logically, the cumulative number of Versions up to the Interval Date is the total number of Records in
   'tblVersions where [Date_Created] <= each Interval Date (weekly). I'm making this assumption since each
   'Version is unique and each Version has a corresponding [Date_Created].
   'Debug.Print Format$(arrDate s(intCounter), "mm/dd/yyyy") & _
   '" =============== ====> " & Format$(DCount( "*", "tblVersion s", "[Date_Created] <= #" _
   '& arrDates(intCou nter) & "#"), "000")
   With MyRS
   .AddNew
   ![Interval Date] = Format$(arrDate s(intCounter), "mm/dd/yyyy")
   ![Total Versions to Date] = Format$(DCount( "*", "tblVersion s", "[Date_Created] <= #" _
   & arrDates(intCou nter) & "#"), "000")
   .Update
   End With
   Next
   
   'Cleanup chores
   MyRS.Close
   Set MyRS = Nothing
   Erase arrDates 'release Memory used by the Array
   [/CODE]
3. Execute the code, results will now be contained in tblResults.
4. It is now a simple matter to create a Pivot Chart plotting the Total Versions to Date on the Y-Axis and the Interval Dates on the X-Axis.
5. If there are any questions whatsoever, please feel free to ask.

[fd1]

Thank you ADezii, tried the code and fed it to a line graph and got exactly what I needed. Kudos To you :)

[ADezii]

Thank you ADezii, tried the code and fed it to a line graph and got exactly what I needed. Kudos To you :)

Glad it worked out for you. I could have sworn that there was an easier approach, but none that I could find.

[fd1]

Glad it worked out for you. I could have sworn that there was an easier approach, but none that I could find.

The only glitch I'm seeing when running the code is that in some cases I get duplicate records of dteEndDate at the end of tblFinalRecords if for example dteStartDate="4/13/2007" and dteEndDate="9/7/2007"

[ADezii]

The only glitch I'm seeing when running the code is that in some cases I get duplicate records of dteEndDate at the end of tblFinalRecords if for example dteStartDate="4/13/2007" and dteEndDate="9/7/2007"

Post a sub-set of the data that is causing the Duplicate Records, and I'll look at it as soon as I get a chance.