Counting items in a query? (1 Viewer)

[mrbeepa]

I'm not real swift at Access, which will be obvious!
I have a data base of items, some items are categorized as Ancient and some are categorized as Spanish.
Each item in the table is unique so there are only one of each item.

I am trying to create a year end query where I can total the cost of each category and know the count of how many is in each category.
The way I'm trying to do this is to create a column in the query that counts the number of items selected from the table in the query itself.
I have tried using DSum and Count, but when I use count I get the total of how many of the item selected in that row and not the total of all the rows in the query;
so count returns 1 in each case.

DSum I have not totally figured out the syntax even though I have read up on several sites.
It is not clear to me how to enter it.

Can anyone show me an example of a query that counts the number of items selected by the query itself?

 

[pbaldy]

From the sound of it:

SELECT Category, Count(*) AS HowMany
FROM TableName
GROUP BY Category

 

[mrbeepa]

From the sound of it:

SELECT Category, Count(*) AS HowMany
FROM TableName
GROUP BY Category

If my table is ItemDB is this how it would read?
SELECT "Qty", Count(*) AS "total" FROM "ItemDB" GROUP BY "ItemDBID"

Qty and ItemDBID are actual columns in the table, ItemDBID is the autonumber unique primary key.
Do I leave the wildcard and put table names and column names in quotes as I have in the example above?

 

[pbaldy]

No quotes, leave the wildcard, and I would think you'd want the category field not the Qty field (which I assume is quantity) in both the SELECT and GROUP BY clauses.

 

[mrbeepa]

No quotes, leave the wildcard, and I would think you'd want the category field not the Qty field (which I assume is quantity) in both the SELECT and GROUP BY clauses.

Here is the sql statement I have now. (with changes I thought you meant?)
SELECT ItemDBID, Count(*) AS total FROM ItemDB GROUP BY ItemDBID
What I would like is for the total column to show a running total,
so it would look like this instead of what is there:
ItemDBID total
93 1
98 2
101 3 and etc..

ItemDBID	total
91	1
92	1
93	1
94	1
95	1
96	1
97	1
98	1
99	1
100	1
101	1
102	1
103	1
104	1
105	1
106	1
107	1

 

[pbaldy]

What is the name of the field containing "Ancient", or "Spanish"?

 

[mrbeepa]

What is the name of the field containing "Ancient", or "Spanish"?

That column is Era

I just found this code on a site and got it to work for one column but am unable to add another totaled column:

SELECT A.[ItemDBID], A.[PriceCost], Sum(B.[PriceCost]) AS "running total"
FROM ItemDB AS A INNER JOIN ItemDB AS B ON A.[ItemDBID] >= B.[ItemDBID]
GROUP BY A.[ItemDBID], A.[PriceCost]
ORDER BY 1;

I would like to add the total of the Qty column to this. Or if you have another way of doing both I would appreciate that my table(ItemDB is the table name) has the following columns. ItemID (an autonumber primary key), CertNumber,Era,PriceCost,Qty. (there are more columns but these are the ones I'm trying to deal with in this query.)

I'd like to have two running total columns one for PriceCost and one for Qty and be able to sort by Era or CertNumber would be nice.

Thank you for your time and patience in helping me!

 

[pbaldy]

I don't think you want the autonumber in there, unless I've misunderstood the goal. See if this gets you anything good:

SELECT Era, Count(*) AS HowMany, Sum(PriceCost) AS HowMuch
FROM ItemDB
GROUP BY Era

 

[mrbeepa]

I don't think you want the autonumber in there, unless I've misunderstood the goal. See if this gets you anything good:

SELECT Era, Count(*) AS HowMany, Sum(PriceCost) AS HowMuch
FROM ItemDB
GROUP BY Era

AWESOME! Exactly what I needed! Thank you so much! I was obviously making it much harder than it had to be!
[EDIT]
But I've ended up still with a problem, using your sql statement how can I make it so I can put criteria in other columns like in the CertNumber column I would like to exclude numbers that start with MT so I added the CertNumber column to your sql query (but in design view) and put criteria in as such: Not Like "MT" and when I do that it stops totalling the columns ?

 

[pbaldy]

Ah good, glad we got it sorted out.

 

[mrbeepa]

Please look at my edit in my last post.

 

[pbaldy]

When you add the column, the Total row will default to "Group By". Change that to "Where" and see how it looks.

 

[mrbeepa]

When you add the column, the Total row will default to "Group By". Change that to "Where" and see how it looks.

Once again AWESOME! Thanks pbaldy! You made my day a whole lot easier and more productive.

 

[pbaldy]

No problemo!