Num for Ending Inventory not pulling correctly

[kentwoodjean]

Having difficulty with the ending inventory figure on a report that is formatted by week. The summary report looks great, but the ending inventory figure for each week is always the figure for the 1st day of the week rather than the last day. My report fields are, Received (count of date1), Closed (TotCloses), 7dayclose (7 days or less), 8dayclose (8 days or more), and Inventory (ending Inventory). I tried using "Last" in my totals line and that didn't work. Any ideas?

Here is the SQL of my query that feeds my report and contains daily figures:

SELECT [RecdClosed Rept1].CountOfDate1, [RecdClosed Rept1].Date1, [RecdClosed Rept1].TotCloses, [RecdClosed Rept1].[7 days or less], [RecdClosed Rept1].[8 days or more], [Daily Inventory].EndInv, [Daily Inventory].BegInv
FROM [RecdClosed Rept1] LEFT JOIN [Daily Inventory] ON [RecdClosed Rept1].Date1 = [Daily Inventory].FromDate
GROUP BY [RecdClosed Rept1].CountOfDate1, [RecdClosed Rept1].Date1, [RecdClosed Rept1].TotCloses, [RecdClosed Rept1].[7 days or less], [RecdClosed Rept1].[8 days or more], [Daily Inventory].EndInv, [Daily Inventory].BegInv;

 

[kentwoodjean]

I have figured this out myself. Can't really explain how I got it to do what I want, but it does work.