Creating a function that queries a one to many relationship

[davideps]

Hi Everyone,

In order to ask my question, I need to provide a bit of background with the question at the end. I am creating a database that allows me to add an arbitrary number of tags to notes. I have three tables: tags, notes, and tagnotes (the last is an association table). Roughly these look like:

TAGS
id,tag
1,"participation"
2,"citizenship"
3,"democracy"

NOTES
id,longtext
1,"long memo from a book"
2,"another long memo from a book"

TAGNOTES
id,tagid,noteid
1,2,1

So, the last table shows that the second note has the tag "participation". The following query returns only those notes with a tag that at least partially matches a string (in this case 'partic'):



SELECT n.*
FROM tags AS t, notes n, tagnotes AS tn
WHERE t.tag like '*Partic*' AND tn.tagid=t.id AND tn.noteid=n.id

Here is my question--I want to turn this query into a Boolean function "hasTag" so I can write complex queries concisely in a form textbox with a "filter on" and "filter off" button. Those queries would look like: "hasTag('Partic') AND not(hasTag('Citiz')". I'm not sure how to start. Is this a VBA function using DoCmd.RunSQL?

Thank you
-david

[pbaldy]

I'm not really clear on the goal, but it sounds like you want to pass a portion of a tag and see if it has a note? You wouldn't use RunSQL, as that will only run an action query, which that is not. You could open a recordset on SQL built using the input parameter and test whether or not it returned a record (test for EOF). This might give you a start:

http://www.baldyweb.com/Function.htm

plus of course you'll need to build SQL and use it in a recordset.

[June7]

Don't understand the query - there are no join clauses. Are you getting expected results?

Don't really understand what you want to accomplish. You want to run a query that filters records where 'Partic' is in tag but 'Citiz' is not? DoCmd.RunSQL is used only with action SQL's like INSERT, UPDATE, DELETE. Can use DoCmd.OpenQuery to open an Access SELECT query.

Just what is it you want to apply 'filter on' and 'filter off' to?

[pbaldy]

there are no join clauses. Are you getting expected results?

David used the older style of criteria instead of joins, which I wouldn't use but probably returns the expected result.