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
> Sales By Quarter In Report, Access 2016    
 
   
masterYoda16
post Feb 11 2018, 08:15 PM
Post#1



Posts: 30
Joined: 8-January 16



can anyone show me how to make my report so that it shows 1st quarter, 2nd quarter, 3rd quarter and 4th quarter?

I have a sample image of what this report is suppose to look.

I would like to be able to filter the report based on the Date start and date end for each year and the report will automatically fill in each quarterly total sales.


So it will look like this
Date Start: 01/01/2018
Date End: 12/31/2018

1st Quarter 2018 (I would like the year to be a dlookup from the year in the date start)

Sales-----$12,000


2nd Quarter 2018
Sales---$56,900


so on and so forth.

I am slowly learning Access after many years of working with excel, and since there aren't many access developers in my place of work, I thought about jumping ship. So if you can provide me a simple, very simple sample db, I would be more than happy to learn and apply that to my small database that I am currently working on. Access is a different beast, and I am starting to enjoy learning it.

Attached File(s)
Attached File  Wizard.PNG ( 24.48K )Number of downloads: 6
 
Go to the top of the page
 
masterYoda16
post Feb 11 2018, 09:20 PM
Post#2



Posts: 30
Joined: 8-January 16



Sorry not sure where this image came from but here is the correct image.

Thank you all.
This post has been edited by masterYoda16: Feb 11 2018, 09:21 PM
Attached File(s)
Attached File  Sales_by_QTR.zip ( 4.5K )Number of downloads: 7
 
Go to the top of the page
 
RJD
post Feb 11 2018, 10:59 PM
Post#3


UtterAccess VIP
Posts: 8,360
Joined: 25-October 10
From: Gulf South USA


Hi: Pretty straightforward ... just limit the date range, grab the quarter with a format function and group by quarter to sum the sales.

CODE
SELECT Format([SalesDate],"q yyyy") AS Quarter, Sum(tblSales.Sales) AS QuarterSales
FROM tblSales
WHERE (((tblSales.SalesDate) Between #1/1/2018# And #12/31/2018#))
GROUP BY Format([SalesDate],"q yyyy");

If you put the date range on a form, then reference the form dates in the query, you can get the range criteria and also have the date range for displaying in the report.

HTH
Joe
Attached File(s)
Attached File  SalesByQuarter.zip ( 20.3K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
masterYoda16
post Feb 12 2018, 04:06 PM
Post#4



Posts: 30
Joined: 8-January 16



I followed your advice and created a form for the date. What I would like the quarters to be arranged like this.


1st Quarter 2018 2nd Quarter 2018
Total Sales Total Sales



3rd Quarter 2018 4th Quarter 2018
Total Sales Total Sales


I was able to insert the word "Quarter" between the "q" and YYYY". I have no clue how to add 1st. 2nd, 3rd, and 4th and arranged it like this.

Can it be formatted this way? I have the attachment with the form.

Thank you.
Attached File(s)
Attached File  SalesByQuarter.zip ( 43.4K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Feb 12 2018, 08:58 PM
Post#5


UtterAccess VIP
Posts: 8,360
Joined: 25-October 10
From: Gulf South USA


QUOTE
I have no clue how to add 1st. 2nd, 3rd, and 4th and arranged it like this.

Well, you don't have to. Just get the data as a single record (see query below and the revised db) and put the results anywhere you want with any descriptions you want, anywhere you want...

CODE
SELECT
Sum(IIf(Format([SalesDate],"q")=1,[Sales],0)) AS Q1,
Sum(IIf(Format([SalesDate],"q")=2,[Sales],0)) AS Q2,
Sum(IIf(Format([SalesDate],"q")=3,[Sales],0)) AS Q3,
Sum(IIf(Format([SalesDate],"q")=4,[Sales],0)) AS Q4
FROM tblSales
WHERE (((tblSales.SalesDate) Between [forms]![frmReport]![txtStartDate] And [forms]![frmReport]![txtEndDate]));

Looks like you were overthinking this when it is quite simple... The first demo was simple and exactly like you displayed in your first graphic. The second is even simpler displayed as in your report in the db.

HTH
Joe


Attached File(s)
Attached File  SalesByQuarter_Rev1.zip ( 24.9K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
masterYoda16
post Feb 13 2018, 05:02 PM
Post#6



Posts: 30
Joined: 8-January 16



Thank you very much for showing me how to get this working. My other questions are, how to add the corresponding year based on the date picker next to the labels 1stQuarter yyyy, etc.
and how to show the 2 fields InStock and OnOrder.
This post has been edited by masterYoda16: Feb 13 2018, 05:05 PM
Attached File(s)
Attached File  SalesByQuarter_Rev1__2_.zip ( 30.93K )Number of downloads: 6
 
Go to the top of the page
 
RJD
post Feb 13 2018, 06:46 PM
Post#7


UtterAccess VIP
Posts: 8,360
Joined: 25-October 10
From: Gulf South USA


Hi: Well, we'll need more information.

1. Will the date range always be from Jan thru Dec? Or could it be otherwise, such as July thru Jun? Or Oct thru Sep?
2. What values of InStock and OnOrder do you want? Normally, I would not consider these additive - just status at a point-in-time. If they ARE additive, just get them the same way you got Sales. If other than that (as I would personally expect), such as as-of the last record in the quarter, then you have a lot more work to do. What do you want to see here?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
masterYoda16
post Feb 13 2018, 08:07 PM
Post#8



Posts: 30
Joined: 8-January 16



1. Will the date range always be from Jan thru Dec? Or could it be otherwise, such as July thru Jun? Or Oct thru Sep?
This will always go from Jan to Dec for the 12 months per year

2. What values of InStock and OnOrder do you want? Normally, I would not consider these additive - just status at a point-in-time. If they ARE additive, just get them the same way you got Sales. If other than that (as I would personally expect), such as as-of the last record in the quarter, then you have a lot more work to do. What do you want to see here?


Per quarters as in 1st Quarter yyyy 2nd Quarter yyyy(lookup from the date picker yyyy)
Sales (sum) Sales (sum)
Instock (sum) InStock(sum)
OnOrder (sum) OnOrder(sum)

3rd Quarter yyyy 4th Quarter yyyy (dlookup the date picker yyyy)
Sales (sum) Sales (sum)
Instock (sum) InStock (sum)

OnOrder (sum) OnOrder (sum)

I hope these make sense.
Go to the top of the page
 
RJD
post Feb 13 2018, 11:33 PM
Post#9


UtterAccess VIP
Posts: 8,360
Joined: 25-October 10
From: Gulf South USA


Hi: The query already shows you how to sum a value by quarter (sales). Just do the same for the other two values for each quarter and put them on the report where you want them. I am still not sure about the definitions of the other two values, and why they are summed, but if that is what you want, then you already have a model for doing that. You should become familiar with the IIf statements in the query and repeat them for the other values.

And since the date range is always the calendar year, you can get the year from the control on the form using the Year function, and place it where you want it on the report. In fact, since you are dealing with a calendar year, you need not have a start and end date - just a year. Then use the year value to limit the query content - for example, WHERE Year([SalesDate]) = Forms!frmReport!ReportYear ... where ReportYear is the new form control.

As a database developer, these are basic skills, and you should study the demo and read about Access techniques to develop your skills.

HTH
Joe


--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st June 2018 - 06:48 PM