Help with my first access database please

[wildtech]

Hi I originally posted this in 'queries' and was advised that I probably need
a form. I am working with my first database in Access 2003 which I want to
use to
locate files using keywords and need some help with a query. My tables are:

File ID
File name
File type
File location

Keyword ID
Keyword

File Name ID
Keyword ID

Each file name is associated with multiple keywords and I wish to be able to
find files which are associated with a chosen keyword or keywords. How do I
construct the query or form?

Thanks for your help

 

[Allen Browne]

Can you tell us a bit more about this?

What kind of form do you have here?
Is it in Form View? Or in Continuous view?

Roughly how many keywords do you expect to use?
Hundreds? Thousands? Tens of thousands?
(This will help determine whether you can use a combo box.

I'm assuming that your 3rd table actually stores the FileID and KeywordID.
If so, you may be able to place an unbound combo in the Form Header section,
and set its AfterUpdate event procedure to filter the form to show only
matching files. The code would be something like this

Private Sub cboFilterKeyword_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save any edits first.
If IsNull(Me.cboFilterKeyword) Then
Me.FilterOn = False 'Remove the filter
Else
strWhere = "EXISTS (SELECT [FileID] FROM [Table3] WHERE
([Table3].[FileID] = [Table1].[FileID]) AND ([Table3].[KeywordID] = " &
Me.cboFilterKeyword & "))"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[wildtech]

Can you tell us a bit more about this?

What kind of form do you have here?
Is it in Form View? Or in Continuous view?

Roughly how many keywords do you expect to use?
Hundreds? Thousands? Tens of thousands?
(This will help determine whether you can use a combo box.

I'm assuming that your 3rd table actually stores the FileID and KeywordID.
If so, you may be able to place an unbound combo in the Form Header section,
and set its AfterUpdate event procedure to filter the form to show only
matching files. The code would be something like this

Private Sub cboFilterKeyword_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save any edits first.
If IsNull(Me.cboFilterKeyword) Then
Me.FilterOn = False 'Remove the filter
Else
strWhere = "EXISTS (SELECT [FileID] FROM [Table3] WHERE
([Table3].[FileID] = [Table1].[FileID]) AND ([Table3].[KeywordID] = " &
Me.cboFilterKeyword & "))"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

As I am new to Access I can not answer your first query.

The total number of keywords will only run into the hundreds (169 so far)
and I would probably search on, at most, four at a time.

The third table has a single entry for each file name and keyword combination.

I hope this adds the additional information you were looking for.

I am afraid the code is, as they say, "all Greek to me." Perhaps I am
attempting something beyond my computing skills. However I will show this to
my son who has been trying to work this out too and who has worked with
Access before as it will undoubtedly mean more to him than me

Many thanks for your reply and help.