Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-09-2019, 01:56 AM   #1
georg7
Newly Registered User
 
Join Date: Sep 2019
Posts: 16
Thanks: 8
Thanked 0 Times in 0 Posts
georg7 is on a distinguished road
sum + name

Hi there,
I got a query that sums all set values and actual values of every Business ratio. But I can't select the names of my Business ratios. Maybe someone got an idea how to get both ? Everytime I want to select the name I get a warning that my aggregation function won't work with the name selection.

(aa1.jpg is my result and ab1.jpg is my SQL Code. I know that I didn't select the Name. The name is in tbl_kz.name)


Best regards
Georg
Attached Images
File Type: jpg aa1.JPG (13.3 KB, 20 views)
File Type: jpg ab1.JPG (16.8 KB, 21 views)

georg7 is offline   Reply With Quote
Old 09-09-2019, 02:17 AM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,276
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: sum + name

How are tables tbl_all and tbl_kz related?

Name is a reserved word and should not use reserved words as names for anything.

Should copy/paste SQL statements in post instead of image.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Tera (09-09-2019)
Old 09-10-2019, 10:15 PM   #3
georg7
Newly Registered User
 
Join Date: Sep 2019
Posts: 16
Thanks: 8
Thanked 0 Times in 0 Posts
georg7 is on a distinguished road
Re: sum + name

Sorry for the waiting time i've been ill.
c3.jpg is the relation of tbl_all and tbl_kz.
My Goal is that the red circled gets me the Name due tbl_kz and that the blue circled is summed.
It should be like this

tbl_kz tbl_all
KennzahlName Sum(Wert)
Produktivitšt 9.481,78

I tried it with an Group By but it didn't work. I Always get an exception.

SELECT tbl_Kennzahlen.KennzahlName, tbl_all.Wert
FROM tbl_Kennzahlen INNER JOIN tbl_all ON tbl_Kennzahlen.ID = tbl_all.kz_ID
Group By Kennzahlen.KennzahlName;


Best regards
Georg
Attached Images
File Type: jpg c3.JPG (17.8 KB, 6 views)
File Type: jpg c5.JPG (81.7 KB, 6 views)
File Type: jpg c4.JPG (19.6 KB, 5 views)

georg7 is offline   Reply With Quote
Old 09-10-2019, 11:20 PM   #4
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,276
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: sum + name

And what was the exception message?

Have to include all fields in the SELECT in the GROUP BY if field is not an aggregate calc. Not seeing aggregate calc in that query. Blue circle is on Soil field yet it is not referenced in query.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
georg7 (09-10-2019)
Old 09-10-2019, 11:52 PM   #5
georg7
Newly Registered User
 
Join Date: Sep 2019
Posts: 16
Thanks: 8
Thanked 0 Times in 0 Posts
georg7 is on a distinguished road
Re: sum + name

Thank you
I've done it now with
SELECT tbl_Kennzahlen.KennzahlName, tbl_all.kz_ID, Sum(tbl_all.Wert) AS WertALL, Sum(tbl_all.Soll) AS SollALL, tbl_all.Ziel
FROM tbl_Kennzahlen INNER JOIN tbl_all ON tbl_Kennzahlen.ID = tbl_all.kz_ID
WHERE (((tbl_all.grp_ID)=1) AND ((tbl_all.ab_ID)=1))
GROUP BY tbl_Kennzahlen.KennzahlName, tbl_all.kz_ID, tbl_all.Ziel
ORDER BY tbl_all.kz_ID;

You saved me again June7
Best regards
Georg

georg7 is offline   Reply With Quote
Reply

Tags
sum

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 04:27 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World