sql to create totals

[TonyWilliams via AccessMonster.com]

I have a query that counts the number of records where the criteria has
defined values. This produces a list of the number of records for each month
based on a field txtmonth . Here is the sql and the results.

SELECT tblhvdealspt1.txtpurpose, Count(tblhvdealspt1.ID) AS CountOfID,
tblhvdealspt1.txtmonth
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtpurpose)="Buyout" Or (tblhvdealspt1.txtpurpose)
="Refinancing" Or (tblhvdealspt1.txtpurpose)="Corporate Finance" Or
(tblhvdealspt1.txtpurpose)="Restructuring"))
GROUP BY tblhvdealspt1.txtpurpose, tblhvdealspt1.txtmonth;

Purpose CountOfID Month
Buyout 3 June 2009
Buyout 3 December 2009
Corporate Finance 10 June 2009
Corporate Finance 1 December 2009
Refinancing 12 June 2009
Refinancing 2 December 2009
Refinancing 1 January 2010
Restructuring 1 June 2009

What I now want to do is develop this SQL to give me totals for the year so
that the result would be this:

Purpose CountOfID CountOfID
2009 2010
Buyout 6 0
Corporate Finance 11 0
Refinancing 14 0
Refinancing 1 1
Restructuring 1 0

I will then need to produce a form where the user can select which year they
want to report on.

Can anyone help point me in the right direction?
Thanks
Tony

 

[TonyWilliams via AccessMonster.com]

Sorry guys all the columns looked lined up when I posted? Hope you can follow
it!
Tony

 

[Duane Hookom]

Is month actually a text/string field or is it a date field? Storing the
month name is generally a bad idea since it doesn't allow you to use date or
other functions.

Have you considered using a simple crosstab query that has Purpose as the
Row Heading, the Year as the Column Heading, and Sum(CountOfID) as the value?

 

[TonyWilliams via AccessMonster.com]

Hi Duane, battling with another problem now but I'm learning how to think
before I go headlong into things like I did in the report problem!

The month is a date field and holds the dates as say 01/06/09 but controls
are formatted to show mmmm yyyy as the day of the month isn't significant.

I hadn't thought of the cross tab query so I'll have a look at that thanks.
Regards
tony

Is month actually a text/string field or is it a date field? Storing the
month name is generally a bad idea since it doesn't allow you to use date or
other functions.

Have you considered using a simple crosstab query that has Purpose as the
Row Heading, the Year as the Column Heading, and Sum(CountOfID) as the value?

I have a query that counts the number of records where the criteria has
defined values. This produces a list of the number of records for each month

[quoted text clipped - 35 lines]

Thanks
Tony

 

[Duane Hookom]

Tony,
Can you see how confusing your question is when:
- your field name begins with "txt" which usually means text
yet your field type is actually a date
- your field name ends with "month" when actually it's a date
- Your sample data supports the assumption you have a text field
- you didn't tell us the field was a date field even though
you wanted to group it by year

You would get better answers faster if you provided better background and
specifications. Then you would have more time for "battling with another
problem" and so would we ;-)
--
Duane Hookom
Microsoft Access MVP

Hi Duane, battling with another problem now but I'm learning how to think
before I go headlong into things like I did in the report problem!

The month is a date field and holds the dates as say 01/06/09 but controls
are formatted to show mmmm yyyy as the day of the month isn't significant.

I hadn't thought of the cross tab query so I'll have a look at that thanks.
Regards
tony

Is month actually a text/string field or is it a date field? Storing the
month name is generally a bad idea since it doesn't allow you to use date or
other functions.

Have you considered using a simple crosstab query that has Purpose as the
Row Heading, the Year as the Column Heading, and Sum(CountOfID) as the value?

I have a query that counts the number of records where the criteria has
defined values. This produces a list of the number of records for each month

[quoted text clipped - 35 lines]

Thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!




.

 

[TonyWilliams via AccessMonster.com]

Duane, I apologise - that's very sound advice. I am not a IT proffessional
and now that I am retired (at 65) I started to look at building databases
just to keep the grey cells active. Initially it was a membership database
for my local tennis club and as I've enjoyed it more, I've offered to do far
more complex (well I think they are) projects for friends. I therefore
haven't gone through the formal training of many of the people who post on
here and it obviously shows. But I'm keen to learn.

On the naming issue I do get confused in that many books I have read and
comments I've seen on newsgroups such as this often talk about the
standardisation of a naming convention txt,cmb etc and yet when I look at
Microsoft's example database they seem to completely abandon that. I do
wonder whether for simplicity I should just choose field names that are self
evident even though they may be unnecessarily long, but that's another issue.

Anyway, thanks again for your pointers and I'll endeavour to make my future
posts clearer.

Tony

Tony,
Can you see how confusing your question is when:
- your field name begins with "txt" which usually means text
yet your field type is actually a date
- your field name ends with "month" when actually it's a date
- Your sample data supports the assumption you have a text field
- you didn't tell us the field was a date field even though
you wanted to group it by year

You would get better answers faster if you provided better background and
specifications. Then you would have more time for "battling with another
problem" and so would we ;-)

Hi Duane, battling with another problem now but I'm learning how to think
before I go headlong into things like I did in the report problem!

[quoted text clipped - 17 lines]

 

[Duane Hookom]

There are a number of good naming conventions. You should use one that makes
sense to you and hopefully makes sense to others. If it is a bit of a
departure from standards, you need to provide some minimal documentation in
your threads.

I have seen lots of developers place "fld" or a data type prefix (int, dbl,
dat,...) at the start of field names. I prefer to always begin a field name
with 3 letters that identify its table. I almost always create an autonumber
primary key that repeats the 3 letters and adds "ID". Most of the time, you
should be able to determine the field type by looking at its name (but not
always). Example:

tblEmployees
===========
empEmpID autonumber primary key
empFirstName
empLastName
empHireDate
empEStID link to tblEmpStatus.estEStID
empDptID link to tblDepartments.dptDptID

txt is reserved as a prefix for text boxes
cbo and lbo are for combo and list boxes
tbl is reserved for tables
qsel, qupd, qxtb, qgrp, qtot, and quni are all used various queries
frm, fmn, sfrm are used for forms and subforms
var, dbl, int, str, dat, boo are all used for different types of memory
variables

I do this because I am also old and often confused. This system has worked
well for me for many years and is mostly consistant with other accepted
conventions.
--
Duane Hookom
Microsoft Access MVP

Duane, I apologise - that's very sound advice. I am not a IT proffessional
and now that I am retired (at 65) I started to look at building databases
just to keep the grey cells active. Initially it was a membership database
for my local tennis club and as I've enjoyed it more, I've offered to do far
more complex (well I think they are) projects for friends. I therefore
haven't gone through the formal training of many of the people who post on
here and it obviously shows. But I'm keen to learn.

On the naming issue I do get confused in that many books I have read and
comments I've seen on newsgroups such as this often talk about the
standardisation of a naming convention txt,cmb etc and yet when I look at
Microsoft's example database they seem to completely abandon that. I do
wonder whether for simplicity I should just choose field names that are self
evident even though they may be unnecessarily long, but that's another issue.

Anyway, thanks again for your pointers and I'll endeavour to make my future
posts clearer.

Tony

Tony,
Can you see how confusing your question is when:
- your field name begins with "txt" which usually means text
yet your field type is actually a date
- your field name ends with "month" when actually it's a date
- Your sample data supports the assumption you have a text field
- you didn't tell us the field was a date field even though
you wanted to group it by year

You would get better answers faster if you provided better background and
specifications. Then you would have more time for "battling with another
problem" and so would we ;-)

Hi Duane, battling with another problem now but I'm learning how to think
before I go headlong into things like I did in the report problem!

[quoted text clipped - 17 lines]

Thanks
Tony

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!




.

 

[TonyWilliams via AccessMonster.com]

Thanks Duane I've printed that of and shall use in future projects as my
"bible"!
many thanks
Tony

There are a number of good naming conventions. You should use one that makes
sense to you and hopefully makes sense to others. If it is a bit of a
departure from standards, you need to provide some minimal documentation in
your threads.

I have seen lots of developers place "fld" or a data type prefix (int, dbl,
dat,...) at the start of field names. I prefer to always begin a field name
with 3 letters that identify its table. I almost always create an autonumber
primary key that repeats the 3 letters and adds "ID". Most of the time, you
should be able to determine the field type by looking at its name (but not
always). Example:

tblEmployees
===========
empEmpID autonumber primary key
empFirstName
empLastName
empHireDate
empEStID link to tblEmpStatus.estEStID
empDptID link to tblDepartments.dptDptID

txt is reserved as a prefix for text boxes
cbo and lbo are for combo and list boxes
tbl is reserved for tables
qsel, qupd, qxtb, qgrp, qtot, and quni are all used various queries
frm, fmn, sfrm are used for forms and subforms
var, dbl, int, str, dat, boo are all used for different types of memory
variables

I do this because I am also old and often confused. This system has worked
well for me for many years and is mostly consistant with other accepted
conventions.

Duane, I apologise - that's very sound advice. I am not a IT proffessional
and now that I am retired (at 65) I started to look at building databases

[quoted text clipped - 33 lines]