Can this query be done?

[S Davis]

Hello,

I have a single table with the following fields:

CLASS
CATEGORY
READDIFF
CODE
DATE

Before I go to far, I should explain one thing: CLASS, CATEGORY, and
CODE are hierarchically related; CODE being between 1 - 10000 and the
lowest child, CATEGORY being the parent of code and having 3
variations, and CLASS being the parent of CATEGORY and having 6
variations. Eventually I would like to display 18 lines of data (6
CLASS with 3 CATEGORY each), with the CODES being grouped together
under each CLASS and CATEGORY and used as unique identifiers to sum
READDIFF on each line. Example:

Note: this data also includes the field DATE, not shown here, which is
explained further below)
CLASS CATEGORY CODE READDIFF
1 1 1 10
1 1 2 15
1 1 3 20
1 2 4 10
1 2 5 7.5
1 2 6 5
1 3 7 10
1 3 8 5
1 3 9 2
2 1 10 10
2 1 11 50
....
6 1 18 75

would be displayed as:

CLASS CATEGORY SumOfREADDIFF
1 1 17
1 2 22.5
1 3 17
2 1 70
....
6 1 75

The above is what I'm trying to achieve.

I need to find some way, either in one query or in a thousand
subqueries, to return what I had outlined above. However, the
additional field of DATE will also need to be used to set boundaries
for summing READDIFF. Ie., sum all READDIFF between 1/1/1900 and
1/1/1901, but exclude anything before or after.

Additionally, there are many instances of the same CODE, as each CODE
has had multiple readings put into the database at different times.
Trying to group together CODE (which seems the simple solution) gives
me problems because each CODE has a unique time and date and also a
different READDIFF. Example:

CODE READDIFF DATE
1 10 1/1/1900
1 5 1/1/1901
1 2 1/1/1902

So trying to do a simple GroupBy on the CODE and a Sum of READDIFF
still results in three unique lines due to the DATE, except where of
course there are two or more DATE that are the same. Unfortunately,
DATE must be included so that the timelines can be established. This
seems to be a flaw (as far as with what I want ot have happen in
access) that constricts me to having to perform a GROUP BY on DATE
since I am performing a SUM on READDIFF and a GROUP BY on CODE. I do
not want to group DATE together, but merely use it as a filter.

Using the simply GROUP BY and SUM functions, I end up with a result
that is approximately 25,000 lines long, rather than the 18 I am
seeking.

Best I can explain in text is to show:


Each unique MRC
Each unique CATEGORY under each MRC
Group together all CODE which are the same
Sum all READDIFF for each grouped CODE as CODEDIFF
Sum all CODEDIFF under each unique MRC and CATEGORY


I've played with using a DISTINCT, but can't construct the query to
work with any success at all.
Thank you one and all to any insight I can be given!

 

[S Davis]

Also, please feel free to email me direct at theseandavis at gmail dot
com with possible solutions. I will post back if there is something
found that works.

 

[John Spencer]

This sounds like a simple summary query.

SELECT Class, Category, SUM(ReadDiff) as SumOf
FROM YourTable
WHERE [Date] Between #1/1/1900# and #12/31/1900#
GROUP BY Class, Category


In the query grid
-- Put Class, Category, ReadDiff, and Date as fields
-- Enter the date range as criteria under the date field
-- Select View: Totals from the menu
-- Change Group By to Sum under ReadDiff
-- Change Group By to WHERE under the date field.

John Spencer
Access MVP 2002-2005, 2007

 

[S Davis]

Thanks John,

That looks like it will work. Unfortunately, and embarrassingly, I left
out one important part of what I am trying to do. This might be helpful
because the first post was getting long winded as it was.

I additionally want to see a count of the unique CODEs. So, this would
be a count of CODE 1, 2, 3, etc, but not a count of each occurrence -
just the unique codes. If counting CODE by itself, each instance should
return "1", meaning that there is 1 unique CODE. However, when
presenting this under each CATEGORY and CLASS, it should be a count of
all unique CODEs. Example:

If CODE 1 - 2500 are under CLASS 1 and CATEGORY 1, then it should read:

CLASS CATEGORY CODE
1 1 2500

This should be displayed regardless of the fact that there are multiple
entries of the same code with different values, much like I was
describing earlier regarding the dates. So if CODE 1 was in the
database 100 times, as well as CODE 2, CODE 3, etc, the end count would
normally be 2500 * 100 = 250,000 CODEs. I would like a count of all
UNIQUE codes, so that even with 250,000 entries, I still receive the
number 2500.

My apologies for leaving this out earlier.

Thank you!

This sounds like a simple summary query.

SELECT Class, Category, SUM(ReadDiff) as SumOf
FROM YourTable
WHERE [Date] Between #1/1/1900# and #12/31/1900#
GROUP BY Class, Category


In the query grid
-- Put Class, Category, ReadDiff, and Date as fields
-- Enter the date range as criteria under the date field
-- Select View: Totals from the menu
-- Change Group By to Sum under ReadDiff
-- Change Group By to WHERE under the date field.

John Spencer
Access MVP 2002-2005, 2007

Hello,

I have a single table with the following fields:

CLASS
CATEGORY
READDIFF
CODE
DATE

Before I go to far, I should explain one thing: CLASS, CATEGORY, and
CODE are hierarchically related; CODE being between 1 - 10000 and the
lowest child, CATEGORY being the parent of code and having 3
variations, and CLASS being the parent of CATEGORY and having 6
variations. Eventually I would like to display 18 lines of data (6
CLASS with 3 CATEGORY each), with the CODES being grouped together
under each CLASS and CATEGORY and used as unique identifiers to sum
READDIFF on each line. Example:

Note: this data also includes the field DATE, not shown here, which is
explained further below)
CLASS CATEGORY CODE READDIFF
1 1 1 10
1 1 2 15
1 1 3 20
1 2 4 10
1 2 5 7.5
1 2 6 5
1 3 7 10
1 3 8 5
1 3 9 2
2 1 10 10
2 1 11 50
...
6 1 18 75

would be displayed as:

CLASS CATEGORY SumOfREADDIFF
1 1 17
1 2 22.5
1 3 17
2 1 70
...
6 1 75

The above is what I'm trying to achieve.

I need to find some way, either in one query or in a thousand
subqueries, to return what I had outlined above. However, the
additional field of DATE will also need to be used to set boundaries
for summing READDIFF. Ie., sum all READDIFF between 1/1/1900 and
1/1/1901, but exclude anything before or after.

Additionally, there are many instances of the same CODE, as each CODE
has had multiple readings put into the database at different times.
Trying to group together CODE (which seems the simple solution) gives
me problems because each CODE has a unique time and date and also a
different READDIFF. Example:

CODE READDIFF DATE
1 10 1/1/1900
1 5 1/1/1901
1 2 1/1/1902

So trying to do a simple GroupBy on the CODE and a Sum of READDIFF
still results in three unique lines due to the DATE, except where of
course there are two or more DATE that are the same. Unfortunately,
DATE must be included so that the timelines can be established. This
seems to be a flaw (as far as with what I want ot have happen in
access) that constricts me to having to perform a GROUP BY on DATE
since I am performing a SUM on READDIFF and a GROUP BY on CODE. I do
not want to group DATE together, but merely use it as a filter.

Using the simply GROUP BY and SUM functions, I end up with a result
that is approximately 25,000 lines long, rather than the 18 I am
seeking.

Best I can explain in text is to show:


Each unique MRC
Each unique CATEGORY under each MRC
Group together all CODE which are the same
Sum all READDIFF for each grouped CODE as CODEDIFF
Sum all CODEDIFF under each unique MRC and CATEGORY


I've played with using a DISTINCT, but can't construct the query to
work with any success at all.
Thank you one and all to any insight I can be given!

 

[John Spencer]

All in one query.

SELECT Class, Category, SUM(ReadDiff) as SumOf, First(CodeCount) as
TheCount
FROM YourTable Inner Join
(
SELECT Class, Category, Count(Code) as CodeCount
FROM
(SELECT Distinct Class, Category, Code
FROM YourTable
WHERE YourTable.Date Between #1/1/1900# and #12/31/1900#
GROUP BY Class, Category) as CC1) as CC
ON YourTable.Class = CC.Class and YourTable.Category = CC.Category
WHERE [Date] Between #1/1/1900# and #12/31/1900#
GROUP BY Class, Category


Three query solution.
Save the following as CC1
SELECT Distinct Class, Category, Code
FROM YourTable
WHERE YourTable.Date Between #1/1/1900# and #12/31/1900#

Save the Following as CC
SELECT Class, Category, Count(Code) as CodeCount
FROM CC1
GROUP BY Class, Category

Finally in the third query, use the CC query and join it to your table
SELECT Class, Category, SUM(ReadDiff) as SumOf, First(CodeCount) as
TheCount
FROM YourTable Inner Join CC
ON YourTable.Class = CC.Class and YourTable.Category = CC.Category
WHERE [Date] Between #1/1/1900# and #12/31/1900#
GROUP BY Class, Category

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

Thanks John,

That looks like it will work. Unfortunately, and embarrassingly, I left
out one important part of what I am trying to do. This might be helpful
because the first post was getting long winded as it was.

I additionally want to see a count of the unique CODEs. So, this would
be a count of CODE 1, 2, 3, etc, but not a count of each occurrence -
just the unique codes. If counting CODE by itself, each instance should
return "1", meaning that there is 1 unique CODE. However, when
presenting this under each CATEGORY and CLASS, it should be a count of
all unique CODEs. Example:

If CODE 1 - 2500 are under CLASS 1 and CATEGORY 1, then it should read:

CLASS CATEGORY CODE
1 1 2500

This should be displayed regardless of the fact that there are multiple
entries of the same code with different values, much like I was
describing earlier regarding the dates. So if CODE 1 was in the
database 100 times, as well as CODE 2, CODE 3, etc, the end count would
normally be 2500 * 100 = 250,000 CODEs. I would like a count of all
UNIQUE codes, so that even with 250,000 entries, I still receive the
number 2500.

My apologies for leaving this out earlier.

Thank you!

This sounds like a simple summary query.

SELECT Class, Category, SUM(ReadDiff) as SumOf
FROM YourTable
WHERE [Date] Between #1/1/1900# and #12/31/1900#
GROUP BY Class, Category


In the query grid
-- Put Class, Category, ReadDiff, and Date as fields
-- Enter the date range as criteria under the date field
-- Select View: Totals from the menu
-- Change Group By to Sum under ReadDiff
-- Change Group By to WHERE under the date field.

John Spencer
Access MVP 2002-2005, 2007

Hello,

I have a single table with the following fields:

CLASS
CATEGORY
READDIFF
CODE
DATE

Before I go to far, I should explain one thing: CLASS, CATEGORY, and
CODE are hierarchically related; CODE being between 1 - 10000 and the
lowest child, CATEGORY being the parent of code and having 3
variations, and CLASS being the parent of CATEGORY and having 6
variations. Eventually I would like to display 18 lines of data (6
CLASS with 3 CATEGORY each), with the CODES being grouped together
under each CLASS and CATEGORY and used as unique identifiers to sum
READDIFF on each line. Example:

Note: this data also includes the field DATE, not shown here, which is
explained further below)
CLASS CATEGORY CODE READDIFF
1 1 1 10
1 1 2 15
1 1 3 20
1 2 4 10
1 2 5 7.5
1 2 6 5
1 3 7 10
1 3 8 5
1 3 9 2
2 1 10 10
2 1 11 50
...
6 1 18 75

would be displayed as:

CLASS CATEGORY SumOfREADDIFF
1 1 17
1 2 22.5
1 3 17
2 1 70
...
6 1 75

The above is what I'm trying to achieve.

I need to find some way, either in one query or in a thousand
subqueries, to return what I had outlined above. However, the
additional field of DATE will also need to be used to set boundaries
for summing READDIFF. Ie., sum all READDIFF between 1/1/1900 and
1/1/1901, but exclude anything before or after.

Additionally, there are many instances of the same CODE, as each CODE
has had multiple readings put into the database at different times.
Trying to group together CODE (which seems the simple solution) gives
me problems because each CODE has a unique time and date and also a
different READDIFF. Example:

CODE READDIFF DATE
1 10 1/1/1900
1 5 1/1/1901
1 2 1/1/1902

So trying to do a simple GroupBy on the CODE and a Sum of READDIFF
still results in three unique lines due to the DATE, except where of
course there are two or more DATE that are the same. Unfortunately,
DATE must be included so that the timelines can be established. This
seems to be a flaw (as far as with what I want ot have happen in
access) that constricts me to having to perform a GROUP BY on DATE
since I am performing a SUM on READDIFF and a GROUP BY on CODE. I do
not want to group DATE together, but merely use it as a filter.

Using the simply GROUP BY and SUM functions, I end up with a result
that is approximately 25,000 lines long, rather than the 18 I am
seeking.

Best I can explain in text is to show:


Each unique MRC
Each unique CATEGORY under each MRC
Group together all CODE which are the same
Sum all READDIFF for each grouped CODE as CODEDIFF
Sum all CODEDIFF under each unique MRC and CATEGORY


I've played with using a DISTINCT, but can't construct the query to
work with any success at all.
Thank you one and all to any insight I can be given!

 

[S Davis]

After some very minor tweaks, it works!!!

To be honest, that blows my mind the way you thought through that. Now
that I see it working, I can almost lie myself into thinking I would
have been able to come to a solution

Thank you so much!!! Send me an address to mail a cheque... you bailed
me out of a big mess!!!!!

All the best,
-Sean

All in one query.

SELECT Class, Category, SUM(ReadDiff) as SumOf, First(CodeCount) as
TheCount
FROM YourTable Inner Join
(
SELECT Class, Category, Count(Code) as CodeCount
FROM
(SELECT Distinct Class, Category, Code
FROM YourTable
WHERE YourTable.Date Between #1/1/1900# and #12/31/1900#
GROUP BY Class, Category) as CC1) as CC
ON YourTable.Class = CC.Class and YourTable.Category = CC.Category
WHERE [Date] Between #1/1/1900# and #12/31/1900#
GROUP BY Class, Category


Three query solution.
Save the following as CC1
SELECT Distinct Class, Category, Code
FROM YourTable
WHERE YourTable.Date Between #1/1/1900# and #12/31/1900#

Save the Following as CC
SELECT Class, Category, Count(Code) as CodeCount
FROM CC1
GROUP BY Class, Category

Finally in the third query, use the CC query and join it to your table
SELECT Class, Category, SUM(ReadDiff) as SumOf, First(CodeCount) as
TheCount
FROM YourTable Inner Join CC
ON YourTable.Class = CC.Class and YourTable.Category = CC.Category
WHERE [Date] Between #1/1/1900# and #12/31/1900#
GROUP BY Class, Category

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

Thanks John,

That looks like it will work. Unfortunately, and embarrassingly, I left
out one important part of what I am trying to do. This might be helpful
because the first post was getting long winded as it was.

I additionally want to see a count of the unique CODEs. So, this would
be a count of CODE 1, 2, 3, etc, but not a count of each occurrence -
just the unique codes. If counting CODE by itself, each instance should
return "1", meaning that there is 1 unique CODE. However, when
presenting this under each CATEGORY and CLASS, it should be a count of
all unique CODEs. Example:

If CODE 1 - 2500 are under CLASS 1 and CATEGORY 1, then it should read:

CLASS CATEGORY CODE
1 1 2500

This should be displayed regardless of the fact that there are multiple
entries of the same code with different values, much like I was
describing earlier regarding the dates. So if CODE 1 was in the
database 100 times, as well as CODE 2, CODE 3, etc, the end count would
normally be 2500 * 100 = 250,000 CODEs. I would like a count of all
UNIQUE codes, so that even with 250,000 entries, I still receive the
number 2500.

My apologies for leaving this out earlier.

Thank you!

This sounds like a simple summary query.

SELECT Class, Category, SUM(ReadDiff) as SumOf
FROM YourTable
WHERE [Date] Between #1/1/1900# and #12/31/1900#
GROUP BY Class, Category


In the query grid
-- Put Class, Category, ReadDiff, and Date as fields
-- Enter the date range as criteria under the date field
-- Select View: Totals from the menu
-- Change Group By to Sum under ReadDiff
-- Change Group By to WHERE under the date field.

John Spencer
Access MVP 2002-2005, 2007


Hello,

I have a single table with the following fields:

CLASS
CATEGORY
READDIFF
CODE
DATE

Before I go to far, I should explain one thing: CLASS, CATEGORY, and
CODE are hierarchically related; CODE being between 1 - 10000 and the
lowest child, CATEGORY being the parent of code and having 3
variations, and CLASS being the parent of CATEGORY and having 6
variations. Eventually I would like to display 18 lines of data (6
CLASS with 3 CATEGORY each), with the CODES being grouped together
under each CLASS and CATEGORY and used as unique identifiers to sum
READDIFF on each line. Example:

Note: this data also includes the field DATE, not shown here, which is
explained further below)
CLASS CATEGORY CODE READDIFF
1 1 1 10
1 1 2 15
1 1 3 20
1 2 4 10
1 2 5 7.5
1 2 6 5
1 3 7 10
1 3 8 5
1 3 9 2
2 1 10 10
2 1 11 50
...
6 1 18 75

would be displayed as:

CLASS CATEGORY SumOfREADDIFF
1 1 17
1 2 22.5
1 3 17
2 1 70
...
6 1 75

The above is what I'm trying to achieve.

I need to find some way, either in one query or in a thousand
subqueries, to return what I had outlined above. However, the
additional field of DATE will also need to be used to set boundaries
for summing READDIFF. Ie., sum all READDIFF between 1/1/1900 and
1/1/1901, but exclude anything before or after.

Additionally, there are many instances of the same CODE, as each CODE
has had multiple readings put into the database at different times.
Trying to group together CODE (which seems the simple solution) gives
me problems because each CODE has a unique time and date and also a
different READDIFF. Example:

CODE READDIFF DATE
1 10 1/1/1900
1 5 1/1/1901
1 2 1/1/1902

So trying to do a simple GroupBy on the CODE and a Sum of READDIFF
still results in three unique lines due to the DATE, except where of
course there are two or more DATE that are the same. Unfortunately,
DATE must be included so that the timelines can be established. This
seems to be a flaw (as far as with what I want ot have happen in
access) that constricts me to having to perform a GROUP BY on DATE
since I am performing a SUM on READDIFF and a GROUP BY on CODE. I do
not want to group DATE together, but merely use it as a filter.

Using the simply GROUP BY and SUM functions, I end up with a result
that is approximately 25,000 lines long, rather than the 18 I am
seeking.

Best I can explain in text is to show:


Each unique MRC
Each unique CATEGORY under each MRC
Group together all CODE which are the same
Sum all READDIFF for each grouped CODE as CODEDIFF
Sum all CODEDIFF under each unique MRC and CATEGORY


I've played with using a DISTINCT, but can't construct the query to
work with any success at all.
Thank you one and all to any insight I can be given!