parameter query

[al9315]

I posted question earlier, cannot find it ??
Record Collection - Parameter Query - e.g. Track called "Bye Bye Blackbird"
- Search all records - track 1,2,3...... for any instance of e.g. "Bye Bye"
???

 

[al9315]

Hi Chris, I greatly appreciate you taking the time to reply !! BUT - you
are obviously way beyond me. I am trying to learn Access2003 on my own !?
I have managed to import all the data from Dataease where I successfully -
years ago wrote the Database - all worked !!
I am getting stuck now - not sure where to type all this Select etc .?

Thanks again for trying to help !!!!
Al

 

[Chris2]

I posted question earlier, cannot find it ??
Record Collection - Parameter Query - e.g. Track called "Bye Bye Blackbird"
- Search all records - track 1,2,3...... for any instance of e.g. "Bye Bye"
???

Oh, a record collection, then.
Note, I'll use Album, records/record would be bad to use as
entity/attribute names.

CREATE TABLE Albums
(AlbumID AUTOINCREMENT
,NameAlbum TEXT(72) NOT NULL
,NameArtist TEXT(72) NOT NULL
,DateRelease DATETIME
,CONSTRAINT pk_Records PRIMARY KEY (RecordID)
)

CREATE TABLE Tracks
(TrackSequence BYTE NOT NULL
,AlbumID LONG NOT NULL
,NameTrack TEXT(72) NOT NULL
,CONSTRAINT pk_Tracks PRIMARY KEY (TrackSequence)
,CONSTRAINT fk_Tracks_Albums FOREIGN KEY (AlbumsID)
REFERENCES Albums (AlbumID)
)


It should be like (but pretty simple, so it should work):

SELECT T1.NameTrack
FROM Tracks as T1
WHERE Track = "Bye Bye Blackbird";


(This is untested.)


Sincerely,

Chris O.

 

[Chris2]

SELECT T1.NameTrack
FROM Tracks as T1
WHERE Track = "Bye Bye Blackbird";

Oops, I typoed.

WHERE T1.NameTrack = "Bye Bye Blackbird";


My apologies.


Sincerely,

Chris O.

 

[Chris2]

Oops, I typoed.

WHERE T1.NameTrack = "Bye Bye Blackbird";

<grr>

It should be:

WHERE T1.NameTrack LIKE "*Bye Bye*";

 

[al9315]

Hi Chris,
I am almost speechless !
You must be brilliant ?! - I did try a few years ago to learn VB - took me a
year to write a "held together with tape" programme which worked.
Perhaps I should give up now !?
I tried what you said - it works too !!
Whetehr I can incorporate these ideas inot my database remians to be seen
I am so pleased with your efforts to help me, thank you !!!
Al

 

[Chris2]

Hi Chris, I greatly appreciate you taking the time to reply !! BUT - you
are obviously way beyond me. I am trying to learn Access2003 on my own !?
I have managed to import all the data from Dataease where I successfully -
years ago wrote the Database - all worked !!
I am getting stuck now - not sure where to type all this Select etc ..?

Thanks again for trying to help !!!!
Al

Sample Tables:
Note: I used only two Track Fields for simplicity's sake.

Paste the following into a query in SQL View, then execute it.

CREATE TABLE UnnormalizedAlbums
(AlbumID AUTOINCREMENT
,NameAlbum TEXT(72) NOT NULL
,Track1 TEXT(72) NOT NULL
,Track2 TEXT(72) NOT NULL
)

Load the table with the following data:

NameAlbum, Track1, Track2
My Album, My Song 1, My Song 2
Your Album, Your Song 1, Bye Bye Blackbird
The Album, A Song 1, A Song 2
Their Album, Bye Bye Blackbird, Their Song 2


Paste the follwoing into a new blank Module in the Visual Basic
Editor.
Execute it by typing "? CheckMultipleColumns("Bye Bye")" in the
immediate window and pressing Enter:


Public Function CheckMultipleColumns(SearchParameter As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim lngRowFound As Long
Dim strSQL As String
Dim strTrack As String

strSQL = "SELECT * FROM UnnormalizedAlbums;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
lngRowFound = 0
strTrack = "Track"

rs.MoveFirst

With rs
Do Until .EOF
For Each fld In .Fields
If Left(fld.Name, 5) = strTrack Then
If fld.Value Like "*" & SearchParameter & "*" Then
lngRowFound = .Fields("AlbumID").Value
' The following will print results in the
'immediate window in the Visual Basic Editor.
' It needs to be replaced with whatever you
'want to do with the output data.
Debug.Print lngRowFound
End If
End If
Next
.MoveNext
Loop
End With

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function




This will get very slow for the full number of columns and large
numbers of rows.

It requires that you pass it the pattern the user wants to search for.

It displays the AlbumID on row the search pattern was located, or 0 if
nothing at all.

The part that displays the AlbumID in the immediate window will have
to be changed to whatever form of data output/manipulation you
require.

Note: This was tested on only limited data, and for only a few
minutes. Please investigate further.

Note: If the complexity of the above is compared to my original
solution . . .


Sincerely,

Chris O.

 

[Chris2]

Hi Chris,
I am almost speechless !
You must be brilliant ?! - I did try a few years ago to learn VB - took me a
year to write a "held together with tape" programme which worked.
Perhaps I should give up now !?
I tried what you said - it works too !!
Whetehr I can incorporate these ideas inot my database remians to be seen
I am so pleased with your efforts to help me, thank you !!!
Al

Al,

Brilliant . . . nah. Wait 'till you see some of mistakes I can make.


However, you are definitely welcome.


Sincerely,

Chris O.