UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Not Part Of Aggregate Function?, Access 2016    
 
   
jackjsmith88
post Jun 17 2019, 05:45 AM
Post#1



Posts: 45
Joined: 28-October 18



Hi All,

Trying to design a query that gives me data based on criteria entered on a form.

The form is a start date and end date for query to select everything in range of specified dates.

SELECT WeeklyTransportReport.DateofSale, DriverName.Driver, Sum([WeeklyTransportReport]![Dekton04mm]+[WeeklyTransportReport]![Dekton08mm]+[WeeklyTransportReport]![Dekton12mm]+[WeeklyTransportReport]![Dekton20mm]+[WeeklyTransportReport]![Dekton30mm]) AS TotalofDekton, Sum([WeeklyTransportReport]![Sensa/Granite20mm]+[WeeklyTransportReport]![Sensa/Granite30mm]) AS TotalOfSensa, Sum([WeeklyTransportReport]![Sink51x41]+[WeeklyTransportReport]![Sink34x37]+[WeeklyTransportReport]![Sink51x37]) AS TotalSinks, WeeklyTransportReport.TotalSlabs
FROM DriverName INNER JOIN WeeklyTransportReport ON DriverName.ID = WeeklyTransportReport.DriverName
WHERE (((WeeklyTransportReport.DateofSale) Between [Forms]![QryDateParameterSTATS]![QryStartDate] And [Forms]![QryDateParameterSTATS]![QryEndDate]));


This is the statement its generated but it keeps returning me the error "DateofSale is not part of aggregate function?

Please can someone help me? or explain what it is im doing wrong?

Many Thanks
Go to the top of the page
 
nuclear_nick
post Jun 17 2019, 05:54 AM
Post#2



Posts: 1,758
Joined: 5-February 06
From: Ohio, USA


The 'aggregate' queries are queries where the information is grouped, summed, or had some other such function performed. You can make a query an aggregate query by clicking the Greek 'E' on the ribbon in the query design grid.

In more plain language...

CODE
Sum([WeeklyTransportReport]![Dekton04mm]+[WeeklyTransportReport]![Dekton08mm]+[WeeklyTransportReport]![Dekton12mm]+[WeeklyTransportReport]![Dekton20mm]+[WeeklyTransportReport]![Dekton30mm]) AS TotalofDekton, Sum([WeeklyTransportReport]![Sensa/Granite20mm]+[WeeklyTransportReport]![Sensa/Granite30mm]) AS TotalOfSensa, Sum([WeeklyTransportReport]![Sink51x41]+[WeeklyTransportReport]![Sink34x37]+[WeeklyTransportReport]![Sink51x37]) AS TotalSinks


… you cannot use 'Sum' without doing something with the fields in the query that are not 'summed'. (Or you can write 'sub-queries' to sum the information you want summed as well.)

Does that help?

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
Phil_cattivocara...
post Jun 17 2019, 05:54 AM
Post#3



Posts: 302
Joined: 2-April 18



You are using Sum() without GROUP BY. If you only need to sum fields you do not need Sum, only ([field1] + [field2] + ... [fieldn]) as Something

(too late... nucleark_nick clicks and types faster than me)
This post has been edited by Phil_cattivocarattere: Jun 17 2019, 05:57 AM

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
nuclear_nick
post Jun 17 2019, 05:55 AM
Post#4



Posts: 1,758
Joined: 5-February 06
From: Ohio, USA


Okay, it may not be a Greek 'E'... I don't know what it is, actually.

tongue.gif

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
Phil_cattivocara...
post Jun 17 2019, 06:02 AM
Post#5



Posts: 302
Joined: 2-April 18



QUOTE (nuclear_nick)
it may not be a Greek 'E'... I don't know what it is, actually.

I did not know me too, I got curious so: it is a capital Greek letter sigma (wikipedia: Summation)


--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
nuclear_nick
post Jun 17 2019, 06:22 AM
Post#6



Posts: 1,758
Joined: 5-February 06
From: Ohio, USA


Ha! Knew it was Greek, anyway.

That's my new thing I learned today... I can go home now?

Oh. Yeah. Still work for a living. I tried.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
BruceM
post Jun 17 2019, 07:12 AM
Post#7


UtterAccess VIP
Posts: 7,898
Joined: 24-May 10
From: Downeast Maine


TotalSlabs would also need to be grouped, but note that if you group on those values you will have the sum data for each value, not for the entire time range. In general, a cleaner approach may be to have the sales data in a related table. As things stand, if you add or remove a size you will need to rewrite the query.
Go to the top of the page
 
GroverParkGeorge
post Jun 17 2019, 07:27 AM
Post#8


UA Admin
Posts: 35,304
Joined: 20-June 02
From: Newcastle, WA


Yes, you have the answer to the immediate problem.

The general syntax for an aggregate query is:

SELECT Field1, Field2, Sum(Field3)
FROM table1
WHERE table1.Field1 = SomeCriterion
GROUP BY Field1, Field2


However, as Bruce points out, your table design is not appropriate. You have what is commonly called Repeating Groups ("Dekton04mm", "Dekton08mm", etc.). This is a problem we often see when a "spreadsheet" approach is taken rather than a relational database approach to creating tables.

I strongly urge you to step back, learn how proper Normalization works and how to use it to correct this table design problem. Here's a good series of blog articles to help you do that.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
jackjsmith88
post Jun 17 2019, 07:31 AM
Post#9



Posts: 45
Joined: 28-October 18



Thanks for your help and advice guys.
Go to the top of the page
 
cheekybuddha
post Jun 17 2019, 08:24 AM
Post#10


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


Actually, looking at your SQL, I don't think you need to aggregate (or at least you could do it a later stage).

Perhaps just try:
CODE
SELECT
  r.DateofSale,
  d.Driver,
  (r.[Dekton04mm] + r.[Dekton08mm] + r.[Dekton12mm] + r.[Dekton20mm] + r.[Dekton30mm]) AS TotalofDekton,
  (r.[Sensa/Granite20mm] + r.[Sensa/Granite30mm]) AS TotalOfSensa,
  (r.[Sink51x41] + r.[Sink34x37] + r.[Sink51x37]) AS TotalSinks,
  r.TotalSlabs
FROM DriverName d
INNER JOIN WeeklyTransportReport r
        ON d.ID = r.DriverName
WHERE
  r.DateofSale Between [Forms]![QryDateParameterSTATS]![QryStartDate]
                   And [Forms]![QryDateParameterSTATS]![QryEndDate]
;

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 17 2019, 08:26 AM
Post#11


UtterAccess VIP
Posts: 11,417
Joined: 6-December 03
From: Telegraph Hill


That said, you really ought to try and fix the structure if you can.

--------------------


Regards,

David Marten
Go to the top of the page
 
gemmathehusky
post Jun 17 2019, 10:35 AM
Post#12


UtterAccess VIP
Posts: 4,722
Joined: 5-June 07
From: UK


note that capital Sigma is the sign used for Integration (integral calculus), the sum of the deltas within the range, so it's a good short-cut choice.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
gemmathehusky
post Jun 17 2019, 11:01 AM
Post#13


UtterAccess VIP
Posts: 4,722
Joined: 5-June 07
From: UK


Just to clarify the original error

in the QBE window,
if it's not visible, then it's EITHER
WHERE, and it shows as WHERE in the SQL
OR
EXPRESSION, and it shows as HAVING in the SQL

but if the field is visible, then it needs to be "GROUPBY", AND "HAVING"

Your issue, is that you have WHERE .. date, but the date itself is also in the query without having a domain function (eg Groupby)


I find it easier to design queries in the QBE window, and then see what the SQL looks like!





--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th July 2019 - 05:29 AM