Selecting Max values

[gkaro]

Hi all, first post...

I have a database that has 5 columns

YEAR, WEEK, RETAILER, SALES(quantity), INVENTORY(quantity).

What I would like to do is query the DB to show sales for every WEEK and YEAR, but only INVENTORY for the latest YEAR and WEEK. With blanks or zeros for every older period.



Something like this

2010, 01, COSTCO, 150, 0(or blank)
2010, 02, COSTCO, 176, 0(or blank)
2011, 04, COSTCO, 142, 0(or blank)
etc
etc,
etc,
but for the latest week
2012, 05, COSTCO, 112, 657 (assume this is the most recent entry)

what I do not want to do is change the history and make all prev values on INVENTORY 0

hope this makes sense.
thanks
gkaro.

[June7]

Use IIf expression in query to construct a field. Will need year and week as input parameters, like:

InvQty: IIf([Year]<>2012 And [Week]<>"05", Null, [Inventory])

That example shows the parameters as fixed but could be references to controls on form or derived from current date:

InvQty: IIf([Year]<>Forms!formname!Yeartextboxname And [Week]<>Forms!formname!Weektextboxname, Null, [Inventory])

InvQty: IIf([Year]<>Year(Date()) And [Week]<>Format(Format(Date(), "ww"),"00"), Null, [Inventory])