Clumping prior year, detail current (1 Viewer)

DalGal

Registered User.
Local time
Today, 05:33
Joined
Oct 23, 2018
Messages
23
I'm fairly new to Access in the fact it's completely self-taught, and I'm not familiar with coding it. I'm needing a report to show the total of prior year transactions, then list individually current year transactions. I created a query to sum prior year, but then how do I add current year. Can I create 2 queries and merge them? It will be an ongoing year after year report, and we need to be able to pull up any year (showing prior as a sum, then current as detailed).

My fields are: Effective date, purchased units, purchase price, vendor
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:33
Joined
Feb 19, 2002
Messages
42,970
When you need multiple levels of summation, the easiest solution is to use a union query.

In the union's first select, use a query that summarizes the prior year data. Make sure to create a dummy sequence number (or date) that will cause this record to sort first in the report. In the second query, select the detail records for the current year. Use the report's sorting and grouping options to break on year so you start with prior year and then move into the current year data.
 

DalGal

Registered User.
Local time
Today, 05:33
Joined
Oct 23, 2018
Messages
23
I just uploaded an Excel of the information I need. Prior year as 1 total by vendor. Current year detailed by exact date, then a grand total (which will be easy once I have the other 2). It doesn't have to be this format, just have the same info.
 

DalGal

Registered User.
Local time
Today, 05:33
Joined
Oct 23, 2018
Messages
23
I've never used the union query. I'll give it a try! Thanks!
 

DalGal

Registered User.
Local time
Today, 05:33
Joined
Oct 23, 2018
Messages
23
See if this works
 

Attachments

  • test1.xlsx
    10.2 KB · Views: 71

plog

Banishment Pending
Local time
Today, 05:33
Joined
May 11, 2011
Messages
11,611
That's not going to be easily achievable in an Access Report. Access Reports are better structured vertically than horizontally. I suggest having your vendor data appear vertically and having just Shares, & and Contrib being the columns.

Also, your layout doesn't designate where the prior year value will appear. Can you tell me where that would go?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:33
Joined
Feb 19, 2013
Messages
16,553
an alternative to a union query is to use an aggregate query with a conditional clause - but will only work if the table has a unique field (typically an autonumber field).

query might look something like this

Code:
SELECT vendor, 
    iif(fldDate<#01/01/2020#,0,tblID) as tranID,
    iif(fldDate<#01/01/2020#,#12/31/2019#,fldDate) as tranDate,
    sum(tranValue) as ttlValue
FROM myTable
GROUP BY vendor, 
    iif(fldDate<#01/01/2020#,0,tblID)
ORDER BY Vendor, 
    iif(fldDate<#01/01/2020#,#12/31/2019#,fldDate)

edit:it may be a union query is more efficient, you would have to try it and see
edit2: agree with plog, for access put vendors and dates down the side and transaction types across the top. You would need two crosstabs which you can join together on vendor in another query, one for shares, the other for contributions. Totals and percentages could be managed with unbound controls in your form or report
 
Last edited:

DalGal

Registered User.
Local time
Today, 05:33
Joined
Oct 23, 2018
Messages
23
I can do that, and the % is just a calculated cell as well as the total current. It can be vertical or horizontal as long as it has all the info.

Prior year (actually all prior activity is added together), then current by date, then a grand total of each vendor and over-all.
 

DalGal

Registered User.
Local time
Today, 05:33
Joined
Oct 23, 2018
Messages
23
Other note is I have centers. Each center has vendors. Each vendor has units and contributions. Each entry of units and/or contributions has a date.
Tables are: Centers, Vendors, Activity
Centers have a unique ID
Vendors have a unique ID
dates, units, and contributions are not unique across the board.
I need to bring up any date and see the balance as of that date, which I've been able to do in a report, but just the sum or just the details.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:33
Joined
Feb 19, 2002
Messages
42,970
Take a stab at the union query. Union queries must be built as SQL Strings. If you are more comfortable using the QBE, then start by building two separate queries.
q1 - Summarize last year (or all previous years). Use criteria so you don't have to modify the query every year.
q2 - Select current year detail. Use criteria so you don't have to modify the queryevery year.

Now build the union:

Select * from q1
Union Select * from q2;

That's easy enough to type.

One thing to remember is that Union queries REQUIRE that all embedded queries have the exact same format. Column names are not relevant but column order is so be very careful that each query selects the same columns in the same order. In this case, q1 will not select the detail fields that q2 will select so you need to create dummies to account for them, just be careful about data types:

q1:
Select Forms!yourform!PrevYear as PrevYear, "" as fld1, 0 as fld2, ..... From YourTable

Change to a totals query so access will add the group by and you can change the group by to sum where needed.

The sample you posted is typical spreadsheet think. If you have only a half dozen vendors, you can probably use a crosstab to make the report show vendors across but this is the road to perdition. Just go vertical and save yourself the hassle of having to redo everything if you get one more vendor than will fit neatly on the report.

You are also adding additional requirements. Start simple - with the union query and we can go from there.
 

DalGal

Registered User.
Local time
Today, 05:33
Joined
Oct 23, 2018
Messages
23
Pat-I will try that-good info! I didn't know about the order and dummy accounts. I have both of those queries set up already. I've never done a union query so I'll look at that.
Not sure where the thank you button is but Thanks!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:33
Joined
Feb 19, 2002
Messages
42,970
You're welcome.

The "thanks" is the thumbs up button.
 

Users who are viewing this thread

Top Bottom