Help with SQL query

[colinod]

I have an sql qery that looks at a database to search thousands of mp3 files, it works fine, we have a table that contains the mp3 information, title etc, we also have a table that contains the words that we search that are associated to specific mp3s, i am trying to get the form that has the results of the query return all the words from the second table that relate to the mp3 being viewed

the following is what i have at the moment and a copy of the database can be found at www.colinwebdes ign.co.uk/mp3 database.rar

SELECT [mp3].[mp3id],
       [mp3].[File],
       [mp3].[location],
       [mp3].[Artist],
       [mp3].[Information],
       [mp3].[filename],
       [mp3].[Information]

FROM mp3 INNER JOIN mp3voice
  ON [mp3].[mp3id]=[mp3voice].[mp3id]

WHERE ([mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_11]
   Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_2]
   Or  [mp3voice].[Voice Type]=[forms]![Form1]![selected_type_3]
   Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_4]
   Or  [mp3voice].[Voice Type]=[forms]![Form1]![selected_type_5]
   Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_6]
   Or  [mp3voice].[Voice Type]=[forms]![Form1]![selected_type_7]
   Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_8]
   Or  [mp3voice].[Voice Type]=[forms]![Form1]![selected_type_9]
   Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_10]
   Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_1])

GROUP BY [mp3].[mp3id],
         [mp3].[File],
         [mp3].[location],
         [mp3].[Artist],
         [mp3].[Information],
         [mp3].[filename]

HAVING (Sum(1)=[Forms]![Form1]![HiddenObject]);

any help or advice would be appreciated

[NeoPa]

Colin, I've rearranged your SQL so that it's legible (including addition of the [ CODE ] tags and losing some extraneous parentheses - probably added by Access), but even after that I'm not clear what you're asking.

Now I can read the SQL I can see that there's really not too much wrong with it, unless you want it to do something quite different.

I could suggest a change for your WHERE clause, but only to make it a little clearer and easier to maintain. What you have now is not wrong.

WHERE ([mp3voice].[Voice Type] In([Forms]![Form1]![selected_type_1],
                                  [Forms]![Form1]![selected_type_2],
                                  [Forms]![Form1]![selected_type_3],
                                  [Forms]![Form1]![selected_type_4],
                                  [Forms]![Form1]![selected_type_5],
                                  [Forms]![Form1]![selected_type_6],
                                  [Forms]![Form1]![selected_type_7],
                                  [Forms]![Form1]![selected_type_8],
                                  [Forms]![Form1]![selected_type_9],
                                  [Forms]![Form1]![selected_type_10],
                                  [Forms]![Form1]![selected_type_11]))

What exactly were you looking for?

[colinod]

I am trying to get a variable called Voice Type from the mp3voice table but only the one that relates to the currently viewed mp3 in my form that runs from the sql query

The mp3voice table has 2 fields one with the name of the file and one that contains aord associated with the file for search purposes so each file can have many words attached to it for the search, i want all of these words to appear on the form when viewing the file

i thought something like

SELECT [mp3].[mp3id],
       [mp3].[File],
       [mp3].[location],
       [mp3].[Artist],
       [mp3].[Information],
       [mp3].[filename],
       [mp3].[Information],
       [mp3voice].[Voice Type]

as the select statement with the last line as the extra but this does not work

I hope this helps you understand and thanks for pointing out the simpler where statement

[NeoPa]

As a full member now, you should know that we expect your code to be posted in [CODE] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

ADMIN.

PS. Having commented on this very point in my last post I admit to being more than a little surprised to see a recurrence of this so immediately.

[colinod]

Sorry could not figure out how to do the code tag bit so here it is again

I am trying to get a variable called Voice Type from the mp3voice table but only the one that relates to the currently viewed mp3 in my form that runs from the sql query

The mp3voice table has 2 fields one with the name of the file and one that contains aord associated with the file for search purposes so each file can have many words attached to it for the search, i want all of these words to appear on the form when viewing the file

i thought something like

SELECT [mp3].[mp3id],
       [mp3].[File],
       [mp3].[location],
       [mp3].[Artist],
       [mp3].[Information],
       [mp3].[filename],
       [mp3].[Information],
       [mp3voice].[Voice Type]

as the select statement with the last line as the extra but this does not work

I hope this helps you understand and thanks for pointing out the simpler where statement

[NeoPa]

Aah. You should have said before. We're more than happy to assist where needed in explaining things about the site :)

When posting a reply within a thread (a new thread has a bit more info thrown in) there is a yellow box on the right entitled REPLY GUIDELINES. This includes instructions on how to add the tags. However, that is the manual approach (in case you need to do it but you're preparing the post in a text editor or somewhere else.

When typing into the reply box you will see at the top of the box something like a toolbar. Various buttons to select which will help you to format your post. Each button has a ToolTip that explains its use if you hover the mouse pointer over it.

The one you're looking for to help with the [ CODE ] tags is the one that looks like a # character. If you have your code selected already, it will automatically enclose the tags around your selection.

Now to get on with seeing if I can't give some help for your question...

[NeoPa]

...I am trying to get a variable called Voice Type from the mp3voice table but only the one that relates to the currently viewed mp3 in my form that runs from the sql query

The mp3voice table has 2 fields one with the name of the file and one that contains aord associated with the file for search purposes so each file can have many words attached to it for the search, i want all of these words to appear on the form when viewing the file
...

Am I right in thinking then, that there may be more than one matching [Voice Type] from your [mp3Voice] table?

Where there is one you want it shown simply, but where there is more than one, you would like the list displayed?

[colinod]

Yes i think you have it each clip can have many entries into the mp3voice table but the field containing the file name will be the same for each file but each file could have many words for it

here is some data copied from the mp3voice table if that helps

mp3id                                                   voicetype
02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        male
02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        refreshing
02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        natural
02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        frisby
02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        relaxed
02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        calming
02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        soft sell
09 ASHBY - SILENCE OF THE LAMBS.MP3                     male
09 ASHBY - SILENCE OF THE LAMBS.MP3                     anthony hopkins
09 ASHBY - SILENCE OF THE LAMBS.MP3                     dramatic
09 ASHBY - SILENCE OF THE LAMBS.MP3                     hard hitting
09 ASHBY - SILENCE OF THE LAMBS.MP3                     movie promo
09 ASHBY - SILENCE OF THE LAMBS.MP3                     movie trailer

the bit after the mp3 filename is the word attached to the file for searching

[NeoPa]

I was afraid that was the case. You see this is not really an process for a database. Typically DBs process records, and aggregates of groups of records, but it has no way of processing within a group within SQL.

Having said that, you are not the first to ask for something like this so I have previously found a sort of work-around for doing something very like this. It uses a call from within the SQL into a VBA function that we need to make available.

See Combining Rows-Opposite of Union.

If you have any questions, please come back here (this thread) to ask them. The other thread is not the place to ask them.

[colinod]

Shouldnt the INNER join statement in my code enable me to get the details i want some how i thought that i would be able to get information from the mpvoice tabel as it is joined to the mp3 tabel when the mp3id in both

[colinod]

Just to say i have made this work through a different way, i mad a new qeury that gets the information from mp3voice if the id matches the id in my form fro the mp3 file this returns all the information for each seperate clip in a form i them made the form look like it was just a list of text using the properties

[NeoPa]

Shouldnt the INNER join statement in my code enable me to get the details i want some how i thought that i would be able to get information from the mpvoice tabel as it is joined to the mp3 tabel when the mp3id in both

Not if you're grouping, but not by that field.

A field in a GROUP BY query can either be grouped or aggregated.

If it is aggregated (as in this case) then clearly the individual elements of the group are not accessible.

[NeoPa]

Just to say i have made this work through a different way, i mad a new qeury that gets the information from mp3voice if the id matches the id in my form fro the mp3 file this returns all the information for each seperate clip in a form i them made the form look like it was just a list of text using the properties

Thank you for letting us know you no longer need this information.

I hope your project turns out well for you :)