Help with writing an If Then clause in Access SQL

[MCTampa]

In my database, I have two tables: Inventory and Restrictions.
The inventory is a list of hotels, grouped by developer, for specific unit sizes and check in dates.

On the table table called Restrictions I can manually enter the variables I want restricted. I'm not sure if I want to delete them or isolate them in a different table, so for now, I'm just working on the Select query.

There is a hierarchy to my restrictions.
1 - Developer
2 - Area
3 - Resort
4 - Size
The four items above are all optional and can be left blank.
5 - Start Week (SW)
6 - End Week (EW)

Items 5 and 6 are mandatory and must be included.

If I specify the Developer, I want all associated items to be returned based on the SW and EW parameters.
If I leave Developer blank, and specify Area, i I want all associated items to be returned based on SW and EW.
If I leave Developer and Area blank, and specify Resort, i I want all associated items to be returned based on SW and EW.
If I leave Developer and Area and Resort blank, and specify Size, i I want all associated items to be returned based on SW and EW.

Below is my code, which works fine if I specify all items, but not if I leave one blank.
The reason for leaving one blank is because (for example) there are numerous resorts in an area, or numerous resorts associated to a developer.
I'm sure there has to be an Or somewhere, but when I've tried that, my data gets inflated.

SELECT 
restrictions.AREA, 
restrictions.Developer, 
restrictions.[SW], 
restrictions.[EW], 
ic.RESX,
ic.TRKFLD, 
ic.Size, 
Sum(ic.[Units]) AS Units

FROM Restrictions AS restrictions LEFT JOIN Inventory AS ic ON 

(restrictions.Area = ic.Area)

And(restrictions.Developer = ic.Developer)

AND (restrictions.Resort = ic.resx)

AND (ic.Week >= restrictions.[SW]) 

AND (ic.Week <= restrictions.[EW])

WHERE (((ic.[Units]) Is Not Null))
GROUP BY restrictions.AREA, restrictions.Developer, ic.RESX, ic.TRKFLD, ic.SpaceType, ic.Size, restrictions.[SW], restrictions.[EW];

Thanks for any help,

Mike

 

[jackd]

Mike,

AND means each condition must be met

It would be helpful if you could tell us about the "business" that this database is trying to support.
2 tables does not seem enough, but I don't know your business.
A copy of the database would also be helpful.

 

[MCTampa]

Thanks.
I know that the AND clause requires all elements to be met. I’m not sure how to integrate an OR clause or if that is event correct (which I doubt).

The nature of the business is hospitality.
I can solve the problem by running multiple queries each scenario but wanted to slim down the process.

 

[jackd]

You can do some analysis to see which fields may not be valued if that is an issue.
Another option is to have a form where you can select various items/fields and build a specific query(s).

If you have only 4 basic queries, you could just create and use 4 queries. Then a form where user can select which query to run.

see this for ideas

 

[Micron]

I believe you would need this pattern for each field that can be null (which you referred to as blank)
And (restrictions.Developer = ic.Developer) OR restrictions.Developer Is Null

My personal preference is to build the sql in code with variables as strSelect, strCriteria, strEnd (and if desired, strOrderBy). A series of one line IF tests either appends or doesn't the part that isn't / is null to the criteria string variable. This could depend on what you really need the results for. If it will be a form or report recordsource, then OK. If you intend to present the query results, then it gets more complicated for a Select query. However, you shouldn't be presenting tables or editable queries to users anyway.