Ok, i have a Access Database (which is used with my ESRI Arcmap) and i
have written a query for a report to pull out our streetlights and
group them by billing wattage. The problem is that it is counting the
number of lights and summing them like i have asked, but in some cases
there are more then one row with a different sum but the same billing
wattage. Here is my SQL query. I'm trying to group by billing wattage
so all the unique billing wattages are together. Thanks in advance!
SELECT POLE.BILLING_WA, POLE.ST_LITE_TY, Sum(POLE.QTY_LIGHTS) AS
SumOfQTY_LIGHTS, POLE.TOWN, Streetlight_Accounts.Account
FROM Streetlight_Accounts RIGHT JOIN POLE ON Streetlight_Accounts.Town
= POLE.TOWN
GROUP BY POLE.BILLING_WA, POLE.ST_LITE_TY, POLE.TOWN,
Streetlight_Accounts.Account, POLE.ST_LTOWNER, POLE.QTY_LIGHTS
HAVING (((POLE.TOWN)<>'LOB' And (POLE.TOWN)<>'port sy') AND
((POLE.ST_LTOWNER)<>'MTO') AND ((POLE.QTY_LIGHTS)>="1"))
ORDER BY POLE.BILLING_WA;
Craig
6  6950 
Craig,
The issue is most likely that you have a difference in one of the other
grouped fields that is causing you to retuen multiple rows for the same
billing wattage. If you have two different ST_LITE_TY, you will get
two rows even if BILLING_WA is the same for each. To eliminate this
type of problem, you have to remove any other extraneous field values
that are not pertinent to your query. If you don't really want
differentiation by ST_LITE_TY or some of your other fields, like TOWN
ro Account, then you should remove these from the query SELECT
statement and GROUP BY clause and use a WHERE clause rather than a
HAVING clause to limit the results by those fields removed from the
SELECT clause. This change should give you the result you want without
additional groupings.
- GH
On Nov 29, 10:46 am, craig.buchin...@gmail.com wrote:
Ok, i have a Access Database (which is used with my ESRI Arcmap) and i
have written a query for a report to pull out our streetlights and
group them by billing wattage. The problem is that it is counting the
number of lights and summing them like i have asked, but in some cases
there are more then one row with a different sum but the same billing
wattage. Here is my SQL query. I'm trying to group by billing wattage
so all the unique billing wattages are together. Thanks in advance!
SELECT POLE.BILLING_WA, POLE.ST_LITE_TY, Sum(POLE.QTY_LIGHTS) AS
SumOfQTY_LIGHTS, POLE.TOWN, Streetlight_Accounts.Account
FROM Streetlight_Accounts RIGHT JOIN POLE ON Streetlight_Accounts.Town
= POLE.TOWN
GROUP BY POLE.BILLING_WA, POLE.ST_LITE_TY, POLE.TOWN,
Streetlight_Accounts.Account, POLE.ST_LTOWNER, POLE.QTY_LIGHTS
HAVING (((POLE.TOWN)<>'LOB' And (POLE.TOWN)<>'port sy') AND
((POLE.ST_LTOWNER)<>'MTO') AND ((POLE.QTY_LIGHTS)>="1"))
ORDER BY POLE.BILLING_WA;
Craig
BILLING_WA ST_LITE_TY SumOfQTY_LIGHTS TOWN Account
0 MV 3
BB 72414
0 MV 2 BB 72414
Here is a snipit of the output... i don't understand why it's not
grouping the MV's and adding the sum of lights together.
Craig
GH wrote:
Craig,
The issue is most likely that you have a difference in one of the other
grouped fields that is causing you to retuen multiple rows for the same
billing wattage. If you have two different ST_LITE_TY, you will get
two rows even if BILLING_WA is the same for each. To eliminate this
type of problem, you have to remove any other extraneous field values
that are not pertinent to your query. If you don't really want
differentiation by ST_LITE_TY or some of your other fields, like TOWN
ro Account, then you should remove these from the query SELECT
statement and GROUP BY clause and use a WHERE clause rather than a
HAVING clause to limit the results by those fields removed from the
SELECT clause. This change should give you the result you want without
additional groupings.
- GH
On Nov 29, 10:46 am, craig.buchin...@gmail.com wrote:
Ok, i have a Access Database (which is used with my ESRI Arcmap) and i
have written a query for a report to pull out our streetlights and
group them by billing wattage. The problem is that it is counting the
number of lights and summing them like i have asked, but in some cases
there are more then one row with a different sum but the same billing
wattage. Here is my SQL query. I'm trying to group by billing wattage
so all the unique billing wattages are together. Thanks in advance!
SELECT POLE.BILLING_WA, POLE.ST_LITE_TY, Sum(POLE.QTY_LIGHTS) AS
SumOfQTY_LIGHTS, POLE.TOWN, Streetlight_Accounts.Account
FROM Streetlight_Accounts RIGHT JOIN POLE ON Streetlight_Accounts.Town
= POLE.TOWN
GROUP BY POLE.BILLING_WA, POLE.ST_LITE_TY, POLE.TOWN,
Streetlight_Accounts.Account, POLE.ST_LTOWNER, POLE.QTY_LIGHTS
HAVING (((POLE.TOWN)<>'LOB' And (POLE.TOWN)<>'port sy') AND
((POLE.ST_LTOWNER)<>'MTO') AND ((POLE.QTY_LIGHTS)>="1"))
ORDER BY POLE.BILLING_WA;
Craig
<cr*************@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...
Ok, i have a Access Database (which is used with my ESRI Arcmap) and i
have written a query for a report to pull out our streetlights and
group them by billing wattage. The problem is that it is counting the
number of lights and summing them like i have asked, but in some cases
there are more then one row with a different sum but the same billing
wattage. Here is my SQL query. I'm trying to group by billing wattage
so all the unique billing wattages are together. Thanks in advance!
Grouping in a report always overrides the query. Use sorting and grouping
in the report.
Regards,
Keith. www.keithwilby.com
There is no way to group these records in the query?
Craig
Keith Wilby wrote:
<cr*************@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...
Ok, i have a Access Database (which is used with my ESRI Arcmap) and i
have written a query for a report to pull out our streetlights and
group them by billing wattage. The problem is that it is counting the
number of lights and summing them like i have asked, but in some cases
there are more then one row with a different sum but the same billing
wattage. Here is my SQL query. I'm trying to group by billing wattage
so all the unique billing wattages are together. Thanks in advance!
Grouping in a report always overrides the query. Use sorting and grouping
in the report.
Regards,
Keith.
www.keithwilby.com
If I were to hazard a guess, one of these two field groupings is
creating your record differences: POLE.ST_LTOWNER, POLE.QTY_LIGHTS.
Since you are summing QTY_LIGHTS, why are you grouping on it?
- GH
On Nov 29, 11:30 am, craig.buchin...@gmail.com wrote:
There is no way to group these records in the query?
Craig
Keith Wilby wrote:
<craig.buchin...@gmail.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...
Ok, i have a Access Database (which is used with my ESRI Arcmap) and i
have written a query for a report to pull out our streetlights and
group them by billing wattage. The problem is that it is counting the
number of lights and summing them like i have asked, but in some cases
there are more then one row with a different sum but the same billing
wattage. Here is my SQL query. I'm trying to group by billing wattage
so all the unique billing wattages are together. Thanks in advance!
Grouping in a report always overrides the query. Use sorting and grouping
in the report.
Regards,
Keith.
www.keithwilby.com- Hide quoted text -- Show quoted text -
Thanks a lot.. Got it working..i can't believe i missed that..
Thanks again
Craig..
GH wrote:
If I were to hazard a guess, one of these two field groupings is
creating your record differences: POLE.ST_LTOWNER, POLE.QTY_LIGHTS.
Since you are summing QTY_LIGHTS, why are you grouping on it?
- GH
On Nov 29, 11:30 am, craig.buchin...@gmail.com wrote:
There is no way to group these records in the query?
Craig
Keith Wilby wrote:
<craig.buchin...@gmail.comwrote in message
>news:11**********************@j72g2000cwa.googleg roups.com...
Ok, i have a Access Database (which is used with my ESRI Arcmap) and i
have written a query for a report to pull out our streetlights and
group them by billing wattage. The problem is that it is counting the
number of lights and summing them like i have asked, but in some cases
there are more then one row with a different sum but the same billing
wattage. Here is my SQL query. I'm trying to group by billing wattage
so all the unique billing wattages are together. Thanks in advance!
Grouping in a report always overrides the query. Use sorting and grouping
in the report.
Regards,
Keith.
>www.keithwilby.com- Hide quoted text -- Show quoted text -
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Paxton |
last post by:
Hi,
I'm trying to display the total page views per page within a given date
range, but the correct SQL is seemingly beyond me. I get the correct result
with a straightforward Group By and Count...
|
by: Robert |
last post by:
Hello Accessors
I have some reports created in Access that are very good for what they do.
However, it seems to me that when you are displaying information you don't
need to print out that a...
|
by: Bob |
last post by:
Hi Everybody
Difficult question
Has anyone else used the "Using the Tab control as a container"
database example that comes with the above book chapter 7 on the
accompanying disc. It is a...
|
by: Mike MacSween |
last post by:
tblCourses one to many to tblEvents.
A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the
future.
At...
|
by: ApexData |
last post by:
I have one table used to maintain information on Service Events.
Each record contains a Repair, Maint, and Battery checkbox
(bound/boolean). Any one of these fields may be checked or left empty....
|
by: Hexman |
last post by:
Hello All,
How do I limit the number of detail records selected in a Master-Detail set using SQL?
I want to select all master records for a date, but only the first 3 records for the details...
|
by: lupo666 |
last post by:
Hi everybody, this time I have three problems driving me nuts :-(((
(1)
I have a report with 20 or so Yes/No "squares". Is there a way to
either hide/show the "square" or change the yes/no...
|
by: prakashwadhwani |
last post by:
Hi !! I'm about to develop a new project for a client.
Should I go about it in Access 2003 or 2007 ? Purchasing it either
for me or for my client is not a major consideration here ... what I'd...
|
by: zandiT |
last post by:
hello everyone
when working with access reports using the grouping sections, how do you change the title of the group header section depending on which group is being displayed. i have a report...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
