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.
4 5692
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
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.
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 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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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
|
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...
|
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....
|
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...
|
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...
|
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...
|
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 ...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
| |