UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Expressions In A Query, Any Version    
 
   
zaxbat
post Feb 26 2018, 10:19 AM
Post#21



Posts: 952
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Right! Data storage is only data storage....good to do it right so the app works well.....but the user need never see the data storage.
The reports, and sometimes forms, are what the user sees...and they are pretty darn powerful in Access. So, don't worry about the rows for now....can always group the data later using various approaches.

Whenever I start making an app for somebody...I always ask them what the report(s) needs to look like.....that well pretty much determine everything else. So, what does your report need to look like? ha ha ha ha ha

Oh....wait a minute....you don't want a report from access do you....you want a table that you can drag back over to excel where this all started. Please say it ain't so.....

Well, that's doable too. Just a few more queries to group and sum the data and it will be the nice table with one row per customer (so excel can handle it).
This post has been edited by zaxbat: Feb 26 2018, 10:27 AM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
LearningMom
post Feb 26 2018, 06:39 PM
Post#22



Posts: 33
Joined: 15-August 09



I appreciate your enthusiasm because mine is waning. Below is the query that has totals over the years for each constituent, this is the report that they have been using for years because most people don't care what was purchased just how much money they brought in. This is a simple query from two tables. Constituent and Purchases over the years, linked by NameID

SELECT Names.Sort, Names.FirstName, Names.LastName, Names.Company, Names.Solicitor1, Sum(IIf([History - Money].[Year]=2013,[History - Money].[$ Paid],[nz])) AS [2013 Total $], Sum(IIf([History - Money].[Year]=2014,[History - Money].[$ Paid],[nz])) AS [2014 Total $], Sum(IIf([History - Money].[Year]=2015,[History - Money].[$ Paid],[nz])) AS [2015 Total $], Sum(IIf([History - Money].[Year]=2016,[History - Money].[$ Paid],[nz])) AS [2016 Total $], Sum(IIf([History - Money].[Year]=2017,[History - Money].[$ Paid],[nz])) AS [2017 Total $], Names.Solicitor2
FROM ([Names] LEFT JOIN [History - Money] ON Names.NameID = [History - Money].NameID) LEFT JOIN [Money] ON Names.NameID = Money.NameID
GROUP BY Names.Sort, Names.FirstName, Names.LastName, Names.Company, Names.Solicitor1, Names.Solicitor2
HAVING (((Sum(IIf([History - Money].[Year]=2017,[History - Money].[$ Paid],[nz]))) Is Not Null)) OR (((Sum(IIf([History - Money].[Year]=2016,[History - Money].[$ Paid],[nz]))) Is Not Null)) OR (((Sum(IIf([History - Money].[Year]=2013,[History - Money].[$ Paid],[nz]))) Is Not Null)) OR (((Sum(IIf([History - Money].[Year]=2014,[History - Money].[$ Paid],[nz]))) Is Not Null)) OR (((Sum(IIf([History - Money].[Year]=2015,[History - Money].[$ Paid],[nz]))) Is Not Null))
ORDER BY Names.Sort;

In the end if you think I need to recreate my databases just to get what this person wants please let know how that can be done if at all.

Go to the top of the page
 
zaxbat
post Feb 26 2018, 10:29 PM
Post#23



Posts: 952
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


If that system is working for them and they are generally happy with it.....then you probably cannot change the database structure without having to redo a lot of other stuff.
A way around that problem is to leave the whole system intact and just build temp tables on the fly that allow you to make whatever report you like...then delete the temp tables after the report is done.
That's a lot easier than revamping the whole system.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
LearningMom
post Feb 28 2018, 06:21 AM
Post#24



Posts: 33
Joined: 15-August 09



I just wanted to update everyone.

So I created a query for every year with 4 columns: Tables | Donations | Fund Mission | ADs

This was 24 queries

Then I combined those queries into 2 queries - because it crashed otherwise - 2012 through 2014 and 2015 through 2017

Then I combined those 2 queries into 1

Super Fun

I really do appreciate all the feedback and support

Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    24th June 2018 - 02:17 AM