Hello,
I have a table in database that contains property information. I need to
select the distinct village that each property but the Village name is
in a column with the Property name and seperated by a comma.
Example Data::
RecordID PropertyName Town
1 MyHouse 1, big Village Main Town
2 MyHouse 2, big Village Main Town
3 MyHouse 3, big Village Main Town
4 MyHouse 1, Small Village Main Town
5 MyHouse 2, Small Village Main Town
6 MyHouse 3, Small Village Main TOwn
7 MyHouse 1, Tiny Village Main TOwn
8 MyHouse 2, Tiny Village Main Town
9 MyHouse 3, Tiny Village Main Town
10 MyHouse 4, Tiny Village Main TOwn
I want the DB to return the following result: -
Big Village
Small Village
Tiny Village
This is the data I have to work with and there is no way of changing the
schema of the DB as I have no control over it.
Any ideas how I would do this?
Regards,
Stuart
4  1226 
To do this you could get distinct name from the DB in order, and then split
each into an array, if previous one is the same as current ignore it.
I don't think there is a way you can do this in a SQL query, just a bunch of
jigger pokery to get the output you want.
Obviously, if you could add a new field to the db it would be far easier,
but thats easy for me to say.
Good luck.
Stu
stuart wrote: Hello,
I have a table in database that contains property information. I need
to select the distinct village that each property but the Village
name is in a column with the Property name and seperated by a comma.
Example Data::
RecordID PropertyName Town
1 MyHouse 1, big Village Main Town
2 MyHouse 2, big Village Main Town
3 MyHouse 3, big Village Main Town
4 MyHouse 1, Small Village Main Town
5 MyHouse 2, Small Village Main Town
6 MyHouse 3, Small Village Main TOwn
7 MyHouse 1, Tiny Village Main TOwn
8 MyHouse 2, Tiny Village Main Town
9 MyHouse 3, Tiny Village Main Town
10 MyHouse 4, Tiny Village Main TOwn
I want the DB to return the following result: -
Big Village
Small Village
Tiny Village
Does every record have "Main Town" in it? Does that have to be removed as
well? Or is "Main Town" in a separate field ... ? I guess it looks like
we're dealing with 3 fields, so I will go on that assumption.
What type and version of database are you using?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Bob,
I am using Foxpro (not through choice I may add). The column containing
'main town' could be another town name.
I know that this is not the best DB design in the world but it has been
forced on my as another system is using the DB for the interface and
update of data. I am only using it for the display of data.
Bob Barrows [MVP] wrote: stuart wrote:
Hello,
I have a table in database that contains property information. I need
to select the distinct village that each property but the Village
name is in a column with the Property name and seperated by a comma.
Example Data::
RecordID PropertyName Town
1 MyHouse 1, big Village Main Town
2 MyHouse 2, big Village Main Town
3 MyHouse 3, big Village Main Town
4 MyHouse 1, Small Village Main Town
5 MyHouse 2, Small Village Main Town
6 MyHouse 3, Small Village Main TOwn
7 MyHouse 1, Tiny Village Main TOwn
8 MyHouse 2, Tiny Village Main Town
9 MyHouse 3, Tiny Village Main Town
10 MyHouse 4, Tiny Village Main TOwn
I want the DB to return the following result: -
Big Village
Small Village
Tiny Village
Does every record have "Main Town" in it? Does that have to be removed as
well? Or is "Main Town" in a separate field ... ? I guess it looks like
we're dealing with 3 fields, so I will go on that assumption.
What type and version of database are you using?
Bob Barrows
I cannot help with foxpro syntax. Please try a foxpro group.
Bob Barrows
stuart wrote: Bob,
I am using Foxpro (not through choice I may add). The column
containing 'main town' could be another town name.
I know that this is not the best DB design in the world but it has
been forced on my as another system is using the DB for the interface
and update of data. I am only using it for the display of data.
Bob Barrows [MVP] wrote: stuart wrote:
Hello,
I have a table in database that contains property information. I
need to select the distinct village that each property but the
Village name is in a column with the Property name and seperated by
a comma.
Example Data::
RecordID PropertyName Town
1 MyHouse 1, big Village Main Town
2 MyHouse 2, big Village Main Town
3 MyHouse 3, big Village Main Town
4 MyHouse 1, Small Village Main Town
5 MyHouse 2, Small Village Main Town
6 MyHouse 3, Small Village Main TOwn
7 MyHouse 1, Tiny Village Main TOwn
8 MyHouse 2, Tiny Village Main Town
9 MyHouse 3, Tiny Village Main Town
10 MyHouse 4, Tiny Village Main TOwn
I want the DB to return the following result: -
Big Village
Small Village
Tiny Village
Does every record have "Main Town" in it? Does that have to be
removed as well? Or is "Main Town" in a separate field ... ? I guess
it looks like we're dealing with 3 fields, so I will go on that
assumption.
What type and version of database are you using?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: iainw |
last post by:
HI All,
1st post here, i wonder if you can help. We are about to upload CMS t
a windows server and keep getting 2 errors below. We need to go LIVE an
it's delaying us.
An error occured when updating the database.
The likely reason is that write permissions are not set on th
'database' folder.
Please remember that write and delete permissions are also required o
the 'content' folder and its subfolders.
|
by: Tarren |
last post by:
Hi:
The problem I am having is when I validate an xml file to a schema, it is
erroring out every element. I think this has something to do with me
defining/referencing the namespaces. I have searched on the net for a
while, but am still confused. Thanks in advance for help. Below is the
code I am working with. What am I not referencing/referencing incorrectly?
===================================
|
by: Jeff Blee |
last post by:
I am hoping someone can help me. I am making a Access 97 app for a
person and have run up against a problem to do with MS Graph. There is
a table that has a number of data elements and a date field and
entries are entered each month.
A graph is required that has three of the data elements represented
basically on the y axis and time as months along the x axis. So for
each month there is a group of three columns representing the data...
|
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: Chad |
last post by:
I have a problem that I am desperate to understand.
It involves dynamically adding controls to a Table control that is built as a result of performing a database query.
I am not looking to avoid the problem by avoiding the table control or resorting to databound controls that better manage state for me. I hope to understand how to solve the problem by using the Table web control and sticking to the approach of building the table at run...
| | |
by: Tim::.. |
last post by:
Can someone please help....
I'm having major issues with a user control I'm tring to create!
I an trying to execute a sub called UploadData() from a user control which I
managed to do but for some reason I keep getting the error:
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.
|
by: bob.herbst |
last post by:
I am trying to write a form that will add a guest to a guest table in a
MySQL database and then reload the page to display any guests that have
been added as well as redisplay the guest form to add more guests.
I have two problems I am running into. I can get the script to run just
fine and it will come up with the event, any guest that has been added
and the form, but when I click submit it doesn't reload the page and
for some unknown...
|
by: gunimpi |
last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431
********************************************************
VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help
wanted
********************************************************
For this teeny job, please refer to:
http://feeds.reddit.com/feed/8fu/?o=25
|
by: Sham |
last post by:
I am trying to perform the following query on a table that has been
indexed using Full Text Search.
The table contains multiple columns than have been indexed. (Below,
all xml columns are indexed).
dbo.maintable(ProfileID int pk)
dbo.fts_table(ProfileID int pk fk, col1 xml, col2 xml, col3 xml)
I want to perform a query that will return any rows that contain ‘x’
and ‘y’ in any columns. I.e. ‘x’ could be in col1 and ‘y’ could be in
|
by: yfangl09 |
last post by:
I have one query with a list of people and required courses they have
to take and another with the same people and courses they have already
taken. How do I generate a query with required courses they have NOT
taken? Anybody have any suggestions?
|
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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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: 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: 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
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
