Getting Month and YTD for Prior Year and Current Year each

[molly]

I have a method that works for me regarding Month and YTD data for 2 time periods: Prior Yr and Current Yr.

However, I just discovered the DateADD and DateSerial commands. How could I use them in my attached Qry22 ??

I presently prompt myself for the desired fiscal Year eg 2007 and the Month eg 12.
Next, Qry22 gathers the records. Unfortunately, I have to use an IIF statement in all my fields !!! Seems like a waste. Right?

Rev$CYmth: Sum(IIf([YearType]=[BaseYear] & "A" And [FiscalMonth]=[FyMo],nz([Rev$])))

And so on. But it works.

I am thinking that a DateSerial or DateAdd method might enable me to avoid a lot of IIF statements. Do you think so? How do?

So the question involves my Qry22 and how to improve it.

I have searched hi and wide on the internet and I see nothing. This is a major question for we Accountant/Access people.

Thanks much.
Molly

 

[fneily]

Some reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You have two tables with FiscalYear and FiscalMonth. No duplicate common fields in a relational database.
In tblData, you have Cost1$, Cost2$. Doesn't conform to First Normal form protocol. Should just be one field called Cost.
Also this table has no primary key.

Your tblDates has all the calendar dates for the fiscal year and fiscal month. And the Primary Key should be DateID.

Your tblData should look like:
DateID (Primary Key)
TranTypeID
SoldToID
FormulaID
Qty
Rev
Cost

You now have a one-to-many relationship between your tblDates and tblData. Connect them in a query and you can now use the calendar dates for your queries instead of going through loops with functions.

It also looks like you should have a tblSoldTo. SoldToID doesn't say much.

 

[molly]

To fneily: thanks so much. I will look into the Fiscal year and month issue. You are probably correct. I only did so because i am often asked to do work on a fiscal period (eg sept 1, 2006 to Aug 31,2007) and sometimes on a calendar data basis. I will look at this.

As for the cost$. I have various kinds of cost. EG Raw Material cost, Cost of Production Cost, Commission Cost and more for each 3 combo record (soldto, shipto, formula) in a given month. So for this Post, i just made it Cost1$ and Cost2$ to signify that there are 2 or more kinds of costs associated with a 3combo code. I am doing Product Line Reporting. Each tblData record is for a customers formula sale. The Qty, sales and various costs are assigned in the SAP main frame to a 3combo code. Does this added info change your mind on how I handle?

I look forward to your thoughts.
Molly

 

[molly]

I see that in my sample, i just made it a 2combo (soldto and formula) to simplfy my post. In my real world data, i also have the shipto location field which makes the 3combo. The soldto field is the "Bill to" field.

I see that you picked up on my TranType. "A" is actual and "B" is budget and "L" is long range plan. I only showed "A" actual records in the sample.

Molly

 

[fneily]

In regards to your "costs". yeah, with your further explanation, then tblData will look like:
DateID (Primary Key)
TranTypeID (A,B,L)
CustID
FormulaID
Qty
Rev
MaterialCost
ProductionCost
Commission

Again, it seems that there should be a customer table with Billing info and shipping info. Thus the CustID in the above table.

 

[molly]

Yes, i have those tables too for the customer and the product.

But, as in Qry22, how would I get the data into the proper qry columns? I have 4 calculated fields per category in the Qry22.

EG, for the Revenue$, i have 4 fields:

Rev$PYmth: Sum(IIf([YearType]=[BaseYear]-1 & "A" And [FiscalMonth]=[FyMo],nz([Rev$])))

Rev$CYmth: Sum(IIf([YearType]=[BaseYear] & "A" And [FiscalMonth]=[FyMo],nz([Rev$])))

Rev$PYytd: Sum(IIf([YearType]=[BaseYear]-1 & "A" And [FiscalMonth] Between 1 And [FyMo],nz([Rev$])))

Rev$CYytd: Sum(IIf([YearType]=[BaseYear] & "A" And [FiscalMonth] Between 1 And [FyMo],nz([Rev$])))

and then the costs have similar 4 field periods of time comparisons.

Molly

 

[molly]

PS definitions - PY means prior year, CY means current year.

and i wonder if using IIF is best or not.

I see the DateSerial and DateAdd commands and I wonder if they are best or not.

thanks
Molly

 

[molly]

I did not show the cust or product tables in order to keep this request simple. I want to focus on how to make Qry22 get the data into the proper fields, using a different method. my current qry22 method works. but i think i need to change my IIF method into another method.

so that is the challenge.

molly

 

[fneily]

Oops. Made a mistake. tblData should look like:
DataID Primary Key
DateID
TranTypeID (A,B,L)
CustID
FormulaID
Qty
Rev
MaterialCost
ProductionCost
Commission

Confused... Do you want to compare Months from different years such as December, 2006 to December 2007?
Ran your query got a strange answer.

Now there's a one-to-many relationship between the tables. Using the two tables, I made a query in design view with criteria under FiscalYear from tblDates 2007 0r 2006 and criteria under FiscalMonth from tblDates 12 and TranTypeID from tblData = "a". Grouping on these fields, I summed Qty, Rev, Cost1, Cost2. The answer then compares Decembers for 2006/2007. Is that what you want?

Here's the SQL:
SELECT tblDates.FiscalYear, tblDates.FiscalMonth, tblData.TranTypeID, Sum(tblData.Qty) AS SumOfQty, Sum(tblData.[Rev$]) AS [SumOfRev$], Sum(tblData.[Cost1$]) AS [SumOfCost1$]
FROM tblDates INNER JOIN tblData ON tblDates.DateID = tblData.DateID
GROUP BY tblDates.FiscalYear, tblDates.FiscalMonth, tblData.TranTypeID
HAVING (((tblDates.FiscalYear)=2007 Or (tblDates.FiscalYear)=2006) AND ((tblDates.FiscalMonth)=12) AND ((tblData.TranTypeID)="a"));

 

[molly]

Hi. I put another file out called fneily1.mdb

i made one change. If we are truly trying to improve the data structure, i changed the date field in tblData to be called SaleDate. it is the sale transaction date. Apparently, you see a method down the road to manipulate the tbldata record into my fiscal year.

SaleDate is when the sale was made. The dates in tblDates is the calendar date range that the SaleDate could fall into. I originally didn't want to introduce another variable but you seem willing to look at.

Therefore, the query1 in fneily1 needs fixing with say a between command. are you sure that you still want to look at this?


If you return to my original file "Molly Year to Date1", try running my Qry23. you will see where i was going with my work. Qry23 is by formulaID. (ignore missing formula name; i omitted on purpose for this sample). And then i could write a similar one by customer if i wanted to. and so on.

So my real purpose here was to improve my Qry22 and the many IIF statements and see how some other method might apply like DateSerial or DateAdd.

But i am open to adjusting my tlbData structure, because i always preferred using SaleDate and getting rid of fiscalYr and fiscalMth fields in tblData. So that is why you have my interest in streamlining my structure while at the same time, eventually making a good qry for my comparisons.

So i suggest that you run my qry23 to see the goal.
then go get this fneily1 file (which uses your good ideas) and see how to make a comparable comparison qry.

thanks alot. you may be onto something. please see the attachment.
glen

 

[fneily]

First, do you know about the Totals button (the button with the Greek E or Sigma) in the query design view? If you click it, another row appears called Total. That is what I am using to group and sum. No IIF statements.

I ran your qry23 and observed the result. I then added FormulaID to mine, ran it and got a cleaner result. The SQL looks like:

SELECT tblDates.FiscalYear, tblDates.FiscalMonth, tblData.TranTypeID, tblData.FormulaID, Sum(tblData.Qty) AS SumOfQty, Sum(tblData.[Rev$]) AS [SumOfRev$], Sum(tblData.[Cost1$]) AS [SumOfCost1$], Sum(tblData.[Cost2$]) AS [SumOfCost2$]
FROM tblDates INNER JOIN tblData ON tblDates.DateID = tblData.DateID
GROUP BY tblDates.FiscalYear, tblDates.FiscalMonth, tblData.TranTypeID, tblData.FormulaID
HAVING (((tblDates.FiscalYear)=2007 Or (tblDates.FiscalYear)=2006) AND ((tblDates.FiscalMonth)=12) AND ((tblData.TranTypeID)="a"));

SalesDate is nice to have, but that's not what I am using. Here is some sample records (not all fields shown) of tblData:
DataID DateID TranTypeID SalesDate
23 60 A
24 60 A
25 60 A
36 79 A
37 79 A

Now you connect tblDates with tblData throught DateID in a query. FiscalYear and FiscalMonth are in tblDates. You'd place criteria on those fields in the query.

 

[molly]

Hi - before your last message, i made a newer file. I call it "Molly Year to Date2".

I put SaleDate into tbldata. So now the tblData is proper.
My Qry132 gets the Fiscal info which i used to store in tblData.

My Qry133 is the guts. I am asked what fiscal year and what fiscal month. Then the IIF statements kick in. Which is the object of our concern.

Then Qry134 with formula becomes the Comparison. Which i copy paste to excel for my boss. I do the same for Qry135 customer.

So the question now, is my Qry133 IIF method the best way???

Molly

 

[molly]

I think you don't see yet the objective of my Qry134.
The qry output has formulaID as rows and the comparison columns.

I then copy paste the qry to excel where i reuse a nicer column heading.

I didn't say this yet, but i have a difference column too. However, i am trying to keep my request simple.

My question revolves around Qry133 and the many IIF statements. I am trying to find out if there is a better method or not than my many IIF's.

thanks
glen

 

[fneily]

You just keep doing the same thing over and over. I explained the table structures with primary keys. You didn't take the suggestion. I use 1 query, you use 4. Here's my output:
FY FM Type Formula Qty Rev Cost1 Cost2
2006 12 A 561 2388 11889 320 239
2006 12 A 567 1000 3000 121 88
2007 12 A 543 266 522 35 14
2007 12 A 561 309 3300 69 34

You can easily see the years, month compared, etc.

Is there a better way then your IIF's? Yes, I just gave you one. You don't want to use it. My suggestion now is you repost and get someone else's answer.

 

[molly]

Hi. I put an excel file showing one of my reports that i copy/paste each month from my Access Qry into excel. maybe that will show you the objective better.

I can understand if you gave me all that you have.

My system works for me. I only wanted to tweak the IIF statements after i read about dateserial and dateadd commands. Just thinking of improvement.

So I am not stuck. Just want to do better.

The way that you show your data is not good for comparing figures. Please see my excel sheet noted as attachement.

Thanks alot. Thanks for trying.
Molly