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:
Code:
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
Code:
TimeLYM: Dateadd("m", -12, TimeInvoiceDate)
The other two calculations might be
Code:
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?