Query with IF statement?

[boostbyslinky]

Folks, hopefully you might be able to help with this one...

I have a database to which I'm attempting to add some additional functionality. The database is already in use for building distribution lists of software upgrades to customers, based on the version of software that they're currently running.

The achieve this, I have a dynamically built query generated by a number of combo boxes.. (example below)

SELECT Net_IDS.net_id
FROM (SYS INNER JOIN [SYS-VERS] ON [SYS].Version = [SYS-VERS].Version) INNER JOIN Net_IDS ON [SYS].Net_ID = Net_IDS.Net_ID
WHERE ((([SYS-VERS].Version_ID)>=(SELECT [SYS-VERS].Version_ID FROM [SYS-VERS] WHERE [SYS-VERS].Version = "2.88A/17")))  AND NET_IDS.NET_ID NOT IN (SELECT NET_ID FROM DO_NOT_SEND)
GROUP BY Net_IDS.Net_ID;

This version of the query excludes sites that are listed in do_not_send.Net_ID

What I would like to do is increase the size of the do_not_send table to allow for individual product choices (i.e. customer is happy to receive certain products, but not others, as network upgrades).. Therefore, I suspect I need some kind of IF statement to replace the "NOT IN" section to say, IF net_id present in do_not_send.net_id then verify whether do_not_send.*product* equals "yes" ... (for example)..

I appreciate that isn't written in fluent SQL, but I think you should get the gist of where I'm attempting to go with it!

Thanks in advance, please feel free to ask questions if you need additional info..

 

[boostbyslinky]

Sorry for the shameless bump, just wondering whether anyone had any thoughts on this one?

 

[vbaInet]

I personally don't understand what you're trying to achieve here. Can you create a spreadsheet with the BEFORE and AFTER set of results? Or explain with some screenshots?

 

[boostbyslinky]

ok,

I'm not quite sure how I'd map out the results clearly in Excel as the end point should simply be a list of IDs..

I'll try to explain what I'm trying to achieve a little more clearly..

I have a number of tables containing customer IDs and product versions, there is a seperate table for each product.
I already have a query that selects customer IDs based on the version of the product that they have, in addition I have a query that works across numerous tables to select customers that have a combination of product versions.

A number of our customers specify the route through which they receive software upgrades, therefore there is an additional table to highlight sites on our "do not send list". I have added in the ability to exclude sites within the Do Not Send list from the queries mentioned above.

An additional layer of complexity has been added to our shipping requirements, in that, some of our do not send sites will happily receive some upgrades but not others, therefore I want to expand the Do Not Send list to include columns referring to products with a Yes/No value to define whether the customer is to happy to receive upgrades for the relevant product.

As a result of this change in requirement, I need to query the product table to return a list of customer IDs and verify that list against the do not send table, if the customer ID appears in the do not send table I then need to verify whether the customer is indicated as a yes or no for ship of the product in question...

Does that make any more sense? (it's easy for me to understand of course, I'm familiar with the data!)

TIA..

 

[vbaInet]

Wouldn't that be a matter of adding an additional WHERE criteria:

NET_IDS.NET_ID NOT IN (SELECT NET_ID FROM DO_NOT_SEND [COLOR=Blue]WHERE do_not_send.[Yes/NoField] = No[/COLOR])

 

[boostbyslinky]

well knock me down with a feather! <reminds himself to start with the simple stuff before MASSIVELY overcomplicating the matter>

Cheers chap, I'll give the a shot..

 

[boostbyslinky]

and what a surprise.. it works perfectly (with the addition of a pair of "s) ..

Thanks again..

 

[vbaInet]

Grandiose!!!

You're welcome.

 

[boostbyslinky]

I always celebrate too soon...

The query could contain up to 5 products, therefore potential 5 queries against the do_not_send.productname ...

Can you give me a steer on the formatting of that? (it's driven from a form, so if you could show me how to do 2 products, I can work the rest out.. )

Cheers again..

 

[vbaInet]

At least the celebration made you feel good for a short while

The query could contain up to 5 products, therefore potential 5 queries against the do_not_send.productname ...

I don't follow. Care to elaborate?

 

[boostbyslinky]

Hmmm... how best to explain..

The query is driven by a form with a collection of combo boxes, allowing product selection and version selection (5 times each), therefore, the

WHERE do_not_send.[Yes/NoField] = No

ends up being

WHERE do_not_send.[Product_select_1] = No

I'm trying to work out the OR structure to be able to do that for [Product_Select_1] through to [Product_Select_5]

The selection of query is defined through a cascaded IF statement in VBA, whereby if Product_Select_2 = N/A, then do query for one product, if Product_Select_2 = N/A, then do query for two products.. etc up to 5..

I think

(SELECT NET_ID FROM DO_NOT_SEND WHERE [Do_Not_Send].[Product_Select_1] = "no" or [Do_Not_Send].[Product_Select_2] = "no")

should do it (at a 2 product level)..
Whilst out walking at lunch I decided that it has to be an OR statement, as a No in any of the fields should preclude the ID from being included in the list..

 

[vbaInet]

(SELECT NET_ID FROM DO_NOT_SEND WHERE [Do_Not_Send].[Product_Select_1] = "no" or [Do_Not_Send].[Product_Select_2] = "no")

should do it (at a 2 product level)..
Whilst out walking at lunch I decided that it has to be an OR statement, as a No in any of the fields should preclude the ID from being included in the list..

That's the one.