Group and Count records in a table

[AccessHunter]

Hi,

I have a table with the type of data as below,
sorted as follows, Case (ascending), Loc (ascending) and Transaction Date (ascending).

CASE	LOC	TRANSACTION DATE     FACILITY
0029174	01	20061025            STORE-A
0029174	01	20061102            WAREHOUSE-1
0029174	01	20061124            WAREHOUSE-1
0029174	01	20061221            WAREHOUSE-1
0029174	01	20080414            STORE-B
0029174	01	20080506            WAREHOUSE-1
0029174	01	20080507            STORE-C
0029174	01	20080603            WAREHOUSE-1
0029174	01	20080706            WAREHOUSE-1

I am trying to count the records like this, the first record where Facility = Store-A as record number 1, next three records where Facility = Warehouse-1 as record number 2, next record where Facility = Store-B as record number 3, next record where Facility = Warehouse-1 as record number 4,next record where Facility = Store-C as record number 5 and next two records where Facility = Warehouse-1 as record number 6. So the count for case = 0029174 and loc = 01 should be 6 records.
Hope I explained it properly

Please treat this as urgent and help.

[Stewart Ross]

Hi. You need to use a query to group your rows by case, location and facility without the transaction date, then base the count on the results of that query.

The grouping query will be along the lines of

SELECT Case, Loc, Facility FROM yourtable 
GROUP BY Case, Loc, Facility;

(if you use the Access query editor you can turn on grouping by using View, Totals.)

If you stored this as GroupQuery1, say, then all you should need to do after that is to run a totals query based on it:

SELECT Case, Count(*) as CaseCount
FROM GroupQuery1
GROUP BY Case;

-Stewart

[AccessHunter]

Hi,

Thanks for the solution.
I created the GroupQuery1 as you explained, but the result I got is this

CASE	LOC	FACILITY

0029174	01	WAREHOUSE-1
0029174	01	STORE A
0029174	01	STORE B
0029174	01	STORE C

It grouped all the warehouse-1 records to one instead of like I said in my first post.

Please help.

Thanks.

[Stewart Ross]

Hi. Apologies. I thought I understood your requirement but clearly did not.

Looking again at what you have asked, you cannot get the sequence you want without something else to differentiate the Warehouse 1 occurrences (what you have termed should be records 4 and 6). You can see that clearly from the grouping that follows from my earlier solution.

SQL has no concept of record position; it does not know that one occurrence of Warehouse 1 is different to another unless there is a factor that can be specified to differentiate these occurrences. Unless you have an algorithm that would allow for transformation of the first set of Warehouse 1 transaction dates to a different value than the second set of dates there is no way that I can see to do what I now understand you need to do from what you have actually told us in your posts.

I have come up with one possible way but I cannot tell if it is valid or not for all your data. Looking at the transaction dates, you could include in the grouping query the year value, by using the following aliased field in your query:

TrYear: Left([Transaction Date], 4)

This should group the rows correctly for you in terms of what you have posted, but as this is my interpretation and you have not specified that the year grouping is indeed a valid grouping in all circumstances I can only advise that it will give you the count you require for the rows you have shown - no more and no less.

-Stewart

[NeoPa]

If I understand your requirement correctly this is certainly not something easily shoe-horned into SQL as it is not an RDBMS issue.

What you have is a processing issue which needs to GROUP records by one set of fields, but only after sorting them by another.

The Transaction Date is a necessary part of the sorting but must be ignored when the GROUPing is done.

I can only suggest you process through a sorted recordset manually (with code) and count your groups that way.