Query in Access 2007 for Running Count

wlreimer
wlreimer used Ask the Experts™
on
I have a table with the following columns: Vendor, Product.
I need to create a query that will count for each Vendor/Product combination so that I get the following:
Vendor | Product | Count
ACME        ABC       1
ACME        ABC       2
ACME         DEF       1
BOLTS       GHI        1

This the reason behind this, I need to be able to use the count field in another query so I need that count to be dynamic based on the info currently in that table.

I can do this in a report, but have tried and tried, searched and searched and can't find a solution to work in a query.

Any help would be appreciated.


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Is there a reason for having

ACME        ABC       1
ACME        ABC       2

In there twice with 1,2 instead of just

ACME        ABC       2  -- count for unique?

Just clarifying

Author

Commented:
Yes, I actually need to take the count and put all of it into another table to create a unique index of
Vendor | Product | Option
ACME      ABC          1
ACME      ABC          2
This is a view with aggregation, or also known as pivot table.
You have to enable the function view (or Totals in newer versions), then you have an additional line in your view, where you can select, how to aggregate your fields.
In your case Vendor: group, Product group, ID field or any other field with values : Count
OK, field "Option" also group

Author

Commented:
Right, but the table that I'm trying to query doesn't have Option yet.
I'm trying to take Vendor | Product | CountofProduct and make Vendor | Product | Option
Here's one of my queries that doesn't work , like what you suggested:
"SELECT Catalog.Vendor, Catalog.[Product #], Count(Catalog.[Product #]) AS [Option]
FROM [Catalog]"
GROUP BY Catalog.Vendor, Catalog.[Product #];
But, what I get is
ACME ABC 2
ACME DEF 1
What I need is this:
ACME ABC 1
ACME ABC 2
ACME DEF 1
You mean you need someting like a increasing number?
I have currently no idea how to do this with a simple query.
I would write a macro for this to walk through the table and count the occurences manually.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
SELECT * FROM (
SELECT r.vendor, r.product, (select 1+count(*) from runcount as r2 where r.vendor=r2.vendor and r.product=r2.product and r2.id<r.id) as opt
FROM runcount as  r
) AS SQ
order by vendor, product, opt asc;


Change runcount to your table name.
Change "ID" to a unique key in the table.

Author

Commented:
It has to be dynamic.  I can't have to change the table structure to make this happen because the person who runs it regularly will NOT get it.
Bembi, I'm not good with marcro's can you tell me how to do it with a macro?
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
If #33119363 was for me, then

I mean change my query in http:#a33119324 to match your table name... !
Top Expert 2016

Commented:
wlreimer,
you will need a Unique recordId to do this.. assming the unique recordId field is named  ID

select ID,Vendor, Product, (select count(*) from tablex A where Vendor=Tablex.vendor and  Product=tablex.product and ID <= tableX.id) as cnt
from tableX
order by tablex.Vendor, tablex.Product

change tableX with actual name of table..

if you don't have a unique recordId,  add an autonumber field to your table and call it ID




Author

Commented:
cyberkiwi, I didn't mean to offend.  I did change runcount to my table name.
My problem is with having to create the unique record id.
I have to make all of this happen programatically with no user intervention required.
If I create a table ahead of time with a unique record id, and run an append query to put all of the records into that table, they will automatically be assigned the unique record id right?  Then a query could run like you are suggesting.  I'll try it.
Top Expert 2016
Commented:
you can run this query to add an autonumber field

          alter table TableX add column ID counter

Author

Commented:
Ok, I'm missing something.  This is what I did:
SELECT CatalogTemp.ProductID, CatalogTemp.[Vendor*], CatalogTemp.[PrivateStyle20*], Count(CatalogTemp.[PrivateStyle20*]) AS Option1
FROM CatalogTemp
GROUP BY CatalogTemp.ProductID, CatalogTemp.[Vendor*], CatalogTemp.[PrivateStyle20*];

But, I'm getting a 1 in the count for each one.  Should I not be including ProductID as a group by?

Author

Commented:
Sorry if I confused you.  The column names are different but it's still the same.  PrivateStyle20* is product.
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
SELECT * FROM (
SELECT r.[Vendor*], r.[PrivateStyle20*], (select 1+count(*) from runcount as r2 where r.[Vendor*]=r2.[Vendor*] and r.[PrivateStyle20*]=r2.[PrivateStyle20*] and r2.ProductID<r.ProductID) as Option1
FROM CatalogTemp as r
) AS SQ
order by [Vendor*], [PrivateStyle20*], Option1 asc;
Top Expert 2016

Commented:
is the productID a unique field?

try this, copy and paste

select ProductID,[Vendor*], [PrivateStyle20*], (select count(*) from CatalogTemp A where [Vendor*]=CatalogTemp.[Vendor*] and  [PrivateStyle20*]=CatalogTemp.[PrivateStyle20*] and ProductID <= CatalogTemp.ProductID) as runcount
from CatalogTemp
order by CatalogTemp.[Vendor*], CatalogTemp.Product

Author

Commented:
Thank you cyberkiwi and capricorn1!  I appreciate the help more than you know.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial