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 DefectDescripti on, SUM(DefectCount ) AS Defects
FROM table_name
GROUP BY DefectDescripti on

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

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

iQA/AwUBQzcbF4echKq OuFEgEQIeawCfcT J5SqfDRWUjvcIpc BGxEoEjvDMAoNch
N4RrRKNg5lUcDom gY3aFr7AY
=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******** ********@newsre ad2.news.pas.ea rthlink.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 DefectDescripti on, SUM(DefectCount ) AS Defects
FROM table_name
GROUP BY DefectDescripti on

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

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

iQA/AwUBQzcbF4echKq OuFEgEQIeawCfcT J5SqfDRWUjvcIpc BGxEoEjvDMAoNch
N4RrRKNg5lUcDom gY3aFr7AY
=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******@veriz on.net> wrote in message
news:yxGZe.1957 $%L4.850@trndny 02...

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******** ********@newsre ad2.news.pas.ea rthlink.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 DefectDescripti on, SUM(DefectCount ) AS Defects
FROM table_name
GROUP BY DefectDescripti on

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

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

iQA/AwUBQzcbF4echKq OuFEgEQIeawCfcT J5SqfDRWUjvcIpc BGxEoEjvDMAoNch
N4RrRKNg5lUcDom gY3aFr7AY
=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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQzdI0oechKq OuFEgEQKj+QCcCt x4oOLajP7GpiWb9 vxmD26gE9MAoO04
6OzSSR1XyQQH1uw Fce+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******** ********@newsre ad2.news.pas.ea rthlink.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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQzdI0oechKq OuFEgEQKj+QCcCt x4oOLajP7GpiWb9 vxmD26gE9MAoO04
6OzSSR1XyQQH1uw Fce+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********@gma il.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.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******@veriz on.net> wrote in message
news:24_Ze.1989 5$Fh4.2744@trnd ny03...

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********@gma il.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.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