Group Table Data by Week

[Timothy.Rybak]

Hello all,

I have a simple table that has 3 fields: Date, Error Code, and Number
of Errors.

Every day a user logs in and enter the error data from the day before.

What I need is a query that will sum the Number of Errors field by
calendar week. Basically, the output would need to look like:

Error Code CW1 CW2 CW3 CW4 ...
EC1 5 4 2 2
EC2 3 4 1 1

 

[Duane Hookom]

I would create a crosstab with a column heading expression of:
ColHead: "CW" & DateDiff("ww",[Date],Date())

In the future consider using a field name other than Date since date is also
a function name.

 

[Timothy.Rybak]

Thanks for the help Duane.

Is there a way to define the start date? In other words, I want to to
start counting the calendar weeks from January 1st.

Thansk!
Z

 

[Timothy.Rybak]

Duane,

Ok, I got it to work, more or less. I don't think that going by
Calendar weeks is going to cut it. So, if I can supply a start date
and and end date from a calendar picker - field names cStartDate and
cEndDate - can I generate the same query, but for column headers have
the date the week starts on? For example, run the Query from Jan 1 and
get:

Error Code 1/1/06 1/8/06 1/15/06 1/22/06 ...
EC1 5 4 2 2
EC2 3 4 1 1

 

[Duane Hookom]

Will the number of weeks vary?
Are the results going to a report?
What's your latest SQL view that is working?
Apparently you have a form with a couple controls. Can you provide the
names?

 

[Timothy.Rybak]

Duane,

Again, I really appreciate the help.

1. The number of weeks will need to be variable, as I have no way of
knowing what the user's will want. This should be decided with a user
input like "Type the number of weeks"

2. Ultimately, the results should be going to a report, but for now I
would settle for just getting the query to work, as the user can export
the data to Excel from the query and play with it there.

3. Currently, I have the following in the crosstab query:

Field: Error
Table: tMain
Total: Group By
Crosstab: Row Heading

Field: Expr1: "CW" & DateDiff("ww",[tMain]![Date],[Forms]![fMain Top 5
Defects]![cEndDate])
Total: Group By
Crosstab: Column Heading

Field: Qty of Errors
Table: tMain
Total: Sum
Crosstab: Value

This results in a query that gives the following result:

Error Code CW0 CW1 CW10 CW11 CW2 ...
EC1 5 4 2 2 3
EC2 3 4 1 1 5

 

[Duane Hookom]

I wouldn't necessarily give them free choice on the number of columns. I
would maybe design a 10 column report and a 15 column report or whatever. To
find a solution, search Google Groups on

relative dates mth0 group:*Access.Reports* author:hookom

 

[Timothy.Rybak]

Ok, I think I am making progress. Here is what I have in my Query now
for the Column Heading:

Expr1: "Week " & DateDiff("ww",[tMain]![date],[Forms]![fMain Top 5
Defects]![cEndDate])

This gives me a weekly summary of every week from the start of the data
until the end date specified by the user.

Now, I need to create a report from this data that will only display,
say, the last 10 weeks from the end date selected by the user.

I have the start of this. I can get the data, but I can only get it by
field selecting the individual week fields from the drop down selector,
which makes them static.

How can I either: 1) Limit the query to showing only 10 weeks worth of
data, or 2) Limit the report ot showing the last 10 weeks of data,
without hard coding any dates into the report?

Thanks!
Tim

 

[Timothy.Rybak]

Nevermind, I figured it out. I was thinking that the last numerical
week, say week 13, was the most current week. This is backwards, Week
0 is actually the most current week, so I set the report fields to Week
0, 1, 2, 3, 4 and now it works great!

Tim

 

[Timothy.Rybak]

OK, Now I have a different problem.

In my query, the column names show up as CW0, CW1, CW2, CW3, CW4, CW5,
etc.

In my report, the column names show up as (from today) CW5, CW4, CW3,
CW2, CW1, CW53 (from last year).

The problem is that when the data stretches to last year, it craps out
with an error about the jet engine doesn't recognize ".

How can I make it so that when the data stretches to last year that the
column name and the query data match up?

 

[Duane Hookom]

I don't understand how you get CW53 from "Week " &
DateDiff("ww",[tMain]![date],[Forms]![fMain Top 5 Defects]![cEndDate]). You
can limit the records returned by setting the Column Headings property or
add a criteria against the Date field.