By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,545 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Help with SQL query

100+
P: 347
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.colinwebdesign.co.uk/mp3 database.rar
Expand|Select|Wrap|Line Numbers
  1. SELECT [mp3].[mp3id],
  2.        [mp3].[File],
  3.        [mp3].[location],
  4.        [mp3].[Artist],
  5.        [mp3].[Information],
  6.        [mp3].[filename],
  7.        [mp3].[Information]
  8.  
  9. FROM mp3 INNER JOIN mp3voice
  10.   ON [mp3].[mp3id]=[mp3voice].[mp3id]
  11.  
  12. WHERE ([mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_11]
  13.    Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_2]
  14.    Or  [mp3voice].[Voice Type]=[forms]![Form1]![selected_type_3]
  15.    Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_4]
  16.    Or  [mp3voice].[Voice Type]=[forms]![Form1]![selected_type_5]
  17.    Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_6]
  18.    Or  [mp3voice].[Voice Type]=[forms]![Form1]![selected_type_7]
  19.    Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_8]
  20.    Or  [mp3voice].[Voice Type]=[forms]![Form1]![selected_type_9]
  21.    Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_10]
  22.    Or  [mp3voice].[Voice Type]=[Forms]![Form1]![selected_type_1])
  23.  
  24. GROUP BY [mp3].[mp3id],
  25.          [mp3].[File],
  26.          [mp3].[location],
  27.          [mp3].[Artist],
  28.          [mp3].[Information],
  29.          [mp3].[filename]
  30.  
  31. HAVING (Sum(1)=[Forms]![Form1]![HiddenObject]);
any help or advice would be appreciated
Aug 8 '08 #1
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. WHERE ([mp3voice].[Voice Type] In([Forms]![Form1]![selected_type_1],
  2.                                   [Forms]![Form1]![selected_type_2],
  3.                                   [Forms]![Form1]![selected_type_3],
  4.                                   [Forms]![Form1]![selected_type_4],
  5.                                   [Forms]![Form1]![selected_type_5],
  6.                                   [Forms]![Form1]![selected_type_6],
  7.                                   [Forms]![Form1]![selected_type_7],
  8.                                   [Forms]![Form1]![selected_type_8],
  9.                                   [Forms]![Form1]![selected_type_9],
  10.                                   [Forms]![Form1]![selected_type_10],
  11.                                   [Forms]![Form1]![selected_type_11]))
What exactly were you looking for?
Aug 9 '08 #2

100+
P: 347
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [mp3].[mp3id],
  2.        [mp3].[File],
  3.        [mp3].[location],
  4.        [mp3].[Artist],
  5.        [mp3].[Information],
  6.        [mp3].[filename],
  7.        [mp3].[Information],
  8.        [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
Aug 11 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Aug 11 '08 #4

100+
P: 347
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

Expand|Select|Wrap|Line Numbers
  1. SELECT [mp3].[mp3id],
  2.        [mp3].[File],
  3.        [mp3].[location],
  4.        [mp3].[Artist],
  5.        [mp3].[Information],
  6.        [mp3].[filename],
  7.        [mp3].[Information],
  8.        [mp3voice].[Voice Type]
  9.  
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
Aug 11 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
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...
Aug 11 '08 #6

NeoPa
Expert Mod 15k+
P: 31,186
...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?
Aug 11 '08 #7

100+
P: 347
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
Expand|Select|Wrap|Line Numbers
  1. mp3id                                                   voicetype
  2. 02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        male
  3. 02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        refreshing
  4. 02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        natural
  5. 02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        frisby
  6. 02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        relaxed
  7. 02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        calming
  8. 02 ALTMAN - HSBC BANK ( REFRESHING, NATURAL).mp3        soft sell
  9. 09 ASHBY - SILENCE OF THE LAMBS.MP3                     male
  10. 09 ASHBY - SILENCE OF THE LAMBS.MP3                     anthony hopkins
  11. 09 ASHBY - SILENCE OF THE LAMBS.MP3                     dramatic
  12. 09 ASHBY - SILENCE OF THE LAMBS.MP3                     hard hitting
  13. 09 ASHBY - SILENCE OF THE LAMBS.MP3                     movie promo
  14. 09 ASHBY - SILENCE OF THE LAMBS.MP3                     movie trailer
the bit after the mp3 filename is the word attached to the file for searching
Aug 11 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
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.
Aug 11 '08 #9

100+
P: 347
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
Aug 12 '08 #10

100+
P: 347
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
Aug 12 '08 #11

NeoPa
Expert Mod 15k+
P: 31,186
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.
Aug 12 '08 #12

NeoPa
Expert Mod 15k+
P: 31,186
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 :)
Aug 12 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.