Use of MS Access Switch()

SpartanValor
SpartanValor used Ask the Experts™
on
I have two tables.  The first contains daily data for multiple accounts.  The second contains fiscal calendars for a subset of accounts that do not use a calendar reporting period.  I want to use a Totals query to take the monthly averages of the data; but I have to bucket the data into the appropriate month based upon its fiscal calendar (if it exists) or a regular calendar.  I'm using an outer join with the first |-> second tables and the following expression:

quarter: Switch([dataDate]>IIf([dec09] Is Not Null,[dec09],#12/31/2009#) And [dataDate]<=IIf([jan10] Is Not Null,[jan10],#1/31/2010#),"January",[dataDate]>IIf([jan10] Is Not Null,[jan10],#1/31/2010#) And [dataDate]<=IIf([feb10] Is Not Null,[feb10],#2/28/2010#),"February",[dataDate]>IIf([feb10] Is Not Null,[feb10],#2/28/2010#) And [dataDate]<=IIf([mar10] Is Not Null,[mar10],#3/31/2010#),"March")

The query returns a Null.  Can someone resolve or propse a better way to produce my desired result?  Thank you in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Generally, the idea of using Switch() is to eliminate IIF's and nested IIF's ...

Can you not rewrite that w/o the IIF's ?  It's a bit hard to follow as is ...

mx

Author

Commented:
I suppose the idea is best explained with an example.  If I have the following records:

date | value
12/30/2009 | v1
12/31/2009 | v2
1/1/2010 | v3
...
n | vn
and a fiscal period "P" is defined from 12/31/2009 to 1/30/2010, I want to find the average of the values (v2,...v32).

I'm using the nested IIF()s b/c the account in the data table may not be mentioned in the fiscal calendar table, implying a regular calendar should be used.  
Top Expert 2010

Commented:
It looks like you can use Nz to avoid the IIfs, which I agree make the expression confusing:quarter: Switch([dataDate] > Nz([dec09], #12/31/2009#) And [dataDate] <= Nz([jan10], #1/31/2010#), "January", [dataDate] > Nz([jan10], #1/31/2010#) And [dataDate] <= Nz([feb10], #2/28/2010#), "February",[dataDate] > Nz([feb10], #2/28/2010#) And [dataDate] <= Nz([mar10], #3/31/2010#), "March",True, "N/A")Note how the last pair acts as a "none of the above", because the criterion is always literally True.BTW, having columns like dec09, jan10, feb10, etc is usually a signal that your DB design is questionable...
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Author

Commented:
Tell me something I don't know matthew!  I'll attached my data.  I would appreciate if you could explain a better way to set up and then calculate what I need.  Thanks.  It will take me 5 mins to make the data non-proprietary.

Author

Commented:
I just made up an example real quickly.  Accounts A and C have a fiscal calendar, B does not.  The averages correspond to the fiscal calendar of the regular calendar.  I omitted results for C to save time, I figured you would understand.  Thanks.
EEexample.xls

Commented:
What is your version of Access?  In Access 2000, I believe the Switch() VBA funtion could only be called from a UDF.
Top Expert 2010

Commented:
Actually, it's quite easy once you have the right structure. Redo the tblCalendar so that it has:

Account (Text)
TheMonth (Text)
StartDt (Date)
EndDt (Date)

Then populate it as in the code snippet below.

Query then becomes:

SELECT d.account, Nz(c.TheMonth,Format(d.dataDate,"yyyy-mm")) AS [Month], Avg(d.value) AS AvgOfvalue
FROM tblData AS d LEFT JOIN tblCalendar AS c ON (d.dataDate<c.EndDt) AND (d.dataDate>=c.StartDt)
GROUP BY d.account, Nz(c.TheMonth,Format(d.dataDate,"yyyy-mm"))
ORDER BY d.account, Nz(c.TheMonth,Format(d.dataDate,"yyyy-mm"))

account	TheMonth	StartDt	EndDt
A	2009-12	12/1/2009	12/30/2009
A	2010-01	12/30/2009	1/31/2010
A	2010-02	1/31/2010	3/2/2010
A	2010-03	3/2/2010	4/3/2010
C	2009-12	12/1/2009	12/31/2009
C	2010-01	12/31/2009	2/1/2010
C	2010-02	2/1/2010	3/2/2010
C	2010-03	3/2/2010	4/5/2010


Note that the EndDt is 1 more than it was before.  The test will always be somedate >= StartDt And somedate < EndDt!

Open in new window

Q-26313696.mdb
Top Expert 2010
Commented:
Sorry, the join was missing something:


SELECT d.account, Nz(c.TheMonth,Format(d.dataDate,"yyyy-mm")) AS [Month], Avg(d.value) AS AvgOfvalue
FROM tblData AS d LEFT JOIN tblCalendar AS c ON (d.dataDate>=c.StartDt) AND (d.dataDate<c.EndDt) And d.account = c.account
GROUP BY d.account, Nz(c.TheMonth,Format(d.dataDate,"yyyy-mm"))
ORDER BY d.account, Nz(c.TheMonth,Format(d.dataDate,"yyyy-mm"));

Q-26313696.mdb
Top Expert 2010

Commented:
The morals of the story are:1) Normalize, normalize, normalize2) If you find yourself resorting to fancy Switch/IIf jiu-jitsu, take another look at moral #1

Commented:
Does this do it:

SELECT Avg(a.var) FROM myTable a WHERE Month(a.myDate) IN (SELECT Month(b.myDate+1) FROM myTable b);

Author

Commented:
Hey Matthews, thanks for the suggestion to better normalize.  It makes it much easier.  I can't open the .mdb file extension becuase of security settings.  I tried to reproduce and it seems to correctly caculculate the averages but for ONLY the accounts on the fiscal calendar, not the other accounts following a regular calendar. They are omitted for the results for the query.  Below is the query I am using.  

You'll notice there are a few more tables in between that I have to use as linking tables to handle some one-to-many relationships involving a fiscal calendar for a group of related accounts - rather than saving many copies of the same fiscal calendar I assigned accounts to a type of fiscal calendar.

How can I return the average for ALL the accounts, not just those that are on the fiscal calendar?
SELECT 
qryFinalDailyAnalytics.msAccount, 
tblFiscalCalendar.calMonth, 
Avg(qryFinalDailyAnalytics.origCost) AS AvgOforigCost, 
Avg(qryFinalDailyAnalytics.bookValue) AS AvgOfbookValue, 
Avg(qryFinalDailyAnalytics.accrInt) AS AvgOfaccrInt, 
Avg(qryFinalDailyAnalytics.mktValue) AS AvgOfmktValue, 
Avg(qryFinalDailyAnalytics.purchYield) AS AvgOfpurchYield, 
Avg(qryFinalDailyAnalytics.currYield) AS AvgOfcurrYield, 
Avg(qryFinalDailyAnalytics.duration) AS AvgOfduration, 
Avg(qryFinalDailyAnalytics.DTEM) AS AvgOfDTEM, 
Avg(qryFinalDailyAnalytics.DTFM) AS AvgOfDTFM
FROM 
qryFinalDailyAnalytics LEFT JOIN 
((tblAccountLegend LEFT JOIN tblFiscalCalenarToAccountLegend ON tblAccountLegend.msAccount = tblFiscalCalenarToAccountLegend.accountNumber) LEFT JOIN tblFiscalCalendar ON tblFiscalCalenarToAccountLegend.calendarId = tblFiscalCalendar.calendarId) ON qryFinalDailyAnalytics.msAccount = tblAccountLegend.msAccount
WHERE 
(((qryFinalDailyAnalytics.dataDate)>=[tblFiscalCalendar]![startDate] And (qryFinalDailyAnalytics.dataDate)<=[tblFiscalCalendar]![endDate]))
GROUP BY 
qryFinalDailyAnalytics.msAccount, 
tblFiscalCalendar.calMonth
ORDER BY 
qryFinalDailyAnalytics.msAccount;

Open in new window

Author

Commented:
obviously the monthly averages for accounts not in the fiscal calendar would need to coincide with a regular calendar.
Top Expert 2010

Commented:
I have no doubt that my approach can be extended to that situation.  However, to get the SQL statement just so, I am going to need a sample MDB file to play with, so if you want me to take this any further I will need you to prepare one.The sample file should be identical in structure to what you have, but of course not contain any sensitive data.  Fake the data if need be.It is sufficient if you can supply sample data in Excel worksheets, as I can very easily import those into Access.

Author

Commented:
Does the output in your .mdb file cotain the averages for B?  If so then I am implementing wrong (and I need to address), if not, then can you extent so that ALL accounts are outputted with averages?
Top Expert 2010

Commented:
Here is the output for my query based on the last sample file I uploaded.Note how I put the date criteria in the join expression and not the WHERE clause.  Access will allow you to do that, but then you can't use the GUI query designer.  You can however work with that in the SQL view.
account	Month	AvgOfvalue
A	2009-12	34
A	2010-01	51.34
A	2010-02	47.13
A	2010-03	50.97
A	2010-04	56.75
B	2009-12	50.5
B	2010-01	51.61
B	2010-02	52
B	2010-03	58
B	2010-04	74.67
C	2009-12	61
C	2010-01	48.84
C	2010-02	55
C	2010-03	50.59
C	2010-04	77.5

Open in new window

Author

Commented:
Ahhh, ok.  So its an implementation issue.  When I get to another computer I will download and take a look.  I didn't notice you put the criterian ON the join.  I will revise.  Thanks a lot Matthew.

Commented:
Is there any method to the way in which the Acct selects his Start and End to monthly accounting periods?  I believe I have a simpler solution to calculating your monthly averages - so don't shut this question down for 24 hours please.

Author

Commented:
GRayL, not sure what you're asking but I don't want it to be interactive with a form.  The fiscal calendars are permanent and those accounts without fiscal calendars should always reference a julian calendar.  Matthew's solution works well but I have to figure out how to adapt it to my specific case, in which several linking tables must be used in a dependence of outer joins.  This is where I am stuck.  I will try to upload a sample .mdb.
dbSample.mdb
Commented:
You need to put into your fiscal calendar a 'regular' fiscal calendar with StartDT and EndDT values of first and last dates of each calendar month.  Name that collection of 12 records per year 'Z' for now.  Then add another field to tblData named AcctPdID.  If an Acct has a regular calendar year set of accounting periods, 'B' will enter 'Z' in this field.  If he has a special set of rules contained in tblCalendar, he re-enters the Acct number - which is 'A' or 'C' in your case.  Thus tblCalendar has one set of rules for a regular Acct and a separate set of rules for each Acct with special dates for each year in your database.  Change the field name in tblCalendar from Acct to AcctPdID.  This field will appear in the three tables.    There may be several accounts which have this normal situation - in which case each of them will use 'Z' in this field.  You also need a separate table tblAccts to have things normalized properly.

tblAccts
=====
Acct - text or numeric
AcctPdID - text or numeric
AcctName
Address
etc.

tblCalendar
========
CalID - pk - autonumber
AcctPdID - fk from tblAccts
YrMon - date format yyyy-mm
StartDT - date
EndDT - date

tblData
=====
DataID - pk - autonumber
DataDate - date
AcctPdID- fk from tblAccts
Value - currency

The query is now very much simplified:

SELECT a.Acct, b.YrMon, Avg(c.Value) From tblAccts a
INNER JOIN (tblCalendar b ON a.AcctPdID = b.AcctPdID)
INNER JOIN tblData c ON b.AcctPdID = c.AcctPdID
WHERE Year(b.YrMon)=2010 AND c.DataDate Between b.StartDT AND b.EndDT
GROUP BY a.Acct, b.YrMon

Note the format of tblCalendar - it can be multi-year and needs 12 records for each special case Acct and one set of 12 records for all the rest with 'normal' accounting periods.  Are you able to work within these changes?  If you want I can create an mdb from you Excel data and get back to you with the results - hopefully you can 'see' the merit of this approach and how it simplifies the problem.

Author

Commented:
Hey Gary, I took your advice and re-normalized the data.  Below please see my implementation.  It is rejecting the SQL on the first "ON."  What you called AcctPdID, I called calendarId.  Any idea what's wrong?  Thanks so much.
SELECT a.msAccount, b.yrMonth, Avg(c.bookValue) From tblAccounts a 
INNER JOIN (tblFiscalCalendar b ON a.calendarId = b.calendarId) 
INNER JOIN qryFinalDailyAnalytics c ON b.calendarId = c.calendarId 
WHERE Year(b.yrMonth)=2010 AND c.dataDate Between b.startDate AND b.endDate
GROUP BY a.msAccount, b.yrMonth

Open in new window

Author

Commented:
Thank you Matthew and Gray.  I eventually used Gray's recommendation although Matthew's also worked.  Below is what finally did the trick.  Many thanks to everyone who helped.  I really appreciate it.
SELECT tblAccounts.msAccount, tblFiscalCalendar.yrMonth, Avg(qryFinalDailyAnalytics.origCost) AS AvgOforigCost, Avg(qryFinalDailyAnalytics.bookValue) AS AvgOfbookValue, Avg(qryFinalDailyAnalytics.accrInt) AS AvgOfaccrInt, Avg(qryFinalDailyAnalytics.mktValue) AS AvgOfmktValue, Avg(qryFinalDailyAnalytics.purchYield) AS AvgOfpurchYield, Avg(qryFinalDailyAnalytics.currYield) AS AvgOfcurrYield, Avg(qryFinalDailyAnalytics.duration) AS AvgOfduration, Avg(qryFinalDailyAnalytics.DTEM) AS AvgOfDTEM, Avg(qryFinalDailyAnalytics.DTFM) AS AvgOfDTFM
FROM (tblAccounts LEFT JOIN tblFiscalCalendar ON tblAccounts.calendarID = tblFiscalCalendar.calendarId) LEFT JOIN qryFinalDailyAnalytics ON tblFiscalCalendar.calendarId = qryFinalDailyAnalytics.calendarID
WHERE (((qryFinalDailyAnalytics.dataDate) Between [startDate] And [endDate]))
GROUP BY tblAccounts.msAccount, tblFiscalCalendar.yrMonth
HAVING ((Year([yrMonth])="2010"))
ORDER BY tblAccounts.msAccount, tblFiscalCalendar.yrMonth;

Commented:
Do you now have a field YrMonth in tblFiscalCalendar?  How did you get to  tblAccounts from tblAccountsLegend?  How did you create the field CalendarID.  You added 19 records to tblFiscalCalendar for all the 'normal' accounts?

I would have to see you upload another 23.2 Meg of data - perhaps you can post several small snippets?  

Commented:
Thanks, glad to help.  Ignore my last post - your notice of award came in while I was sending it.

Author

Commented:
Oh, one last thing.  I have to "SELECT qryFinalDailyAnalystics.msAccount..." NOT "SELECT tblAccounts.msAccount..." otherwise each account on the normal julian calendar will display the average values of ALL the normal julian accounts rather than the average of ONLY the account's values.  

Commented:
glad it's working out ok.  good luck with the project.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial