Summing Queries?

[Don Sealer]

I have about 40 different defects I'm tracking. I'd like to have all of
these defects totaled individually, both by month and by year. I'd like to
show these results in a report. I know I could create 40 queries (one for
each defect) with just the two fields, count and defect, and sum the count
field but that sounds like a lot of work.
Is there a way I can do this much simpler and quicker?
My database has two fields, one is called "count" (where a number is entered
to count the number of defects). The second field is called "Defect
Description" (where the defect type is entered).
Thanks,
Don..........

[MGFoster]

Don Sealer wrote:

I have about 40 different defects I'm tracking. I'd like to have all of
these defects totaled individually, both by month and by year. I'd like to
show these results in a report. I know I could create 40 queries (one for
each defect) with just the two fields, count and defect, and sum the count
field but that sounds like a lot of work.
Is there a way I can do this much simpler and quicker?
My database has two fields, one is called "count" (where a number is entered
to count the number of defects). The second field is called "Defect
Description" (where the defect type is entered).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You mean your table has 2 columns (aka fields). A database has tables;
tables have columns.

Something like this:

SELECT DefectDescription, SUM(DefectCount) AS Defects
FROM table_name
GROUP BY DefectDescription

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzcbF4echKqOuFEgEQIeawCfcTJ5SqfDRWUjvcIpcBGxEo EjvDMAoNch
N4RrRKNg5lUcDomgY3aFr7AY
=L7Nm
-----END PGP SIGNATURE-----

[Don Sealer]

I understand about Field vs column. With that in mind I need some
additional help.
Actually my database has many columns. Two of them are Defect Count &
Defect Description.
How do I write this query? I've tried several different ways but I keep
getting errors, syntax and such. Here's what I've got but it's not working.
Also how could I sort this by month and then by year?
Thanks again for your help and guidance,
Don...........

SELECT Defect Description, sum(Defect Count) AS defects
FROM [Data Collection Table];
Group by Defect Description
"MGFoster" <[e-mail]> wrote in message
news:vW****************@newsread2.news.pas.earthli nk.net...

Don Sealer wrote:

I have about 40 different defects I'm tracking. I'd like to have all of
these defects totaled individually, both by month and by year. I'd like to show these results in a report. I know I could create 40 queries (one for each defect) with just the two fields, count and defect, and sum the count field but that sounds like a lot of work.
Is there a way I can do this much simpler and quicker?
My database has two fields, one is called "count" (where a number is entered to count the number of defects). The second field is called "Defect
Description" (where the defect type is entered).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You mean your table has 2 columns (aka fields). A database has tables;
tables have columns.

Something like this:

SELECT DefectDescription, SUM(DefectCount) AS Defects
FROM table_name
GROUP BY DefectDescription

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzcbF4echKqOuFEgEQIeawCfcTJ5SqfDRWUjvcIpcBGxEo EjvDMAoNch
N4RrRKNg5lUcDomgY3aFr7AY
=L7Nm
-----END PGP SIGNATURE-----

[Don Sealer]

I got it. I didn't type it correctly. It works very well thank you
In order to sort these by month and year would it be best to create a query
by month and another query by year? Would it be possible to show both of
these in a single report?
Thanks again for your help,
Don..............

"Don Sealer" <vz******@verizon.net> wrote in message
news:yxGZe.1957$%L4.850@trndny02...

I understand about Field vs column. With that in mind I need some
additional help.
Actually my database has many columns. Two of them are Defect Count &
Defect Description.
How do I write this query? I've tried several different ways but I keep
getting errors, syntax and such. Here's what I've got but it's not working. Also how could I sort this by month and then by year?
Thanks again for your help and guidance,
Don...........

SELECT Defect Description, sum(Defect Count) AS defects
FROM [Data Collection Table];
Group by Defect Description
"MGFoster" <[e-mail]> wrote in message
news:vW****************@newsread2.news.pas.earthli nk.net...

Don Sealer wrote:

I have about 40 different defects I'm tracking. I'd like to have all of these defects totaled individually, both by month and by year. I'd

like

to show these results in a report. I know I could create 40 queries (one for each defect) with just the two fields, count and defect, and sum the count field but that sounds like a lot of work.
Is there a way I can do this much simpler and quicker?
My database has two fields, one is called "count" (where a number is entered to count the number of defects). The second field is called "Defect
Description" (where the defect type is entered).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You mean your table has 2 columns (aka fields). A database has tables;
tables have columns.

Something like this:

SELECT DefectDescription, SUM(DefectCount) AS Defects
FROM table_name
GROUP BY DefectDescription

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzcbF4echKqOuFEgEQIeawCfcTJ5SqfDRWUjvcIpcBGxEo EjvDMAoNch
N4RrRKNg5lUcDomgY3aFr7AY
=L7Nm
-----END PGP SIGNATURE-----

[MGFoster]

Don Sealer wrote:

I got it. I didn't type it correctly. It works very well thank you
In order to sort these by month and year would it be best to create a query
by month and another query by year? Would it be possible to show both of
these in a single report?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you have the date value in the same table just add the date to the
query:

SELECT date_column, [Defect Description], SUM([Defect Count]) AS Defects
FROM table_name
GROUP BY date_column, [Defect Description]

The JET engine (the Access database engine) will sort the columns in the
GROUP BY clause in ascending order. If you want a different order add
the ORDER BY clause:

ORDER BY date_column DESC, [Defect Description]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzdI0oechKqOuFEgEQKj+QCcCtx4oOLajP7GpiWb9vxmD2 6gE9MAoO04
6OzSSR1XyQQH1uwFce+wkbDw
=WXKq
-----END PGP SIGNATURE-----

[Don Sealer]

Thanks for your reply. I'm kind of stuck. The first reply you gave me
worked well except it didn't separate the data by month and year. The last
reply gave me results but it listed all records, it didn't group the results
by defect description. It's probably something I'm not doing correctly.
The last suggesting gave me every record with dates. I was really hoping
for a finished result that would list the defects by the current month,
summed, and grouped by the defect description. I would also like to see the
same thing except it would be by the year, summed, and grouped by the defect
description.
I've played with the SQL several different ways to try and get these results
but haven't been able to.
Can you suggest what I might be doing wrong or another approach?
Thanks again,
Don...........
"MGFoster" <[e-mail]> wrote in message
news:sN****************@newsread2.news.pas.earthli nk.net...

Don Sealer wrote:

I got it. I didn't type it correctly. It works very well thank you
In order to sort these by month and year would it be best to create a query by month and another query by year? Would it be possible to show both of these in a single report?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you have the date value in the same table just add the date to the
query:

SELECT date_column, [Defect Description], SUM([Defect Count]) AS Defects
FROM table_name
GROUP BY date_column, [Defect Description]

The JET engine (the Access database engine) will sort the columns in the
GROUP BY clause in ascending order. If you want a different order add
the ORDER BY clause:

ORDER BY date_column DESC, [Defect Description]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzdI0oechKqOuFEgEQKj+QCcCtx4oOLajP7GpiWb9vxmD2 6gE9MAoO04
6OzSSR1XyQQH1uwFce+wkbDw
=WXKq
-----END PGP SIGNATURE-----

[dbahooker]

writing 40 queries isn't that big of a deal.. but you should be able to
reuse some of them

[Jana]

Don:

You should take a look at a crosstab query. I think that will give you
what you're looking for. Use the wizard, it should walk you right
through creating it. It will need to be based on a query that shows
every record without grouping to work properly. So first have a query
like this:

SELECT date_column, [Defect Description], [Defect Count]
FROM table_name

Then use that query for the source of the crosstab query. In the
wizard you'll be able to pick how to group the dates, too. You're
going to want to have the Defect Description as a row heading, the Date
Column as a column heading, pick the desired date grouping, and
Sum([Defect Count]) as the calculated number to display in each
intersection. That should do it!!

Good luck,
Jana

[Don Sealer]

Thanks Jana,
I've never used a crosstab query before. Actually I've never really used
the SQL query function before. I'm learning to do both right now.
You're idea is perfect with a couple of exceptions. Actually I do get the
results I wanted. However I was hoping to get a little more. For instance.
The query does give me each month and a total of all months, so that is
great. If it's possible in my report I'd like to compare the current month
against the rest of the months. Is that possible?
I'd also like to display the names of the defects in the report. Right now
it doesn't do that, can it be done.
Thanks for your help, this cross tab query presents some great options.
I'll just need to understand how it all works so I can use it in for future
apps.
Thanks again,
Don.................

"Jana" <Ba********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Don:

You should take a look at a crosstab query. I think that will give you
what you're looking for. Use the wizard, it should walk you right
through creating it. It will need to be based on a query that shows
every record without grouping to work properly. So first have a query
like this:

SELECT date_column, [Defect Description], [Defect Count]
FROM table_name

Then use that query for the source of the crosstab query. In the
wizard you'll be able to pick how to group the dates, too. You're
going to want to have the Defect Description as a row heading, the Date
Column as a column heading, pick the desired date grouping, and
Sum([Defect Count]) as the calculated number to display in each
intersection. That should do it!!

Good luck,
Jana

[Don Sealer]

Actually I'm getting closer. I've figured out the description problem so
now I'm left with only the problem of how do I get it to report only the
current month and all the other months as year. In the report I choose the
month of Aug (just for practice) and of course the query shows a total for
all months (year). This is great but how can I get just the current month?
If I add some verbiage such as:
WHERE (((Format([Date],"mm/yyyy"))=Format(Date(),"mm/yyyy")));

Would that work?? I'm probably going to try and put it in but I've got a
feeling it will do something negative to what you've already suggested. I
suppose I'd be willing to go into the report each month and change it to
accommodate whatever month we're in.
Not sure verbiage is the correct term but someone will probably tell me what
is correct.
Thanks so much for your help,

Don.............
"Don Sealer" <vz******@verizon.net> wrote in message
news:24_Ze.19895$Fh4.2744@trndny03...

Thanks Jana,
I've never used a crosstab query before. Actually I've never really used
the SQL query function before. I'm learning to do both right now.
You're idea is perfect with a couple of exceptions. Actually I do get the
results I wanted. However I was hoping to get a little more. For instance. The query does give me each month and a total of all months, so that is
great. If it's possible in my report I'd like to compare the current month against the rest of the months. Is that possible?
I'd also like to display the names of the defects in the report. Right now it doesn't do that, can it be done.
Thanks for your help, this cross tab query presents some great options.
I'll just need to understand how it all works so I can use it in for future apps.
Thanks again,
Don.................

"Jana" <Ba********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Don:

You should take a look at a crosstab query. I think that will give you
what you're looking for. Use the wizard, it should walk you right
through creating it. It will need to be based on a query that shows
every record without grouping to work properly. So first have a query
like this:

SELECT date_column, [Defect Description], [Defect Count]
FROM table_name

Then use that query for the source of the crosstab query. In the
wizard you'll be able to pick how to group the dates, too. You're
going to want to have the Defect Description as a row heading, the Date
Column as a column heading, pick the desired date grouping, and
Sum([Defect Count]) as the calculated number to display in each
intersection. That should do it!!

Good luck,
Jana

[Jana]

Hmmm....not EXACTLY sure what you're wanting, but my understanding is
this:
You want to see a total for the current month, and you still want to
see monthly data, but you'd also like to have a separate column
entitled "Total This Month", yes? If that is the case, here's what
you're going to do:

Add another calculated field to your query, make it a sum and a row
heading, and use this expression:
Total This Month: IIF(Format([Defect
DATE],"mm/yyyy")=Format(Date(),"mm/yyyy"),[Defect Count],0)

Everything from 'Total This Month' to '0)' is one line...
This basically does an if then statement for you. The format for this
IIF statement is as follows:
Column Label: IIF(LogicalTest,DoThisIfTrue,OtherwiseDoThis)
Where your LogicalTest is Format([Defect
DATE],"mm/yyyy")=Format(Date(),"mm/yyyy")
if LogicalTest is true (i.e. the mm/yyyy of the defect date matches
the mm/yyyy of the current date)
then return the Defect Count
otherwise, the Defect Count is zero.

When you sum that expression, the IIF will only give a value for the
current month's defects and give a zero for any other month's defects
(in that calculation only). There's probably a more elegant solution,
but it escapes me at this moment.

Good luck! Welcome to the world of Crosstab Queries!!

Jana

[MGFoster]

Jana wrote:

Hmmm....not EXACTLY sure what you're wanting, but my understanding is
this:
You want to see a total for the current month, and you still want to
see monthly data, but you'd also like to have a separate column
entitled "Total This Month", yes? If that is the case, here's what
you're going to do:

Add another calculated field to your query, make it a sum and a row
heading, and use this expression:
Total This Month: IIF(Format([Defect
DATE],"mm/yyyy")=Format(Date(),"mm/yyyy"),[Defect Count],0)

Everything from 'Total This Month' to '0)' is one line...
This basically does an if then statement for you. The format for this
IIF statement is as follows:
Column Label: IIF(LogicalTest,DoThisIfTrue,OtherwiseDoThis)
Where your LogicalTest is Format([Defect
DATE],"mm/yyyy")=Format(Date(),"mm/yyyy")
if LogicalTest is true (i.e. the mm/yyyy of the defect date matches
the mm/yyyy of the current date)
then return the Defect Count
otherwise, the Defect Count is zero.

When you sum that expression, the IIF will only give a value for the
current month's defects and give a zero for any other month's defects
(in that calculation only). There's probably a more elegant solution,
but it escapes me at this moment.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're close Jana. Another way is to use the IIF() [Immediate IF]
function like this in a query:

PARAMETERS StartDate Date, EndDate Date, MonthStart Date;
SELECT [Defect Description],
SUM(IIf(date_column < MonthStart, [Defect Count]) As PriorMonths,
SUM(IIf(date_column >= MonthStart,[Defect Count]) AS ThisMonth
FROM table_name
WHERE date_column BETWEEN StartDate AND EndDate
GROUP BY [Defect Description]

This is a cross-tab query without the TRANSFORM clause.

What the SUM(IIf(...)) expressions do:

SUM(IIf(date_column >= MonthStart,[Defect Count]) AS ThisMonth:

This expression checks if a record's date_column value is on, or
after, the start of the month you want to look at. If so, it will
include that record's Defect Count in the summation of Defect Counts for
that month.

SUM(IIf(date_column < MonthStart, [Defect Count]) As PriorMonths:

This expression checks if the record's date_column value is before,
the desired month. If it is then that record's Defect Count is summed
with the PriorMonths. Remember the earliest limit of dates the query is
looking at is StartDate; therfore, this expression will only sum for
records from the StartDate to before the MonthStart.

You have to put in the correct dates for the StartDate, EndDate and
MonthStart. E.g.: If you wanted YTD for 2005: that'd be

StartDate = 1/1/2005
EndDate = Today's date (Sep 26, 2005)
MonthStart = 9/1/2005 (Sep 1, 2005)

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzi1S4echKqOuFEgEQIilwCgtxyzS5H58JRjQvzwDmVlZu yImcQAnA0G
TVQp+Cq5818o13H/lousXTAT
=5CF4
-----END PGP SIGNATURE-----

[Jana]

MG:

Thanks for the input! Now that I see your answer, I understand what he
was looking for. Knew I wasn't getting it quite right. Very elegant
solution :)

Jana

[Don Sealer]

I thought I sent this reply hours ago but I don't see it. I apologize if
somehow it shows up twice.

I'd like to thank both of you for your help and advice. These last two
replies seem to be over my head. I really don't understand and wouldn't
know where to begin to write the data you've supplied. I'm really in the
dark on most of this. I haven't had any training in Access. My only
resource has been Northwind and the excellent help and support I get from
this group. So thanks but I don't think I can do what your suggesting.

However I have pretty much gotten the result I initially set out to get.
Although I'd prefer not to have to do anything to get the results I'm
willing to make a trade off. That being, in my report, if I go into Design
View and click on the date control I can change it from one month to the
next (this month is Sep so in a few days I'll change that to Oct). It
requires only a little work each month but it will get the results I
wanted.

Thanks ever so much for your patience and time. I hope to someday
understand these things better and possibly be able to take all your
suggestions and put them to work.
Thanks again,
Don.............
"Jana" <Ba********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

MG:

Thanks for the input! Now that I see your answer, I understand what he
was looking for. Knew I wasn't getting it quite right. Very elegant
solution :)

Jana