Clumping prior year, detail current

[DalGal]

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

 

[plog]

Most likely you will use a subquery. But it may just be a DSUM(https://www.techonthenet.com/access/functions/domain/dsum.php) if you simply need just 1 value.

Can you create a simple mock up of what you want the report to look like. Either in the forum itself or upload an Excel file.

 

[DalGal]

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]

I've never used the union query. I'll give it a try! Thanks!

 

[plog]

There's no file

 

[DalGal]

See if this works

 

[plog]

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]

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

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

 

[DalGal]

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]

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.

 

[DalGal]

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!