Stup-id question about Count query

  • Thread starter Thread starter Lunga
  • Start date Start date
L

Lunga

I'm building a Select query, for a report, from a single Table that
prompts for date criteria and displays the records. I want to group
and total the returned records by another field, "Transaction_Type".
The field has only 4 possible values(Issue, Return, Reissue, Delete)
so I want to know the count of each value.

After reading Access Help, I clicked, View|Totals. A new line
appeared on the detail view of the Query and in the "Transaction_Type"
field, selected Count from the Group list, saved and ran it.

The results were displayed and returned the correct number of records,
but instead of grouping, or counting, it showed all the records and a
new Column appeared called "CountofTransaction_Type" with the number 1
in the field.

Using Access 2003, Linked to SQL table. (Yea I know, I inherited it
from someone else)

Obviously I'm doing something wrong and searching google has returned
a lot of
Answers, but none more than Access help could provide.

Thanks in advance, for your help
 
Perhaps something like this:

SELECT TableName.Transaction_Type,
Count(TableName.FieldName1) AS CountOfFieldName1,
Count(TableName.FieldName2) AS CountOfFieldName2,
Count(TableName.FieldName3) AS CountOfFieldName3,
Count(TableName.FieldName4) AS CountOfFieldName4
FROM TableName
GROUP BY TableName.Transaction_Type;
 
Lunga,

What do you mean by:

" Using Access 2003, Linked to SQL table. (Yea I know, I inherited it from
someone else)"

SQL Server is a great database, and depending on what your application is
doing, linking Access as a front end to a SQL Server backend works great.
For those forms that you want to be bound, this is a perfect solution. For
those tables that don't need to be bound, SQL Server works great as the
backend.

Dale
 
I think what you need to do here is a Cross-tab query. This example will
give you a row for each day that has any kind of transaction, and 4 columns
(as you have described below), with the number of records for each
transaction type, for each day.

Add the Tranaction_Date, Transaction_Type, and the ID (assumes you have an
ID field as your primary key) fields to the query grid.

From the menu, select Query->Crosstab.

In the "Total" row of the query grid, enter Group_By under Transaction_Date,
and Transaction_Type, enter Count under the ID field.

In the Crosstab Row, select Row Heading for the Transaction_Date, Column
Heading for the Transaction_Type, and Value for the ID field.

Then, in the criteria row of the Transaction_Date, enter your date prompt.
You might want to make it more generic, to allow you to do multiple days,
something like:

BETWEEN Cdate([Enter Start Date]) AND DateAdd("d", [Number of days],
Cdate([Enter Start Date])


HTH
Dale
 
If your using any version of S2k you can use the
RAC (Relational Application Companion) utility
as a native sql server tool.RAC can replace the
Access crosstab and is far more powerful with many
features/options.It overcomes all the limitations of the
Access crosstab and dramatically extends functionality.
In addition to solving pivoting/crosstab problems, it
also solves many other common data manipulation
problems without resort to complicated sql coding.
For more advanced users it is fully integrated with
S2k for even more power to solve complex problems.
RACs easy to use ranking options simulate the sql99
analytic functions that will be (hopefully) included in
Yukon.RAC is a system of sp's and functions and comes
with a gui or can be executed like any server sp,either
in batch or embedded in another sp.RAC can be used
by any client that can connect to S2k including Access:)
RAC installs directly in a S2k database.

RAC v2.2 and QALite @
www.rac4sql.net
 
Thanks Ken, I thought I wasn't going to have to type SQL statements,
but it looks like I'm going to have to...<sigh>

Lee
 
It was meant to fend off the unhelpfull "why are you doing it that
way" replies,
Where the person ignores your questions and talks about how they would
do it. I've used Access as a front end since 2000 came out and I
think it's great.

Thanks for your answer on the cross-tab query by the way. I was
thinking of doing it that way, but it seemed like such a "round-about
way" to get to where I'm going.
Lee
 
Lunga said:
It was meant to fend off the unhelpfull "why are you doing it that
way" replies,
Where the person ignores your questions and talks about how they would
do it. I've used Access as a front end since 2000 came out and I
think it's great.

Thanks for your answer on the cross-tab query by the way. I was
thinking of doing it that way, but it seemed like such a "round-about
way" to get to where I'm going.
Lee


"Dale Fye" <[email protected]> wrote in message
Click to expand...
 
Lunga said:
Thanks for your answer on the cross-tab query by the way. I was
thinking of doing it that way, but it seemed like such a "round-about
way" to get to where I'm going.
Lee
Click to expand...

If your going to do crosstabs/pivoting on S2k check out
the RAC utility.Similar to Access crosstab but much
more powerful with many features/options.Simple to use,
no completed sql coding is necessary.

RAC v2.2 and QALite @
www.rac4sql.net
 
Back
Top