Access Database Query Issue

[Bosnoval]

Access 2003 Databse
ASP.NET 2
Win 2003 Server

Currently I have a search page that only allows a single keyword (person) to be searched at a time. I thought upgrading that option via a multiple selection listbox would be better so that you can search for a pair or more of people at a time but the query that makes this happen is giving me a run for my money. I’m unsure as how to proceed and so I’m hoping someone can help me out.

My somewhat simplified query is as follows:

 
SELECT DISTINCT Pictures.PicRegDate, Pictures.PicFilename, Pictures.PicId, Pictures.PicDate
 
FROM (Pictures  LEFT JOIN Attributes ON Pictures.PicId=Attributes.PicId)  LEFT JOIN KeyWords ON Pictures.PicId=KeyWords.PicId
 
WHERE (Attributes.Data LIKE @ddlValueList Or (@ddlValueList='%' And Attributes.Data Is Null)) AND ((KeyWords.Keyword LIKE @mnu_keywordList) Or ((@mnu_keywordList='%' AND KeyWords.Keyword Is Null)) 
 
AND (YEAR(Pictures.PicDate) BETWEEN @txtDateRngBegin AND @txtDateRngEnd)
 
 
ORDER BY Pictures.PicDate, Pictures.PicRegDate
 
 

“%” is a default value for both @ddlValueList and @mnu_keywordLis t which is just the value for a menu selection of “< Any >”. A typical runtime value for each might be “Birthday” for @ddlValueList and “Doe, John” for @mnu_keywordLis t. The resulting records would be all pictures of John Doe at a birthday.

I’m guessing my new search page is going to have to dynamically write portions of the query, like an AND statement or something for each keyword, but I’m not even certain the syntax for the query enough to be able to write out that block of code yet…

[nico5038]

Hmm, I have no ASP .NET experience, but for querie constructions hat shouldn't bother. :-)

For multiple fields your approach will be OK, just use a LIKE and %
For a "full match" pass the entered string and for the <all> use %.

For muliple "full matches" you can construct an IN(a, b, c, ....) structure in the WHERE clause before passing the query to Access.
In Access I often use a (temp) help table with the distinct values for e.g. a lastname and a YesNo field. For the display of the result I use a JOIN of the original table with the temptable that's having a True criteria for the YesNo field.

Getting the idea ?

Nic;o)

[NeoPa]

Personally, I'd go for the In() structure where possible. Unfortunately it doesn't handle multiple Like entries though. This is where Nico's TempTable idea really helps (You could just build a more complicated SQL string here with multiple ORs instead). His solution is better overall as it's more scalable and better handles large numbers of these criteria. Another alternative is to use SubQueries in place of the TempTable. It has the benefit of handling the whole query in a single SQL string. (More on SubQueries can be found in Subqueries in SQL.)

[Bosnoval]

Ah, thanks for the IN() tips. That helps a lot. Using that I could loop through the “selected” listbox items and write them into the IN() statement like the two names below:

 
SELECT DISTINCT Pictures.PicRegDate, Pictures.PicFilename, Pictures.PicId, Pictures.PicDate
 
FROM (Pictures  LEFT JOIN Attributes ON Pictures.PicId=Attributes.PicId)  LEFT JOIN KeyWords ON Pictures.PicId=KeyWords.PicId
 
WHERE KeyWords.Keyword IN ('Doe, John', 'Doe, Jane')
 
ORDER BY Pictures.PicDate, Pictures.PicRegDate
 
 

By the temp table and/or sub-query do you mean as a way to generate exclusive searches? For example the above query actually doesn’t generate the results I’m after. It essentially searches for the two names separately and then joins the total results. Instead I really only want to return results where BOTH people are pictured (pictures with only one or the other person shouldn’t be in the result).

[nico5038]

With multiple rows for one picture you would need to switch to using a GroupBy query.
Use the "IN() query" as a basis for that and use Count() in the GroupBy query with imageID as the grouping field. The Count() field can be tested for >1 to get only multiple hits.

Getting the idea ?

Nic;o)

[Bosnoval]

…again thanks for the help! It’s very much appreciated.

Hmm, well I get that two or more fields in the IN(test1, test2) query could produce the same record multiple times. …and that only image records that do are the records I’m after.

What I don’t understand is how to count the PicId’s in a useful way. Once the IN() comes back with the records wouldn’t the DISTINCT already keep me from having duplicate PicId’s. A count(KeyWords. PicId) would only result in a literal count of how many records I have wouldn’t it? I can’t think of a way to count(PicId) where the count would result in a count of how many times that particular unique PicId / record row was in the records as apposed to it just returning a .recordcount equivalent for the total number of rows in the result.

The only way I can think of implementing this would possibly be with a sub-query???

[nico5038]

The Group By PictureID will give a count per PictureID.
When you remove the "Group By PictureID" you'll get just a count of all records.

Just create a groupby and see the effect of adding / removing fields.
It's the used number of fields that will give the granularity. The more fields in the grouping the lower the count in general.
Distinct will just remove duplicates, but not count the number if dupes.

Nic;o)

[NeoPa]

Bosnoval,
This is quite advanced logic and I can only say, Nico seems to have a good handle on it. Trust him as he's shown good understanding of this issue. This is a very neat trick to keep in your arsenal :) If you get stuck come back for help, but be assured, this concept is a very good one.

[Bosnoval]

Oh, I’m not doubting the concept I’m just saying I’m having trouble understanding it ha-ha. I guess my issue is that when trying to test this so that I can better understand it I’m getting errors. I’m trying something like this:

SELECT Pictures.PicRegDate, Pictures.PicFilename, Pictures.PicId, Pictures.PicDate
 
FROM (Pictures  LEFT JOIN Attributes ON Pictures.PicId=Attributes.PicId)  LEFT JOIN KeyWords ON Pictures.PicId=KeyWords.PicId
 
WHERE KeyWords.Keyword IN ('Doe, John', 'Doe, Jane') AND YEAR(Pictures.PicDate) BETWEEN 1900 AND 2007
 
GROUP BY Pictures.PicId
 
ORDER BY Pictures.PicDate, Pictures.PicRegDate
 

I’ve also tried changing the GROUP BY to KeyWords.PicId but it gives the same error, “You tried to execute a query that does not include the specified expression 'PicRegDate' as part of an aggregate function.”

As far as the count() syntax goes for a GROUP BY I’ve been reading up on sites like “sql-tutorial.net” to understand what you mean. Altogether I think you’re suggesting a SQL query something like this below??

SELECT Pictures.PicRegDate, Pictures.PicFilename, Pictures.PicId, Pictures.PicDate, Count(Pictures.PicId)
 
FROM (Pictures  LEFT JOIN Attributes ON Pictures.PicId=Attributes.PicId)  LEFT JOIN KeyWords ON Pictures.PicId=KeyWords.PicId
 
WHERE KeyWords.Keyword IN ('Doe, John', 'Doe, Jane') AND YEAR(Pictures.PicDate) BETWEEN 1900 AND 2006
 
GROUP BY Pictures.PicId
 
ORDER BY Pictures.PicDate, Pictures.PicRegDate
 

This too gives the same error.

[nico5038]

Try:

SELECT Pictures.PicRegDate, Pictures.PicFilename, Pictures.PicId, Pictures.PicDate, Count(Pictures.PicId)
 
FROM (Pictures  LEFT JOIN Attributes ON Pictures.PicId=Attributes.PicId)  LEFT JOIN KeyWords ON Pictures.PicId=KeyWords.PicId
 
WHERE KeyWords.Keyword IN ('Doe, John', 'Doe, Jane') AND YEAR(Pictures.PicDate) BETWEEN 1900 AND 2006
 
GROUP BY Pictures.PicRegDate, Pictures.PicFilename, Pictures.PicId, Pictures.PicDate
 
ORDER BY Pictures.PicDate, Pictures.PicRegDate
 

All fields in the SELECT part need to have an aggregate function (Count, Max, etc) or need to be present in the GROUP BY.
As I assume that all Picture fields are the same for every PictID, this GROUP BY should work.

Nic;o)