Spreading data out via Pivot Table in Access VBA

Question

Consider a one-to-many relationship like this:

tblStuff
    - id

tblEntry
    - stuff_id
    - the_date
    - the_value

With some data like this:

stuff_id    the_date      the_value
--------    --------      ---------
42          01-Jan-1920   14
42          08-Jan-1920   12
42          22-Jan-1920   7
99          15-Mar-1920   18
99          22-Mar-1920   17

Note that for Stuff #42 there are 2 consecutive entries (1st and 8th Jan), then a week is missed, then another entry.

I need to display each Stuff with its entries spread across a specific number of columns, spread out by the_date. For example:

I need to somehow display this like so:

id    week1    week2    week3    week4    week5
--    -----    -----    -----    -----    -----
42    14       12                7
99    18       17

Is this possible? The columns represent the number of weeks since the first Entry, which may be different for each Stuff. I'm not familiar with pivoting data like this - where should I begin?

Any help very much appreciated.

EDIT

There is going to be exactly 194 "week" columns. I'm unable to redesign the database, this is what I have to work with.

Answer 1

Consider building a Weekly Number table which holds each stuff_id weeks starting from first through the 194th week. You can build this in VBA using action queries with passed in parameters in a loop:

Public Sub WeekNumberTable()
    Dim db As Database
    Dim qdef As QueryDef
    Dim strSQL As String
    Dim i As Integer

    Set db = CurrentDb

    ' MAKE TABLE TO INITIATE FIRST WEEK
    strSQL = "SELECT stuff_ID, Min(the_date) As entry_date,  'week1' As week_number" _
         & " INTO tblEntryWeekNumber" _
         & " FROM tblEntry" _
         & " GROUP BY stuff_ID"

    db.Execute strSQL, dbFailOnError

    ' PREPARE SQL STRING
    strSQL = "PARAMETERS DayAdd Long, WeekNumber Text(255);" _
        & " INSERT INTO tblEntryWeekNumber (stuff_ID, entry_date, week_number)" _
        & " SELECT stuff_ID, Min(the_date) + [DayAdd], [WeekNumber] As week_number" _
        & " FROM tblEntry" _
        & " GROUP BY stuff_ID"

    ' LOOP THROUGH ALL WEEKS ITERATIVELY FOR EACH STUFF_ID
    For i = 1 To 194
       Set qdef = db.CreateQueryDef("", strSQL)

       qdef!DayAdd = 7 * i
       qdef!WeekNumber = "week" & i
       qdef.Execute , dbFailOnError
    Next i

    Set qdef = Nothing 
    Set db = Nothing
End Sub

Once this tabe is build, left join it to original tblEntry data and apply a crosstab query:

TRANSFORM Sum(t.the_value) AS SumOfthe_value
SELECT w.stuff_id
FROM tblEntryWeekNumber w
LEFT JOIN  tblEntry t ON (w.stuff_id = t.stuff_ID) 
AND (w.entry_date = t.the_date)
GROUP BY w.stuff_id
PIVOT w.week_number;

Or in Crosstab Query Design View after left joining tables on stuff_id and the_date/entry_date:

   Field: stuffId           the_value      weekNumber
   Table: tblWeekNumber     tblEntry       tblWeekNumber
   Total: Group By          Sum            Group By
Crosstab: Row Heading       Value          Column Heading
    Sort:
Criteria:
      or:

Output

stuff_id    week1   week2   week3   week4   week5   week6
42             14      12               7       
99             18      17       

Answer 2

No need for VBA for this. You need a Crosstab query.

First create a base query from your table, that uses DatePart("ww", ...) to calculate the week number, and concatenate it with "week".
Be sure to use the correct arguments for firstdayofweek and firstweekofyear, depending on your country. In the query you must use the numbers, the constants aren't available there.

If you want 194 week columns, you will also have to add the year to that string.

Then run the crosstab wizard on that query.

In the resulting query, switch to SQL view. There you can add

PIVOT week_string IN ("week1", "week2", ...)

to get all week numbers, even if there is no data for some weeks.
See TRANSFORM statement

Answer 3

It sounds like you need a cross tab query.

I've taken these instructions from the Office support site (link above). This method uses the query wizard. Don't worry, it's not as complex as the long instructions would suggest!

On the Create tab, in the Other group, click Query Wizard.

In the New Query dialog box, click Crosstab Query Wizard, and then click OK.

The Crosstab Query Wizard starts.

On the first page of the wizard, choose the table or query that you want to use to create a crosstab query.

On the next page, choose the field that contains the values that you want to use as row headings.

You can select up to three fields to use as row headings sources, but the fewer row headings you use, the easier your crosstab datasheet will be to read.

If you choose more than one field to supply row headings, the order in which you choose the fields determines the default order in which your results are sorted.

On the next page, choose the field that contains the values that you want to use as column headings.

In general, you should choose a field that contains few values, to help keep your results easy to read. For example, using a field that has only a few possible values (such as gender) might be preferable to using a field that can contain many different values (such as age).

If the field that you choose to use for column headings has the Date/Time data type, the wizard adds a step that lets you specify how to group the dates into intervals, such as months or quarters.

If you choose a Date/Time field for column headings, the next page of the wizard asks you to specify the interval to use to group the dates. You can specify Year, Quarter, Month, Date, or Date/Time. If you do not choose a Date/Time field for column headings, the wizard skips this page.

On the next page, choose a field and a function to use to calculate summary values. The data type of the field that you select determines which functions are available.

On the same page, select or clear the Yes, include row sums check box to include or exclude row sums.

If you include row sums, the crosstab query has an additional row heading that uses the same field and function as the field value. Including a row sum inserts an additional column that summarizes the remaining columns. For example, if your crosstab query calculates average age by location and gender (with gender column headings), the additional column calculates the average age by location, across all genders.

You can change the function that is used to produce row sums by editing the crosstab query in Design view.

On the next page of the wizard, type a name for your query and then specify whether you want to view the results or modify the query design.

You can also manually create these queries using the designer, although I would recommend you use the wizard the first few times.