Solved Totals Yearly Query (1 Viewer)

ypma

Registered User.
Local time
Today, 15:36
Joined
Apr 13, 2012
Messages
643
Constructive advise would be appreciated for my question . I have created 3 queries . Query 1: counts Source = Product Transfer yearly. Query 2: counts Source "Not Product Transfer" yearly. Query 3 is the combined result of query 1 and 2 . Is there a more efficient way to produce the results in query 3 ? I have looked at Cross tab query , however my experience with them is limited .

I have attached a demo of what I am attempting to achieve .

Regards Ypma
 

Attachments

  • DemoCountQuery.accdb
    420 KB · Views: 134

namliam

The Mailman - AWF VIP
Local time
Today, 16:36
Joined
Aug 11, 2003
Messages
11,696
Code:
TRANSFORM Count([personal Detaill new].[Client  ID]) AS [AantalVanClient  ID]
SELECT Year([DateCompleted]) AS Expr1
FROM [personal Detaill new]
GROUP BY Year([DateCompleted])
PIVOT [personal Detaill new].Source;
There are a few ways of doing this, but there are so many issues here ...
 

ypma

Registered User.
Local time
Today, 15:36
Joined
Apr 13, 2012
Messages
643
Namliam, Thank you very much for you response, its not quite the result I had in mind. As per my demo I only require two two totals per year . Example total for PR and total for Not PR , I hope this makes sense . If you would be so kind as to modify you SQL script so it reflect my result in qryYearlyTotPRnotPR of the demo . I fully accept there some bad practices in the demo and will address them in due course .

Thanks again

Ypma
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:36
Joined
Sep 21, 2011
Messages
14,044
Could you not do it all in one query by classing another field as which type, then transform on that.?

Code:
SELECT Count([personal Detaill new].[Client  ID]) AS [P T], Year([DateCompleted]) AS [Year completed], [personal Detaill new].Source, IIf([source]="Product Transfer","PR","NPR") AS PRStatus
FROM [personal Detaill new]
GROUP BY Year([DateCompleted]), [personal Detaill new].Source, IIf([source]="Product Transfer","PR","NPR");

HTH
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:36
Joined
Aug 11, 2003
Messages
11,696
Namliam, Thank you very much for you response, its not quite the result I had in mind. As per my demo I only require two two totals per year . Example total for PR and total for Not PR , I hope this makes sense . If you would be so kind as to modify you SQL script so it reflect my result in qryYearlyTotPRnotPR of the demo . I fully accept there some bad practices in the demo and will address them in due course .

Thanks again

Ypma
Best way to address that issue is to add a related table, to "translate" your sources into 2 sources and use the related table to pivot on.

Another way is to implement a simple IIF() simular to the one @Gasman is using
 

ypma

Registered User.
Local time
Today, 15:36
Joined
Apr 13, 2012
Messages
643
Could you not do it all in one query by classing another field as which type, then transform on that.?

Code:
SELECT Count([personal Detaill new].[Client  ID]) AS [P T], Year([DateCompleted]) AS [Year completed], [personal Detaill new].Source, IIf([source]="Product Transfer","PR","NPR") AS PRStatus
FROM [personal Detaill new]
GROUP BY Year([DateCompleted]), [personal Detaill new].Source, IIf([source]="Product Transfer","PR","NPR");

HTH
Gasman, your SQL is almost there , however I need the Not PT sources to be counted as a whole and the PT sources separately.
see below
Desired out come ,without the dashes .
Year--- Not PT----- P T

2019 ----1----- ---- 2

2020 ---- 6 ------ 2

I really like your script and have learn a lot from it for future use , without appearing to be ungrateful could you adjust your SQL to mirror my desired outcome.

Regards Ypma
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:36
Joined
Sep 21, 2011
Messages
14,044
I just gave you a copy of the SQL from the SQL GUI.
You still need to make a Crosstab of that data.?

I have not used Crosstab much, but I will have a go. You can change the column headings as you wish.

Code:
TRANSFORM Count(qryCountProductTransfer.[P T]) AS [CountOfP T]
SELECT qryCountProductTransfer.[Year completed], Count(qryCountProductTransfer.[P T]) AS [Total Of P T]
FROM qryCountProductTransfer
GROUP BY qryCountProductTransfer.[Year completed]
PIVOT qryCountProductTransfer.PRStatus;

All done using the query GUI
I just took the ProductTransfer query and adjusted to suit, so you might want to change the names as well?
 

Attachments

  • DemoCountQuery.zip
    27.9 KB · Views: 139
Last edited:

ypma

Registered User.
Local time
Today, 15:36
Joined
Apr 13, 2012
Messages
643
Gasman, you have made a octogenarian a happy bunny , I am nearly at my desire outcome and will work with your solution. Many thanks

Regards Ypma
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:36
Joined
Sep 21, 2011
Messages
14,044
Gasman, you have made a octogenarian a happy bunny , I am nearly at my desire outcome and will work with your solution. Many thanks

Regards Ypma
If you just change the text strings for PRStatus to your names, you should have what you wanted?
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:36
Joined
Aug 11, 2003
Messages
11,696
Major drawdown here is the fact that things are hardcoded, hardcoding things is an accident waiting to happen... much like many things in this example. Sooner or later the same logic will appear in different places, and sooner or later the logic will change ... and come more complex times come more time down the road at some point things will go out of synch.

You would be much better served having a { source } table to "roll up" your specific source to a reporting source.... this will allow you to work with different groups, sources and reporting sources when in the future the requirements change....
As such, using a crosstab is "creating" columns from values... which is more of an excel thing to do unless you are displaying it as a table view on a form or report or exporting it as is.... making a normal "group by" query with the data in rows (not columns) is the way to go.
 

ypma

Registered User.
Local time
Today, 15:36
Joined
Apr 13, 2012
Messages
643
Major drawdown here is the fact that things are hardcoded, hardcoding things is an accident waiting to happen... much like many things in this example. Sooner or later the same logic will appear in different places, and sooner or later the logic will change ... and come more complex times come more time down the road at some point things will go out of synch.

You would be much better served having a { source } table to "roll up" your specific source to a reporting source.... this will allow you to work with different groups, sources and reporting sources when in the future the requirements change....
As such, using a crosstab is "creating" columns from values... which is more of an excel thing to do unless you are displaying it as a table view on a form or report or exporting it as is.... making a normal "group by" query with the data in rows (not columns) is the way to go.
Dank je wel Namliam for you comments, the demo was just a database to demonstrate the the question I was posing. The values would appear in a management report. As you can see I am not a professional programmer and only assist if asked by family members. For this project I will stick with Gasman's solution , but bare in mind your comments .
Regard Ypma
 

Users who are viewing this thread

Top Bottom