creating a recordset with multiple criteria

[bikebanditcom]

i need help creating a recordset with three criteria, search by region, supplier, and then by a date range. the tables can be found here.

http://www.wecantlose.com/relationships2.htm

any help is appreciated. thank you

dan

 

[Skittle]

Well I always start by creating a page that returns one of the criteria. The easiest way to build a test SQL statement for Access is to set up an Access Query and look at the SQL view. You can then copy the SQL statement to the command string of your ASP command text and use it as a template.

After that is working you can modify the code to conditionally append 'AND fieldname = ' parameter to the command text to give different criteria for each user specified request.

 

[Tarwn]

I generally am not in fabvor of using the built in Query editor, but in this case I would tend to agree. It appears that you are going to have at least 9 tables in your statement, so while you could easily do something like:

SELECT * FROM tblSales, tblLots, tblPhases, tblProjects, tblSuperintendants, tblRegion, tblSalesPackages, tblPackageLevels, tblParts WHERE tblSales.lotID = tblLots.lotID AND tblLots.pahseID = tblPhases.phaseID AND etc etc

You don't want to do this. When you use a select statement like this from a comma delimited group of tables it applies that WHERE clause after it has created a temporary table of all of the data in the given tables, with one row for every single possible combination from each of the tables. This is bad because in this case even if you only had 2 records in each table your looking at 2 to the 9th from my statement above, or 512 differant rows in the temporary table. Now what if you had 10 rows in each table? 15? Yikes!
On the other hand, if you use Join statements (which the query builder will do) the temporary table is bound by the equalities you use in the ON statements (which will be your relationships) and that first temp table in memory will only have records that fit the relationships, which will be much better memory wise. Plus it speeds up executiong of the statement on the WHERE clauses because there are fewer comparisons that have to be made.

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[bikebanditcom]

thanks so much guys, atleast i know my tables are set up correctly then? ok. im going to test out the access query and then i'll attempt to add things, now how exactly does one write the paramater out &quot;fieldname = &quot;<% region %>&quot; ?

thanks alot guys.

dan

ill keep you posted

 

[bikebanditcom]

hmm..this is proving to be difficult..i have too many ambigious outer joins....? damnit man...umm, i beginning to think these tables aren't set up properly to be able to pull this report from, any one mind giving me an opinion? in the meantime i created main query that joins tables region, project, phase, lot, then i've put the rest into the next query

SELECT qUpperTableJoin.Region, tblSupplier.supplierName, tblSched.dateset1A, tblSched.dateset2A, tblSched.dateset3A, tblSched.dateset1B, tblSched.dateset2B, tblSched.dateset3B, tblSched.dateset1C, tblSched.dateset2C, tblSched.dateset3C, tblParts.*, tblPackageLevels.partID, tblParts.cost
FROM (qUpperTableJoin INNER JOIN (tblSales INNER JOIN ((tblParts INNER JOIN (tblPackageLevels INNER JOIN tblSalesPackages ON tblPackageLevels.packageLevelID = tblSalesPackages.packageLevelId) ON tblParts.partID = tblPackageLevels.partID) INNER JOIN tblSched ON tblSalesPackages.salesPackID = tblSched.salesPackageID) ON tblSales.saleID = tblSalesPackages.salesID) ON qUpperTableJoin.lotNum = tblSales.lotID) INNER JOIN tblSupplier ON tblParts.supplierID = tblSupplier.supplierID;



Type mismatch in the expression?

 

[bikebanditcom]

heres my query

http://www.wecantlose.com/user.asp

??

 

[bikebanditcom]

im sorry

http://www.wecantlose.com/use.htm

 

[bikebanditcom]

http://www.wecantlose.com/user.htm

 

[Skittle]

get the query to do what you want in Access first and then cut and paste it into your asp criteria string. If you are unfamilar with SQL or ASP it takes a problem of syntax and selection out of the equation.

Start small with just two tables and build it up.