Totaling Columns

[BethanyM]

Hi,

I am working on a query where I want to see quarterly data. The original tables are set up so that they show sales, transactions, amount, etc. by month.

What I need to do is group the data by quarter and then sum up that data. I was able to pretty easily create a query that shows each quarter but I have not been able to sum up that data.

I need the data to be summed up for each quarter for each year so I can then create formulas in a report that shows the difference in performance Q/Q as well as Y/Y.

I tried using things like DSUM, etc. but have not been able to work.

Here is the SQL for LY Q3

SELECT access_dashboard_monthly.qb_merchant_id, access_dashboard_monthly.date_start, access_dashboard_monthly.clicks, access_dashboard_monthly.impressions, access_dashboard_monthly.orders, access_dashboard_monthly.leads, access_dashboard_monthly.sales, access_dashboard_monthly.sales_gross, access_dashboard_monthly.commission, access_dashboard_monthly.sas_fee, access_dashboard_monthly.act_aff, access_dashboard_monthly.total_aff, access_dashboard_monthly.fts, access_dashboard_monthly.aov, Year([date_start]) AS YrNum
FROM access_dashboard_monthly
WHERE (((Month([date_start]))=7)) OR (((Month([date_start]))=8)) OR (((Month([date_start]))=9))
GROUP BY access_dashboard_monthly.qb_merchant_id, access_dashboard_monthly.date_start, access_dashboard_monthly.clicks, access_dashboard_monthly.impressions, access_dashboard_monthly.orders, access_dashboard_monthly.leads, access_dashboard_monthly.sales, access_dashboard_monthly.sales_gross, access_dashboard_monthly.commission, access_dashboard_monthly.sas_fee, access_dashboard_monthly.act_aff, access_dashboard_monthly.total_aff, access_dashboard_monthly.fts, access_dashboard_monthly.aov, Year([date_start])
HAVING (((access_dashboard_monthly.qb_merchant_id)=[Forms]![***RunReportsForm***]![PickMerchantID]) AND ((Year([date_start]))=2013))


ORDER BY access_dashboard_monthly.date_start DESC;


With this formula I easily, can get the data for LYQ3 but once I get that I am not sure how to sum it. Ideally I would like to sum each quarter over multiple years and then create master queries that would then show me all of that data soI can build out formulas to capture the Q/Q and Y/Y performance.

Thanks in advance for any help you may be able to provide.

[dipique]

If you're doing this in access, the "Report" functionality may be ideal for you. If not, try posting your database so we can better visualize the issue.

[BethanyM]

Hi,

How would I go about posting my database? I know I can sum an individual quarter in the Access report. But the issue is that that only covers let's say the present quarter and in reality I need a query that lists the data broken down individually by quarter. So for instance This Year Quarter 3 would just be (July-September) of this year summed. But I also need that query to show me what This Year Quarter 2 is as well as Last Year Quarter 3. My problem is I can get any one of the three to work properly in a query but not all 3.

[June7]

Construct fields in query that can be used to organize the records by query/year, month/year:

SELECT *, Format([date field],"yyyyq") AS YrQtr, Format([date field]), "yyyymm") As YrMo FROM tablename;

or

SELECT *, Year([date field]), DatePart("q",[date field]) AS Qtr, Month([date field]) As Mon FROM tablename;

Now use that query as source for subsequent queries.

Might find this of interest: http://allenbrowne.com/subquery-01.html#YTD

To provide db, follow instructions at bottom of my post.

[BethanyM]

I have included a YrNum, MoNum, QtrNum to be able to restrict data. Ex. Year([date_start]) AS YrNum

I use a where statement to restrict the quarter currently. My issue is that I can clearly see which values belong to which quarter, I am just not sure how I can total them to be able to display in the report.

My db even with removing sensitive data is over 2MB, so I cannot attach.

[June7]

Zip is over 2MB? Can upload to a fileshare site such as Box.com and post link to the file.

Use aggregate (GROUP BY) totals query.

Or as dipique suggested, build a report and use its Grouping & Sorting features with aggregate calcs in footers. This will allow display of detail records as well as summary calcs. If you want a prior year month or quarter next to current month or query, review the Allen Browne tutorial I referenced.

Maybe even report/subreport will be needed to get desired output.

[BethanyM]

Sorry about the delay but here is my database. What I am trying to do is have the report that is the subreport populate this qtr's data, last qtr's data, and q/q change right now. In order for this to work, I have created a query where I put in time variables QTR, YRNum, etc. and I have added in sorting and grouping on the report. My issue is that it shows me the data for this year's qtr and last year's qtr but it is not in the same chart.Test4Forum.zip

I've also considered using DSUM or DLookup to populate the other columns but I am receiving errors. Here is what I input into the field: =DSum([clicks],[QryQ3DataAll2],[YrQtr]=2013-3)

I also added in the YrQtr that was suggested just now and there isn't a real difference (i.e. the first column of my table works but not the others)

[BethanyM]

I got the Dsum to work so I can now populate the rest of the table. The only issue now is that I only want it to show me one table but since it sees that there are numerous records it is trying to display them. Any idea on how to restrict it from creating additional tables?

[June7]

Your table is a link (MySQL, Oracle?) so I cannot test. You have to provide db with imported data.

I don't understand your last post. I thought the issue was building a query. When did populating a table enter the picture? What is 'creating additional tables'?

[BethanyM]

I solved it. I created a query then created another query based off of that where I used DSum to calculate the quarterly totals.