IF statement equivalent in SQL

[Ken Puls]

Hey folks,

I'm not a SQL geek, and I'm having a little trouble coming up with some syntax. I'm wondering if someone can help me out.

I have two tables that I'm working with:

tblBudget_Ops
tblBudget_ForecastEach has identical fields in it:

Account
Dept
Sub
BudgetMonth
AmountThe Budget_Ops table has line items with a budget for each Account/Dept/Sub/Month combination. (So for every account/dept/sub combination there will be an amount for each of the 12 months)

Budget_Forecast, though, only has data for specific account/dept/sub/date combinations. The reason for this is that it is holding only budget values that we have specifically overridden.

What I'm trying to accomplish here is to pull a query that lists a complete forecast. To do this I need to pull the Amount from the Budget_Forecast table first (which may be zero.) If there isn't an amoutn in that table for the Account/Dept/Sub/Date combination, then I need to get the value that is in the Budget_Ops table.

Can anyone give me a hand constructing such an animal? Ultimately I want to pull this into a PivotTable in Excel so I can just mine data from it.

Thanks!

[geekgirlau]

I don't have MS Access on site today, but if I understand you correctly I think you'll need a union query: all records from Budget_Forecast PLUS all records from Budget_Ops that do NOT already exist in Budget_Forecast.

To exclude the items in Ops that already exist in the Forecast, you can create a query that contains both tables, with every field linked other than the Amount. You need the joins to include all the records from Ops but only matching records from Forecast. Your criteria is that the Amount from the Forecast = zero (or null if there's no actual record existing in the table).

It's a little hard to describe, but I can whip something up this evening for you if you like.

[Ken Puls]

I would like, I would like! :)

Just to be clear though... If any record exists in the Forecast table, I want to use that. It's only if a record doesn't exist there that I'd want to reach back to budget. So I think the criteria is =NULL.

I've attached a pic of a portion of my Forecast table. You'll notice that some do have zeros. So where they show zeros there, I need it to return zero.

There will be records for each of these in the Budget_Ops table too, plus many more accounts... (Notice that there is no record for 78050-805 here for 12/31/2010, so I'd want to pull whatever is in teh Budget_Ops table for that combination.

I think I might be repeating myself, but does that makes sense? Is it enough to work with?

[geekgirlau]

Can you post a small example db with the 2 tables? Easier to have some values to work with.

I would also suggest that you look at the naming convention to separate the 2 tables. I generally use the following for field names:

Data type (txt, lng, dtm etc.)
Table descriptor (in your case this might be BgtO & BgtF)
Name for the fieldThe reason for this is to differentiate between the fields in queries where you are accessing both tables - I'll show you an example later.

[Ken Puls]

Okay, here's the Budget_Ops table, stripped down to just those accounts as well.

So what I'd be looking for:

68310-805 for 12/31/2010 would have -239,000 (from Forecast)
78050-805 for 12/31/2010 would have 11,875 (from Ops)Make sense?

One kicker that I ran into before trying to pull this off... if the forecast number was the same as Ops, my query just ignored it. I need it to pull the forecast...

[Ken Puls]

Oops, sorry, just saw your reply. Wil mock something up shortly.

[Ken Puls]

Here you go...

For obvious reasons the account names have been changed and data stripped to just Oct-Dec 2010. The account numbers remain though, and you should find that you can repro filter the tables to get the info above.

Just for interest's sake, I've also included the queries I've been using (there's actually a budget_Ops and a Budget_Internal set... we have lots of budgets). The query I've got works for the most part, but the challenge I'm having is that, if the same number shows up in the forecast and budget table, it wipes it to 0 in the output. My thought is that we toss that, and rebuild the way it should be done. It does show the output format I'm after though.

Thanks a ton for your help with this. I've been meaning to get this fixed for a couple of years, as it's been causing me some real headaches...

[geekgirlau]

Sample file with new query attached.

In your sample the "Sub" field was blank, so I haven't included this in the links on the query. Null values will cause issues - they're pesky little critters.

I recommend that you keep the union query separate - use it only to list the forecast vs ops data. You can then use this as a subquery to incorporate data from other tables. Union queries are tricky enough to read and analyse without making them more complicated.

[Ken Puls]

Hi Anne,

Unfortunately, it doesn' look like it worked. I've attached a pic and noted where the data comes from for those records. As you can see, the table query is selecting both from the forecast and ops table and putting them into the query. The highlighted ones are the only ones that should be returned.

For the record, I do actually need the sub included as well, as I have some accounts with subdepts. Very few, but some. I've just been using these accounts as an example because I can see the issues I'm facing elswhere in my project with these ones right now, so it's easy to test.

Regardless, adding for those appears to be pretty easy. Just add another "AND" in there, correct?

[geekgirlau]

Hi Ken

The query to be looking at is qryForecast_NEW, which will give you the results you need.

If we need to add Sub to the join and it will contain Nulls, you'll need to add


AND (nz(tblBudget_Ops.Sub,"") = nz(tblBudget_Forecast.Sub,""))

to the query (just before WHERE).

[Ken Puls]

Apologies, Anne. I have no idea WTF I did this morning. I was working with that query (copied it into my DB, which is why you see that table header.) I swear that the results I was getting are as illustrated above...

Very strange... Copying it in now seems to work?

At any rate, making the change for the nz portion and will test...

[Ken Puls]

Crap. Just realized that I have to layer another query on to get the rest of the stuff and I'm late for a soccer coaches meeting. I'll test later tonight when I'm home from that.

Thanks for your help with this. :)

[geekgirlau]

I've added in the extra fields:

Your original query has been renamed as "qryForecast_Budget_Ops_OLD"
The union sub query is "sqryForecast_Budget_Ops"
The query "qryForecast_Budget_Ops" now uses the union sub query, and incorporates the additional fields from tblCOA

[Ken Puls]

Okay, so here's a weird one for you...

This works, which is all good. But what I do is connect to the database via ADO and run the following query:


SELECT qryForecast_Budget_Ops.* FROM qryForecast_Budget_Ops WHERE (((qryForecast_Budget_Ops.BudgetMonth)>#12/31/2009# And (qryForecast_Budget_Ops.BudgetMonth)<=#12/31/2010#));

If I go into Access and run that as a SQL query Bob's your uncle and it all pops up just fine. But trying to access it through ADO gives me the following error:

-2147217900 Undefined function 'nz' in expression.

Grrr... happen to know that equivalent for nz in ADO? I've got so much running off this database that re-writing for DAO right now isn't really an option...

[Ken Puls]

I thought I might try to just solve the short term issue by connecting a pivot table directly to the query, just so I can get this working. No such luck there either though... it appears that queries that are union based, or derived from a union base, don't show up in the wizard to select. Regardless, that was a stopgap anyway, but frustrating that it doesn't work!

[geekgirlau]

Ken, what version are you using? I've just tested in 2010 and it's working without the nz function. Not sure about 2007, but in 2003 you'll need to break down the union query a little further - one where Sub is not null and there is a join between the two tables, and one where Sub IS null and there is no join.

[hansup]

If the Nz() function is not recognized you must be trying to run your query from an environment other than Access ... classic ASP? VBScript?

Nz() is a VBA function. Within Access the database engine's expression service can use VBA functions and even user-defined functions in queries. Outside Access, the expression service is not available, so your queries can only use the smaller set of functions directly supported by the database engine. And Nz() is not one of them. This isn't an ADO issue.

If your environment is VBScript, you could code your own replacement for Nz(). Perhaps it would help to tell us more about how you're attempting to run the query.

[Ken Puls]

Anne,

I'm running 2010. I removed the whole reference to the Sub link, and it seems to work. The thing is that 99% of our accounts don't have subs any more, I was just trying to make sure in case we get into an issue with them again. I'm relatively confident that we won't use them again and that it won't be an issue for us with the forecasts (just history), so shouldn't whack me. Having said that, two years from now when someone decides that we need to use them, I worry about people remembering. So are you just saying to do the binding but drop the whole nz portion?

Hansup, I'm using Excel VBA to create an ADO connection to the database. Through that connection I pass a dynamically generated SQL string to select a filtered set of query results back. (Basically I'm trying to cut down to just the current year.

But that's where the issue comes in. Excel reports the error I listed above re the nz query. I tried setting a reference to the Access library in the Excel VBE, hoping it would let me leverage the nz function, but no joy there.

[hansup]

In that context, I'm sure the expression service is not available ... so that's why no Nz().

Try it with an Iif() expression instead of Nz() in your query.

Iif(IsNull(tblBudget_Ops.Sub), "", tblBudget_Ops.Sub)


That should get you the same as what I think you want from:

nz(tblBudget_Ops.Sub,"")

[geekgirlau]

Ken I think you'll find in 2010 there's no issue if you join the
tables in the union query via the Sub field in addition to the
others, and just remove the nz altogether. In other words, the
join for Sub should be identical to the other joins. To test,
add values to Sub for some records so you have a combination of
null and not null.

[Ken Puls]

Thanks guys. I did what you suggested, Anne, and I think I'm good to go. (Sorry for not replying earlier... got wrapped up in using the results to get our budgets out.)

[hansup]

Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved! :thumb

[Ken Puls]

OMG... told off in my own forum! :eek:

:rotlaugh:

[hansup]

I guessed you would be more amused than annoyed. Thanks for your good humor.

Regards,
Hans