By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

Group and Count records in a table

P: 77
Hi,

I have a table with the type of data as below,
sorted as follows, Case (ascending), Loc (ascending) and Transaction Date (ascending).
Expand|Select|Wrap|Line Numbers
  1. CASE    LOC    TRANSACTION DATE     FACILITY
  2. 0029174    01    20061025            STORE-A
  3. 0029174    01    20061102            WAREHOUSE-1
  4. 0029174    01    20061124            WAREHOUSE-1
  5. 0029174    01    20061221            WAREHOUSE-1
  6. 0029174    01    20080414            STORE-B
  7. 0029174    01    20080506            WAREHOUSE-1
  8. 0029174    01    20080507            STORE-C
  9. 0029174    01    20080603            WAREHOUSE-1
  10. 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.
Sep 5 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. SELECT Case, Loc, Facility FROM yourtable 
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Case, Count(*) as CaseCount
  2. FROM GroupQuery1
  3. GROUP BY Case;
-Stewart
Sep 5 '08 #2

P: 77
Hi,

Thanks for the solution.
I created the GroupQuery1 as you explained, but the result I got is this
Expand|Select|Wrap|Line Numbers
  1. CASE    LOC    FACILITY
  2.  
  3. 0029174    01    WAREHOUSE-1
  4. 0029174    01    STORE A
  5. 0029174    01    STORE B
  6. 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.
Sep 6 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Sep 6 '08 #4

NeoPa
Expert Mod 15k+
P: 31,429
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.
Sep 6 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.