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
12 1968 NeoPa 32,573
Recognized Expert Moderator MVP
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?
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 32,573
Recognized Expert Moderator MVP
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.
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 32,573
Recognized Expert Moderator MVP
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 32,573
Recognized Expert Moderator MVP
...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?
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 32,573
Recognized Expert Moderator MVP
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: James |
last post by:
My form and results are on one page.
If I use :
if ($Company) {
$query = "Select Company, Contact
From tblworking
Where ID = $Company
Order By Company ASC";
}
|
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use SUBSTRING(ProductName,
1, CHARINDEX('(', ProductName)-2).
I can get this result, but I had to use several views (totally
inefficient). I think this can be done in one efficient/fast query,
but I can't think of one.
In the case that one query is not...
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my predecessor, I hasten to add) so that each day it creates a
copy of the record for each company, changes the date to today's date,
and prompts the user for any changes of ratings on that day. The
resulting data table grows by approx 600 records per...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values...
|
by: Steve Patrick |
last post by:
Hi All
You guys are my last hope, despite spending money on books and hours reading
them I still can not achieve the results I need.
I have designed a database in Access 2000 based on 1 table, all has gone
very well with one exception. The table is based on applications made by
potential customers looking to buy franchise rights to particular locations
and as part of the process they are asked to list their preferred locations
1 to 4....
| |
by: Alan Lane |
last post by:
Hello world:
I'm including both code and examples of query output. I appologize if
that makes this message longer than it should be.
Anyway, I need to change the query below into a pivot table query. I'm
having trouble doing it. Help!
Here is my code so far:
Sub OldRegionQuery()
|
by: Takeadoe |
last post by:
Dear NG,
Can someone assist me with writing the little code that is needed to
run an update table query each time the database is opened? From what
I've been able to glean from this group, the Autoexec Macro looks like
the way to go. Could someone please assist?
Thank you very much!
Mike
|
by: mcmahonb |
last post by:
Hey people...
I've been searching this forum for a few hours and even though this topic has been went over from many different angles; I cannot seem to figure out how to make things work on my side.
I am trying to learn how to manipulate Dynamic Queries by forms via the example database: QrySampl.MDB, offered by Microsoft (as a learning tool, I suppose.) In particular, I am working with code from the example: "Query By Form (QBF) Using...
|
by: n |
last post by:
Hello! Here is a problem I hope you can point me to a solution. It
Problem: A teacher needs to know which lesson to teach.
A school has a curriculum with 26 lessons, A-Z.
For a given class, a random number of students arrive, each of which
has completed a random number of lessons taken at random from the
curriculum.
|
by: Jo |
last post by:
Hi there,
I'm Jo and it's the first time I've posted here. I'm in process of creating
a database at work and have come a little unstuck.....I'm a bit of a novice
and wondered if anyone could help. I work in a library and send out dual
language books to babies of dual or other nationality.
The db is to be used for logging a range of book titles and numbers ordered
and books sent out to individuals. I am trying to work out a way of...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |