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    
 
   
LearningMom
post Feb 24 2018, 08:44 AM
Post#1



Posts: 33
Joined: 15-August 09



I have a database that keeps track of a dinner. After the dinner is over with the information on what the constituent purchased is put in a history table. I have written a query that looks at a specific solicitor with columns showing how much money each constituent paid over the years.

The fields are Name, Organization, 2012 Total Dollars, 2013 Total Dollars etc and then the last column is the current year 2017 which has not been put in history yet but still shows the money for that year.

Each constituent is on its own separate row and it works fine.

The solicitor now wants to see the totals broken out, a column for their 2012 table and a separate column for their 2012 night of purchase say raffle tickets, so forth and so on. This is the expression I wrote which totals those to purchases for each year.
2012 Total $: Sum(IIf([History - Money].[Year]=2012,[History - Money].[$ Grand Total],[nz]))

Is there a way to tell it to look at each moneyID per year so it shows each individual purchase in a designated column for each year?

Go to the top of the page
 
GroverParkGeorge
post Feb 24 2018, 10:05 AM
Post#2


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, your table appears to be modeled on the concept of a "spreadsheet"; it is NOT a relational table which is the proper approach for Access.

This sort of query is exponentially harder to create from an improperly designed "spreadsheet-style" table; and that's just one of the drawbacks to it.

You could probably create a union query that selects records from each "year" as a work-around, but I would strongly urge you to fix the table design problem.

Invest some time in learning how Relational Databases, like Access, work best. Then set about correcting this table design.

Here are two resources that'll get you pointed in the right direction.
Newcomers Reading List
Roger's Access Blog - Repeating Columns
Go to the top of the page
 
LearningMom
post Feb 24 2018, 10:23 AM
Post#3



Posts: 33
Joined: 15-August 09



Thank you for your feedback. I guess I didn't explain my database correctly and apologize that it seemed like I was talking about one table.
Go to the top of the page
 
GroverParkGeorge
post Feb 24 2018, 10:35 AM
Post#4


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


Sorry, this is the sentence I was referring to: "The fields are Name, Organization, 2012 Total Dollars, 2013 Total Dollars etc and then the last column is the current year 2017"

I apparently misinterpreted that to mean one table.

Tell us about the entire table structure, please.
Go to the top of the page
 
LearningMom
post Feb 24 2018, 03:04 PM
Post#5



Posts: 33
Joined: 15-August 09



The fields Name, Org, Address, year, table, ad, total dollars are all from different tables in a query
I removed the formula so now if a constituent purchased more than 1 table or other items they will have multiple rows for years.

I would like columns that say 2012 table and list the table name, 2012 AD list the ad and 2012 total list the amount they paid for each item and continue that for each year.

2012 Table | 2012 AD | 2012 Table Amount $ | 2012 AD Amount $
Silver Full b/w $10,000 $1,000

I can do this in a pivot table but the newer version of access doesn't have the pivot tables feature anymore, so my client can't use it, plus their client wants it in excel and the pivot table doesn't export into excel with text fields in the data area. I can create it in excel but writing the conditional format so the names appear and not the id numbers is it not something I really want to do, but I will do what I have to do, unless I can create a query that I can export.



Go to the top of the page
 
GroverParkGeorge
post Feb 24 2018, 03:11 PM
Post#6


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


How about a crosstab?
Go to the top of the page
 
zaxbat
post Feb 24 2018, 03:39 PM
Post#7



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


Excellent idea GPG.

If you have not worked with crosstab/pivot queries they may confuse you a bit at first.....but they work really well for summing columns into one record per entity (customer, in your case).
This post has been edited by zaxbat: Feb 24 2018, 03:47 PM
Go to the top of the page
 
LearningMom
post Feb 24 2018, 07:33 PM
Post#8



Posts: 33
Joined: 15-August 09



Unfortunately you can only add 1 value in a crosstab. I keep trying to figure out a way of copying the pivot table as that is perfect but I can't even highlight it to copy it.
Go to the top of the page
 
zaxbat
post Feb 24 2018, 08:13 PM
Post#9



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


Kind of shooting in the dark here.....can you attach a jpg of your table relationships display?
Go to the top of the page
 
LearningMom
post Feb 25 2018, 08:16 AM
Post#10



Posts: 33
Joined: 15-August 09



I guess I am befuddled that I can write expressions in a query showing a column for each years totals, but can't write one that puts the table name in the column next to that total if there are two purchases in the same year.
Go to the top of the page
 
Jeff B.
post Feb 25 2018, 08:21 AM
Post#11


UtterAccess VIP
Posts: 10,168
Joined: 30-April 10
From: Pacific NorthWet


"put the table name ..." would seem to imply that you have tables named to convey meaning (e.g., 2012, 2013, 2014). It would help us help you to understand better what data you're starting with.

More info, please...
Go to the top of the page
 
GroverParkGeorge
post Feb 25 2018, 10:16 AM
Post#12


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


I think we're still a bit at sea as to what your actual tables look like, and how you are gathering the various fields into a query.

To be frank, without that level of detail, anything we suggest is a guess.

In order to move forward, please show us your tables as well as the SQL for this query. Thanks.


Go to the top of the page
 
LearningMom
post Feb 25 2018, 10:18 AM
Post#13



Posts: 33
Joined: 15-August 09



I want my info to look like this:
Name Address 2012 2012 Table $ 2012 2012 AD $ 2012 Mission Fund 2012 Mission Fund $
John Doe 123 Main Street, Peace, TX 11111 Silver Table $10,000 B/W Ad $1000 Mission Fund $500




Right Now it looks like this:
Name Address 2012 2012 Table $ 2012 2012 AD $ 2012
John Doe 123 Main Street, Peace, TX 11111 Silver Table $10,000
John Doe 123 Main Street, Peace, TX 11111 Mission Fund $500
John Doe 123 Main Street, Peace, TX 11111 B/W Ad $1000


And with every year added more rows are appearing.

Go to the top of the page
 
GroverParkGeorge
post Feb 25 2018, 10:22 AM
Post#14


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


Please show us the TABLES themselves. That's where it all starts.

Also, please show us the SQL from the query, not the output.

Thanks.
Go to the top of the page
 
LearningMom
post Feb 25 2018, 10:33 AM
Post#15



Posts: 33
Joined: 15-August 09



SELECT Names.NameID, Names.Sort, Names.Salutation, Names.FirstName, Names.LastName, Names.Company, Names.[1stAddress], Names.[2ndAddress], Names.City, Names.State, Names.ZipCode, Names.Cell, Names.Email, Names.Solicitor1, Names.Solicitor2, Sum(IIf([History - Money].[Year]=2012,[History - Money].[$ Grand Total],[nz])) AS [2012 Total $], IIf([History - Money]![Year]=2012,[History - Money]![Ticket Category]) AS [2012 Ticket Category], IIf([History - Money]![Year]=2012,[History - Money]![AD Category]) AS [2012 AD Category], Sum(IIf([History - Money].[Year]=2013,[History - Money].[$ Grand Total],[nz])) AS [2013 Total $], IIf([History - Money]![Year]=2013,[History - Money]![Ticket Category]) AS [2013 Ticket Category], IIf([History - Money]![Year]=2013,[History - Money]![AD Category]) AS [2013 AD Category], Sum(IIf([History - Money].[Year]=2014,[History - Money].[$ Grand Total],[nz])) AS [2014 Total $], IIf([History - Money]![Year]=2014,[History - Money]![Ticket Category]) AS [2014 Ticket Category], IIf([History - Money]![Year]=2014,[History - Money]![AD Category]) AS [2014 AD Category], Sum(IIf([History - Money].[Year]=2015,[History - Money].[$ Grand Total],[nz])) AS [2015 Total $], IIf([History - Money]![Year]=2015,[History - Money]![Ticket Category]) AS [2015 Ticket Category], IIf([History - Money]![Year]=2015,[History - Money]![AD Category]) AS [2015 AD Category], Sum(IIf([History - Money].[Year]=2016,[History - Money].[$ Grand Total],[nz])) AS [2016 Total $], IIf([History - Money]![Year]=2016,[History - Money]![Ticket Category]) AS [2016 Ticket Category], IIf([History - Money]![Year]=2016,[History - Money]![AD Category]) AS [2016 AD Category], Sum(IIf([History - Money].[Year]=2017,[History - Money].[$ Grand Total],[nz])) AS [2017 Total $], IIf([History - Money]![Year]=2017,[History - Money]![Ticket Category]) AS [2017 Ticket Category], IIf([History - Money]![Year]=2017,[History - Money]![AD Category]) AS [2017 AD Category]
FROM ([Names] LEFT JOIN [2017 Grand Totals] ON Names.NameID = [2017 Grand Totals].NameID) LEFT JOIN [History - Money] ON Names.NameID = [History - Money].NameID
GROUP BY Names.NameID, Names.Sort, Names.Salutation, Names.FirstName, Names.LastName, Names.Company, Names.[1stAddress], Names.[2ndAddress], Names.City, Names.State, Names.ZipCode, Names.Cell, Names.Email, Names.Solicitor1, Names.Solicitor2, IIf([History - Money]![Year]=2012,[History - Money]![Ticket Category]), IIf([History - Money]![Year]=2012,[History - Money]![AD Category]), IIf([History - Money]![Year]=2013,[History - Money]![Ticket Category]), IIf([History - Money]![Year]=2013,[History - Money]![AD Category]), IIf([History - Money]![Year]=2014,[History - Money]![Ticket Category]), IIf([History - Money]![Year]=2014,[History - Money]![AD Category]), IIf([History - Money]![Year]=2015,[History - Money]![Ticket Category]), IIf([History - Money]![Year]=2015,[History - Money]![AD Category]), IIf([History - Money]![Year]=2016,[History - Money]![Ticket Category]), IIf([History - Money]![Year]=2016,[History - Money]![AD Category]), IIf([History - Money]![Year]=2017,[History - Money]![Ticket Category]), IIf([History - Money]![Year]=2017,[History - Money]![AD Category]), Names.DoNotMail
HAVING (((Names.Solicitor1)="Andersen") AND ((Names.DoNotMail) Is Null)) OR (((Names.Solicitor2)="Andersen"))
ORDER BY Names.Sort;

Attached File(s)
Attached File  Relationship.JPG ( 28.33K )Number of downloads: 3
 
Go to the top of the page
 
GroverParkGeorge
post Feb 25 2018, 11:16 AM
Post#16


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


Thank you.

WIth that detail, one of our SQL experts can pitch in and offer suggestions.

BTW: it's generally considered less than desirable to use spaces and special characters (like the $ sign) in field names. You can work around them, but it's always a bit trickier. Give some thought to cleaning that up if you can.

BTW: I see this [nz] in the SQL. What does it do?
Go to the top of the page
 
GroverParkGeorge
post Feb 25 2018, 11:25 AM
Post#17


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, you left out a query or table: [2017 grand totals] .
Go to the top of the page
 
zaxbat
post Feb 25 2018, 08:38 PM
Post#18



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


Look at this code and give it a try. Hope i have the right filenames and fields but without a zipped DB to start from there may be a few minor typos. I know that the grouping and summing are not in this code yet, but just start with this and see if it is close to what you are looking for.

p.s. Don't even try to get this to work in the design query UI, it won't because of the variables, double quotes, and line continuation marks. But runs like a champ in VBA (see pic of output file with dummy data).
Would recommend shorter field names, in general. Should never use a reserved name (Year) for a field name. Would avoid special characters ($), numbers, and even spaces in field names---if you use them then you always have to put the field name in brackets....takes a lot of time and creates code failures that you look straight at and can't see.

' ================================================================================
================================

Sub test2()
Dim makefieldname1 As String
Dim makefieldname2 As String
Dim makefieldname3 As String
Dim mysqltxt As String
Dim mytbl As String
Dim i, startyear, endyear As Integer

'TURN ON ERROR AND NO WARNINGS
'DROP TABLE NAME (GET RID OF ANY OLD TMPTABLE)
'CREATE TABLE PROGRAMATICALLY
'ADD COLUMNS FOR YEAR
'INSERT INTO TABLE WHERE YEAR = YEAR

On Error GoTo HANDLE_ERRORS
DoCmd.SetWarnings False
mytbl = "tmprpttbl"
startyear = 2017
endyear = 2018
'delete possible old temp table from previous run
mysqltxt = "DROP TABLE " & mytbl & ";"
DoCmd.RunSQL mysqltxt

'create empty table
mysqltxt = _
" CREATE TABLE " & mytbl & _
" ( " & _
" NameID LONG, " & _
" Sort CHAR(32), " & _
" Salutation CHAR(32), " & _
" FirstName CHAR(32), " & _
" LastName CHAR(32), " & _
" Company CHAR(32), " & _
" 1stAddress CHAR(32), " & _
" 2ndAddress CHAR(32), " & _
" City CHAR(32), " & _
" State CHAR(32), " & _
" ZipCode CHAR(32), " & _
" Cell CHAR(32), " & _
" Email CHAR(32), " & _
" Solicitor1 CHAR(32), " & _
" Solicitor2 CHAR(32), " & _
" DoNotMail YESNO, " & _
" ChkDate DATE " & _
" );"
' MsgBox (mysqltxt)
DoCmd.RunSQL mysqltxt


'loop through all applicable years
For i = startyear To endyear
'assemble field names for year i to add into table
makefieldname1 = Str(i) & "_TICKET_CAT"
makefieldname2 = Str(i) & "_AD_CAT"
makefieldname3 = Str(i) & "_TOTAL"

'add column/field names for year i into table
mysqltxt = _
" ALTER TABLE " & _
mytbl & _
" ADD COLUMN " & _
makefieldname1 & " CHAR(32), " & _
makefieldname2 & " CHAR(32), " & _
makefieldname3 & " MONEY " & _
" ;"
' MsgBox (mysqltxt)
DoCmd.RunSQL mysqltxt

'run query to select this year data into table
mysqltxt = _
" INSERT INTO " & mytbl & _
" SELECT " & _
" Names.NameID, Names.Sort, Names.Salutation, Names.FirstName, Names.LastName, Names.Company, Names.[1stAddress], Names.[2ndAddress], " & _
" Names.City, Names.State, Names.ZipCode, Names.Cell, Names.Email, Names.[Solicitor1], Names.[Solicitor2], Names.DoNotMail, " & _
" [History - Money].[Ticket Category] AS " & makefieldname1 & ", " & _
" [History - Money].[AD Category] AS " & makefieldname2 & ", " & _
" [History - Money].[$ Grand Total] AS " & makefieldname3 & ", " & _
" [year] as ChkDate " & _
" FROM " & _
" Names LEFT JOIN [History - Money] on Names.NameID = [History - Money].NameID " & _
" WHERE (" & _
" (right([History - Money].[year],4) = " & Str(i) & ") " & _
" AND " & _
" (((Names.Solicitor1 = 'Andersen') And (Names.DoNotMail Is Null)) Or (Names.Solicitor2 = 'Andersen'))" & _
" )" & _
" ORDER BY Sort;"
' MsgBox (mysqltxt)
DoCmd.RunSQL mysqltxt
Next i

DoCmd.SetWarnings True
Exit Sub

HANDLE_ERRORS:
MsgBox ("Error in special query routine: " & Err.Description)
Resume Next

End Sub
This post has been edited by zaxbat: Feb 25 2018, 09:30 PM
Attached File(s)
Attached File  VBAcreatedTmpTable.jpg ( 496.41K )Number of downloads: 6
Attached File  test2.zip ( 264.14K )Number of downloads: 6
 
Go to the top of the page
 
LearningMom
post Feb 26 2018, 06:20 AM
Post#19



Posts: 33
Joined: 15-August 09



I appreciate all the feedback and advice and clearly the effort everyone has put in, and going forward will start applying these great ideas to my databases. With all the being said this client wants all the information on one person in 1 row only no matter what year or what they purchased or donated. They are excel people and don't understand anything else.

Again thank you everyone
Go to the top of the page
 
GroverParkGeorge
post Feb 26 2018, 07:36 AM
Post#20


UA Admin
Posts: 34,132
Joined: 20-June 02
From: Newcastle, WA


Actually OUTPUT is not the same thing as STORAGE. Excel people don't know that because they are only familiar with that single method of storing/displaying data in a worksheet.

I would love to be able to pursue this, but without that missing query, well, it's a bit of guesswork.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    16th December 2018 - 11:57 PM