Getting 0 where records don't exist

[GoDawgs]

Ok, I've got a problem that is pretty convoluted, I think I'm going about it the wrong way, but it is actually the best way I can think of...with that said, here goes.

I have two tables, one with year to date totals for each store, and one with last month totals for each store. I then have a report that shows all this data by different groupings, and totals by open stores and by closed stores. Now each store in the last month totals table is obviously either open or closed...but in the year to date totals table a store can be open for January and February, but closed for March and April...and this data must go to its appropriate section, so for example store 600 will have some data in "open total" and some data in "close total". Now this is not a big deal, I worked that out...the solution was to have two records for store 600...the problem occurs when i try and join the Last Month table with the Year to Date table to bring all the data together for the report...because there are two records for store 600 in one table and only one record in the other table I get the Last Month data showing up twice (if i link by store number) or I get only the "closed" data showing up (if i link by store number and open/closed).

what i'm getting now:
store ytdRevenue lastmonthRevenue open/closed
600 2200 800 open
600 3300 800 closed

or

600 3300 800 closed

Anyway, that's the problem, my ideal solution would be to have the data as follows:

store ytdRevenue lastmonthRevenue open/closed
600 2200 0 open
600 3300 800 closed

so basically get a 0 for lastmonth where there is a record in ytd but not one in lastmonth (based on store # and open/closed)...but any other suggestion would be much appreciated. thanks.

kevin

 

[jebry]

Hi Kevin!

Define your last month revenue field as follows:

LastMonthRevenues: IIf([YearToDateTable]![Open/CloseField] = Open, 0, [LastMonthTable]![LastMonthRevenue])

A couple of things to note, do not name the new field the same as the field in the actual table(like I did above), and you can format this new field to currancy, if you want to, by right clicking on the field in the query design view and choosing properties(I think).

hth
Jeff Bridgham
bridgham@purdue.edu

 

[GoDawgs]

Thanks for the response...here's the problem with that (and it's my fault, i should've included more examples in my original question): all of the stores that are open for ytd will be zeroed for last month, the problem with that is any store that is still open will lose their data for last month. Basically I need to only zero any time that a store shows up with two records instead of just one...that's the way I've been trying to think of it but haven't come up with anything yet.

 

[jebry]

Hi Kevin!

Try this:

In a module create the following function:

Public Function LastRevenue(StoreName As String, OpenString As String, MonthRevenue As Double(Currancy?) As Double or Currency

Dim rst As DAO.Recordset
Dim strStore As String

strStore = "Select * From YTDTable Where StoreName = '" & StoreName & "'"

Set rst = CurrentDb.OpenRecordset(strStore, dbOpenDynaset)

rst.MoveLast
rst.MoveFirst

If rst.RecordCount = 2 And OpenString = "Open" Then
LastRevenue = CDbl("0&quot or CCur("0&quot
Else
LastRevenue = MonthRevenue
End If

End Function

In your SQL use:

LastMonthRevenue: LastRevenue([YTDTable]![StoreName], [YTDTable]![Open/CloseField], [LastMonthTable]![lastmonthrevenue])

This should return what you want.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[GoDawgs]

Thanks again...I was trying to avoid using code, but I guess I kinda knew in the end I'd have to end up doing that...

 

[jebry]

Hi!

Sorry about the code, you may be able to do this with nested iifs, but I couldn't figure it out. I forgot one line in my code, the last line should be Set rst = Nothing.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[GoDawgs]

Quick help again Jeff...it won't let me dim rst as dao.recordset (says it's a user defined variable, so obviously not in my library or something), what do I need to do to get that to work? Thanks for your help man, much appreciated.

Kevin

 

[GoDawgs]

Nevermind, I missed a very important part...I forgot this was a new office install, I didn't have the dao library as a reference...

 

[jebry]

Hi!

See, help doesn't get much quicker than that!

Jeff Bridgham
bridgham@purdue.edu

 

[GoDawgs]

Yeah, much easier to solve your own problems...thanks for all the help Jeff, I'm sure I'll be needing it again...

Kevin