Need some kind of format for a Qry field

[gpierce9]

This question is about adding 12 to a field to then get all the associated Sales dollars.



My qry works under one condition which is I can choose ONE field variable.
But if I try to do math with that field, I do not get my desired data. Here goes.

In the qry, I have a TimeID field that comes from a table. It is an autonumber field that relates to a month end date.
EG a record in my table has TimeID of 75 and next to it a field showing month ending 1/31/2018.

In the Qry, I made a 2nd calculated field. The one with the problem.
TimeIDLYM: [TimeID]-12

LYM means last year month in accounting talk.
Notice that I want the current year month having an answer of 75 for 1/31/2018 to subtract 12 months
to derive an answer of 63 which corresponds to 1/31/2017 prior year.

I am hoping to find the last year sales using the 63 answer. Here is the next Qry field.

LYMonthSales$: IIf([TimeIDLYM]=[TimeID],Round(Sum(Nz([Sales],0))))

So if the TimeIdLym is 63, it would look to the tblTime, TimeID field for 63 and bring in my sales for last year.

But I get zero sales. I have some kind of formatting problem with TimeIdLym where I show
TimeIDLYM: [TimeID]-12

I made a dummy Access file to follow, if you don't mind.

The qry to try is named ..... Qry50 Last Year Month ... which needs to use the 63 answer.

The qry .... Qry40 Current Year Month .... works because it is using the answer of 75.

So Qry50 is the problem. Something is wrong with .... TimeIDLYM: [TimeID]-12

Thanks. I will see now how to post the shorty 48KB zipped dummy file here. Thanks.
Glen

[ranman256]

if you want to do math on dates use;
dateAdd("m",-12,datefield)

if you want to format a calc:
put in your formula
IIf([TimeIDLYM]=[TimeID],(Sum(Nz([Sales],0))))

with the cursor in the query field, look at the property FORMAT
set it to currency
decimals, 2

[ssanfu]

There are 2 things wrong.

1) You are misusing the Autonumber field in table "tblTime".

AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

Autonumbers are simply a way to create a unique identifier for each record. It should NEVER be treated as a meaningful piece of data.

If you need a meaningful sequencer (like to assign user numbers), you have to create it and manipulate it yourself. You will need to stop using Autonumber, and create a sequence number that you increment yourself and apply to the record when it is saved.

See: Autonumbers--What they are NOT and What They Are
Microsoft Access Tables: Primary Key Tips and Techniques


2) The table "tblSales" has a design issue and a relationship is wrong, so I changed the tables PK/FK field names and changed the design of table ""tblSales"

I use "_PK" as the suffix for a PK field and "_FK" as the suffix for a FK field. Note the PK field of table "tblTime" and the FK field in table "tblSales".

In query "Qry50LastYearMonth" (note that I removed the spaces in the query name), you have calculations:

TimeIDLYM: [TimeID_PK]-12
LYMonthSales$: IIf([TimeIDLYM]=[TimeID_PK],Round(Sum(Nz([Sales],0))))
LYMonthMargin$: IIf([TimeIDLYM]=[TimeID_PK],Round(Sum(Nz([Margin],0))))

Not sure what they calculate.

Maybe "TimeIDLYM: [TimeID_PK]-12" is to calculate 1 year ago from the current year start?
Maybe try

TimeLYM: Dateadd("m", -12, TimeInvoiceDate)

The other two calculations might be

LYMonthSales: IIf([TimeIDLYM]=TimeInvoiceDate,Round(Sum(Nz([Sales],0))))
LYMonthMargin: IIf([TimeIDLYM]=TimeInvoiceDate,Round(Sum(Nz([Margin],0))))

Maybe explain what you are trying to do as if I am 14 yo. Given a rep and a date, what results do you expect?

[gpierce9]

Ssanfu. No, I was not trying to write something for a 14 year old. Sorry if you felt that way. I was writing this for people who are not cost accountants like myself
who might just be programmers only. Your method sounds real good and I hope to do it in 2 days when I get back.

I have never seen anyone or any models that try to show a customers sales (or whatever) with columns of current year month, last year same month, current year YTD and last year Ytd.

That is what I am shooting for. I will fix up my file with your ideas and see if this works for me. Thanks for taking the time.
If you were near me in South NJ, I'd take you to lunch! I will do this in 2 or 3 days as I need to do something. thanks Glen

[ssanfu]

My error....

I should have said "Please explain to me what you are trying to do as if I am 14 yo. Given a rep and a date, what results do you expect? I am not a cost accountant."