473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Group and Count records in a table

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
4 5692
Stewart Ross
2,545 Expert Mod 2GB
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
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
Stewart Ross
2,545 Expert Mod 2GB
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
32,556 Expert Mod 16PB
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

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

Similar topics

3
by: Rich Protzel | last post by:
Hello, So my table contains say 100,000 records, and I need to group the categories in fld1 by the highest count of subcategories. Say fld1 contains categories A, B, C, D, E. All of these...
2
by: Matik | last post by:
Hello everyone, Small and (I think) very simple quesiton;-) which makes me creazy. Let's say I have two tables listed below: T1 ==== IDX ==== 1
2
by: wilscott1st | last post by:
Is it possible in access to select the total count of records for a given group of records and show those all those counts in one report? For example, I have a table of records that contain...
3
by: Hyphessobricon | last post by:
Hallo, Indeed, a count of a query with a group by function gives more records than there are and so for-next structures don't function. How is this to be mended. Anyone? Everyone in fact....
5
by: jacob.dba | last post by:
I have a table with first name, last name, SSN(social security number) and other columns. I want to assign group number according to this business logic. 1. Records with equal SSN and (similar...
2
by: john | last post by:
I have a table with 5 fields. In a query I would like to make a count for every unique combination of field 2,3, and 4. I fiddled with the count and dcount option, but can't get it to work for more...
9
by: MLH | last post by:
A mailing list table in its virgin state contained sequential, consecutive integers in an autonumber field (A97). I've deleted records throughout the table. Now I would like to identify each...
6
by: linzeyd | last post by:
I have a problem that's driving me crazy. i have a table that is a series of things like this (Unit type) (part description) (count) IWU High pressure flowmeter 3 IWU Transformer ...
4
by: SjaakBanaan | last post by:
Hey all, I have a question that ought to be simple but has given me headaches for a while. I have a table with contact email addresses, say. T ID ContID Priority Emailaddress...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.