Sum query

J

jtfalk

I am close but need a little help. I have a tag inventory log database I am
making. I am running a chart and had the following in a query:

Week: "week" & (Format([Issue Date]," WW = MM 'YY")) - by group
Tags: ID - count

This was great - it tool each week and showed how many tags were put in that
week.

Now I want to graph the running total. i added in this and it does do a
running total but now it shows every instance in each week that there was
atag (eg week 39 has 3 instances.) I need it to only show the date by week
like to reference the above Week: "week" & (Format([Issue Date]," WW = MM
'YY")) - by group

RunTotal: DSum("idexists","issues","[issue date]<=#" & [issue date] & "#") -
by group

Any sggestions?
 
J

jtfalk

SELECT "week" & (Format([Issue Date]," WW = MM 'YY")) AS Week,
Count(Issues.ID) AS Tags, DSum("idexists","issues","[issue date]<=#" & [issue
date] & "#") AS RunTotal FROM Issues GROUP BY "week" & (Format([Issue Date],"
WW = MM 'YY")), DSum("idexists","issues","[issue date]<=#" & [issue date] &
"#");

Issues Date is the actual date that the tag was logged in. I need to look at
the week for the sum data and not the days of the week. Here is the output
currently. See how it repeats week 37, 38, and 29 becasue there was data put
in on differnet days even though on the same week.
Week Tags RunTotal
week 31 = 07 '09 9 9
week 32 = 08 '09 14 23
week 36 = 09 '09 3 26
week 37 = 09 '09 5 31
week 37 = 09 '09 5 36
week 38 = 09 '09 5 41
week 38 = 09 '09 2 43
week 38 = 09 '09 1 44
week 39 = 09 '09 1 45
week 39 = 09 '09 9 54
week 40 = 10 '09 2 56
week 41 = 10 '09 20 76
week 42 = 10 '09 1 77

Here it is without the running total:
SELECT "week" & (Format([Issue Date]," WW = MM 'YY")) AS Week,
Count(Issues.ID) AS Tags FROM Issues GROUP BY "week" & (Format([Issue Date],"
WW = MM 'YY"));
Week Tags
week 31 = 07 '09 9
week 32 = 08 '09 14
week 36 = 09 '09 3
week 37 = 09 '09 10
week 38 = 09 '09 8
week 39 = 09 '09 10
week 40 = 10 '09 2
week 41 = 10 '09 20
week 42 = 10 '09 1

Duane Hookom said:
Tell us everything significant about your chart's Row Source.

--
Duane Hookom
Microsoft Access MVP


jtfalk said:
I am close but need a little help. I have a tag inventory log database I am
making. I am running a chart and had the following in a query:

Week: "week" & (Format([Issue Date]," WW = MM 'YY")) - by group
Tags: ID - count

This was great - it tool each week and showed how many tags were put in that
week.

Now I want to graph the running total. i added in this and it does do a
running total but now it shows every instance in each week that there was
atag (eg week 39 has 3 instances.) I need it to only show the date by week
like to reference the above Week: "week" & (Format([Issue Date]," WW = MM
'YY")) - by group

RunTotal: DSum("idexists","issues","[issue date]<=#" & [issue date] & "#") -
by group

Any sggestions?
Click to expand...
Click to expand...
 
D

Duane Hookom

A single week can span more than one month. How do you want to handle this?

--
Duane Hookom
Microsoft Access MVP


jtfalk said:
SELECT "week" & (Format([Issue Date]," WW = MM 'YY")) AS Week,
Count(Issues.ID) AS Tags, DSum("idexists","issues","[issue date]<=#" & [issue
date] & "#") AS RunTotal FROM Issues GROUP BY "week" & (Format([Issue Date],"
WW = MM 'YY")), DSum("idexists","issues","[issue date]<=#" & [issue date] &
"#");

Issues Date is the actual date that the tag was logged in. I need to look at
the week for the sum data and not the days of the week. Here is the output
currently. See how it repeats week 37, 38, and 29 becasue there was data put
in on differnet days even though on the same week.
Week Tags RunTotal
week 31 = 07 '09 9 9
week 32 = 08 '09 14 23
week 36 = 09 '09 3 26
week 37 = 09 '09 5 31
week 37 = 09 '09 5 36
week 38 = 09 '09 5 41
week 38 = 09 '09 2 43
week 38 = 09 '09 1 44
week 39 = 09 '09 1 45
week 39 = 09 '09 9 54
week 40 = 10 '09 2 56
week 41 = 10 '09 20 76
week 42 = 10 '09 1 77

Here it is without the running total:
SELECT "week" & (Format([Issue Date]," WW = MM 'YY")) AS Week,
Count(Issues.ID) AS Tags FROM Issues GROUP BY "week" & (Format([Issue Date],"
WW = MM 'YY"));
Week Tags
week 31 = 07 '09 9
week 32 = 08 '09 14
week 36 = 09 '09 3
week 37 = 09 '09 10
week 38 = 09 '09 8
week 39 = 09 '09 10
week 40 = 10 '09 2
week 41 = 10 '09 20
week 42 = 10 '09 1

Duane Hookom said:
Tell us everything significant about your chart's Row Source.

--
Duane Hookom
Microsoft Access MVP


jtfalk said:
I am close but need a little help. I have a tag inventory log database I am
making. I am running a chart and had the following in a query:

Week: "week" & (Format([Issue Date]," WW = MM 'YY")) - by group
Tags: ID - count

This was great - it tool each week and showed how many tags were put in that
week.

Now I want to graph the running total. i added in this and it does do a
running total but now it shows every instance in each week that there was
atag (eg week 39 has 3 instances.) I need it to only show the date by week
like to reference the above Week: "week" & (Format([Issue Date]," WW = MM
'YY")) - by group

RunTotal: DSum("idexists","issues","[issue date]<=#" & [issue date] & "#") -
by group

Any sggestions?
Click to expand...
Click to expand...
Click to expand...
 
J

jtfalk

I would like to go with the day that starts the week to be the month that it
is in.

Duane Hookom said:
A single week can span more than one month. How do you want to handle this?

--
Duane Hookom
Microsoft Access MVP


jtfalk said:
SELECT "week" & (Format([Issue Date]," WW = MM 'YY")) AS Week,
Count(Issues.ID) AS Tags, DSum("idexists","issues","[issue date]<=#" & [issue
date] & "#") AS RunTotal FROM Issues GROUP BY "week" & (Format([Issue Date],"
WW = MM 'YY")), DSum("idexists","issues","[issue date]<=#" & [issue date] &
"#");

Issues Date is the actual date that the tag was logged in. I need to look at
the week for the sum data and not the days of the week. Here is the output
currently. See how it repeats week 37, 38, and 29 becasue there was data put
in on differnet days even though on the same week.
Week Tags RunTotal
week 31 = 07 '09 9 9
week 32 = 08 '09 14 23
week 36 = 09 '09 3 26
week 37 = 09 '09 5 31
week 37 = 09 '09 5 36
week 38 = 09 '09 5 41
week 38 = 09 '09 2 43
week 38 = 09 '09 1 44
week 39 = 09 '09 1 45
week 39 = 09 '09 9 54
week 40 = 10 '09 2 56
week 41 = 10 '09 20 76
week 42 = 10 '09 1 77

Here it is without the running total:
SELECT "week" & (Format([Issue Date]," WW = MM 'YY")) AS Week,
Count(Issues.ID) AS Tags FROM Issues GROUP BY "week" & (Format([Issue Date],"
WW = MM 'YY"));
Week Tags
week 31 = 07 '09 9
week 32 = 08 '09 14
week 36 = 09 '09 3
week 37 = 09 '09 10
week 38 = 09 '09 8
week 39 = 09 '09 10
week 40 = 10 '09 2
week 41 = 10 '09 20
week 42 = 10 '09 1

Duane Hookom said:
Tell us everything significant about your chart's Row Source.

--
Duane Hookom
Microsoft Access MVP


:

I am close but need a little help. I have a tag inventory log database I am
making. I am running a chart and had the following in a query:

Week: "week" & (Format([Issue Date]," WW = MM 'YY")) - by group
Tags: ID - count

This was great - it tool each week and showed how many tags were put in that
week.

Now I want to graph the running total. i added in this and it does do a
running total but now it shows every instance in each week that there was
atag (eg week 39 has 3 instances.) I need it to only show the date by week
like to reference the above Week: "week" & (Format([Issue Date]," WW = MM
'YY")) - by group

RunTotal: DSum("idexists","issues","[issue date]<=#" & [issue date] & "#") -
by group

Any sggestions?
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

It might be easiest to create, understand, and maintain this with a couple
queries:

If I understand corrrectly, create a query like:
=== qgrpIssuesByWeek =====
SELECT DateAdd("D",-Weekday([Issue Date])+1,[Issue Date]) AS WeekOf,
Count(Issues.ID) AS CountOfID
FROM Issues
GROUP BY DateAdd("D",-Weekday([Issue Date])+1,[Issue Date]);

Then create a query with a running total:
=== qtotIssuesRunTotal ===
SELECT qgrpIssuesByWeek.WeekOf, qgrpIssuesByWeek.CountOfID, (Select
Sum(CountOfID) FROM qgrpIssuesByWeek q WHERE q.WeekOf
<=qgrpIssuesByWeek.WeekOf) AS RunTotal
FROM qgrpIssuesByWeek;



--
Duane Hookom
Microsoft Access MVP


jtfalk said:
I would like to go with the day that starts the week to be the month that it
is in.

Duane Hookom said:
A single week can span more than one month. How do you want to handle this?

--
Duane Hookom
Microsoft Access MVP


jtfalk said:
SELECT "week" & (Format([Issue Date]," WW = MM 'YY")) AS Week,
Count(Issues.ID) AS Tags, DSum("idexists","issues","[issue date]<=#" & [issue
date] & "#") AS RunTotal FROM Issues GROUP BY "week" & (Format([Issue Date],"
WW = MM 'YY")), DSum("idexists","issues","[issue date]<=#" & [issue date] &
"#");

Issues Date is the actual date that the tag was logged in. I need to look at
the week for the sum data and not the days of the week. Here is the output
currently. See how it repeats week 37, 38, and 29 becasue there was data put
in on differnet days even though on the same week.
Week Tags RunTotal
week 31 = 07 '09 9 9
week 32 = 08 '09 14 23
week 36 = 09 '09 3 26
week 37 = 09 '09 5 31
week 37 = 09 '09 5 36
week 38 = 09 '09 5 41
week 38 = 09 '09 2 43
week 38 = 09 '09 1 44
week 39 = 09 '09 1 45
week 39 = 09 '09 9 54
week 40 = 10 '09 2 56
week 41 = 10 '09 20 76
week 42 = 10 '09 1 77

Here it is without the running total:
SELECT "week" & (Format([Issue Date]," WW = MM 'YY")) AS Week,
Count(Issues.ID) AS Tags FROM Issues GROUP BY "week" & (Format([Issue Date],"
WW = MM 'YY"));
Week Tags
week 31 = 07 '09 9
week 32 = 08 '09 14
week 36 = 09 '09 3
week 37 = 09 '09 10
week 38 = 09 '09 8
week 39 = 09 '09 10
week 40 = 10 '09 2
week 41 = 10 '09 20
week 42 = 10 '09 1

:

Tell us everything significant about your chart's Row Source.

--
Duane Hookom
Microsoft Access MVP


:

I am close but need a little help. I have a tag inventory log database I am
making. I am running a chart and had the following in a query:

Week: "week" & (Format([Issue Date]," WW = MM 'YY")) - by group
Tags: ID - count

This was great - it tool each week and showed how many tags were put in that
week.

Now I want to graph the running total. i added in this and it does do a
running total but now it shows every instance in each week that there was
atag (eg week 39 has 3 instances.) I need it to only show the date by week
like to reference the above Week: "week" & (Format([Issue Date]," WW = MM
'YY")) - by group

RunTotal: DSum("idexists","issues","[issue date]<=#" & [issue date] & "#") -
by group

Any sggestions?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUM in a UNION query 2
Crosstab Query by Week with Dates as Headers 5
Cross-tab Criteria not evaluating 19
Date Formats-Weeks in Date No 2
crosstab query? 12
Urgent - Calendar - Need 7 days for week selected 11
convert date to week, starting at financial year 2
Year and Week Totals 2

Top