Help, please! Beginner..Trying to filter results from a min size-max size in Access?

[AshGMW]

Hi there,

*NB: I am a beginner with Access, so paraphrasing would be appreciated*

I work for a commercial property company and have just created a database for our approximate 2,000 applicants. The purpose of this database is to email applicants relevant properties that may be of interest.

Therefore, a typical 'search' that I need to perform on a daily basis could be something like:- All applicants looking for warehouse/factory space, between 2,000-6,000 sq ft in NW London. So, as an example, I have my fields set up as follows:

Company^ Property Location^ Property Type^ Minimum Size Sqft^ Maximum Size Sqft^
App1 NW London Warehouse/factory 2,000 4,500
App2 S London Offices 3,000 10,000
App3 NW London Warehouse/Factory 2,500 5,000
App4 NW London Warehouse/Factory 3,500 6,000

However, when I filter my "minimum sq ft" "greater than" 2,000 and "maximum sq ft" "Less than" 6,000, it returns very little results; out of 2,000, possibly only 80. I now see that these columns are contradicting eachother. Also, you can imagine if I were then to filter out property type and location, I would be left with barely any applicants.

Please help!! I haven't a clue how to get the database to perform in the way I need it to.

I need to be able to search between a range of sizes without the two columns conflicting between one another.



I hope this makes sense.

Thanks

[Rawb]

For the individual locations, I'd change your Table layout to something like the following:

LocID	LocCompany	LocAddress	LocType	LocSize
1	App1	NW London	Warehouse/Factory	4500
2	App2	S London	Offices	10000
3	App3	NW London	Warehouse/Factory	5000

Then you can just run a Query like the following:

SELECT 
  * 
FROM 
  Location 
WHERE 
  [LocSize] BETWEEN 2000 AND 6000

That would return all locations that have a size in the range you're looking for.

[AshGMW]

Rawb,

Thanks so much for your reply.

The only problem is that each applicant is logged has a minimum sqft AND maximum sqft preference which is the reason for two seperate size columns (min and max). Unfortunately, its not possible for me to log an applicant under just one size bracket as your example suggests.

I also noticed that you labelled your columns in a code type format, e.g. "Loc", followed by the field name. Is this essential for creating a database? I have just labelled mine quite obviously such as "Property Location", "Minimum Sq Ft", "Maximum Sq Ft", "Company Name" etc.

I did run a query whereby I asked the database to find any values in the minimum column of "greater than" 2000 and in the maximum column of "less than" 3500, but its seems when I do that, it contradicts the results.

I apologise if my request doesn't make a lot of sense but I really appreciate any advise you may have.

Thank you

[uncletreetrunk]

I would create an unbound form that runs a query. That way it's more user friendly and you don't have to change the query everytime you want something new.

I would suggest the following:

Have property location and property type as combo boxes in the form. In Data>Row Source put

 SELECT DISTINCT [tblName].[FieldName] FROM tblName ORDER BY [FieldName];

In the query design view>criteria for property location and property type have

 Like [Forms]![frmName]![cboName] & "*"

This will allow you to either search what you selected in the combo box or it will return all records if nothing is specified



For Location size I would have two text boxes for min and max size this will allow for maximum flexibility in your search

In your query for each location size field have something similar to this in the criteria: (this could be something for minimum square footage)

 Between (IIf([Forms]![frmName]![txtSmallMinSqft] Is Null,0,[Forms]![txtSmallMinSqft]![MinAmt])) And (IIf([Forms]![frmName]![txtSmallMaxSqft] Is Null,1000000000,[Forms]![frmName]![txtSmallMaxSqft]))

This will allow for greater than, less than, or a range of values specified by the user



These links might be of use:
IIf statements: http://office.microsoft.com/en-us/ac...001228853.aspx
http://www.techonthenet.com/access/f...vanced/iif.php

Combo boxes: http://www.techonthenet.com/access/comboboxes/

Naming Conventions: http://access.mvps.org/access/general/gen0012.htm

Unbound forms running query: http://ms-access-tips.blogspot.com/2...ain-query.html

Using Like/ Between/Wildcards (first three sections): http://www.fontstuff.com/access/acctut06.htm