Formula Parsing

Hi,

I have three tables in the following structure (simplified):

Table 1: Containing the customers
---------------
create table Customers
(
[cusID] int identity(1, 1) not null,
[cusName] varchar(25) not null
)

Table 2: Containing the customer data fields
-------------------
create table Data
(
[datID] int identity(1, 1) not null,
[datName] varchar(25) not null,
[datFormula] varchar(1500)
)

Table 3: Containing the customer data values
--------------------
create table Values
(
[cusID] int not null,
[datID] int not null,
[valValue] sql_variant
)

In this structure the user can add as many data fields to a customer as
he wants (e.g. Country, City, Email, Phone, ...). I have added triggers
which create a view similar to a pivot (I am working in SQL 2000) and
add triggers to the view so it is insertable, deletable and updateable.

What I would like to do, is allow the user to create new fields where
the values are based upon a calculation. This calculation would be done
through a formula similar to what he would do e.g. in excel (this
formula is stored in the dimFormula field then).

An example might help. Let's assume the user created a field 'Sales'
(containing last year's sales) and 'Invoices' (containing the number of
invoices that were created for him last year). Now, he wants to create
a field 'AvgSales' with the formula '[Sales]/[Invoices]'.

(Note that through adding these data fields, the above view was created
(let's assume it is called vw_Customers and contains the columns [ID],
[Name], [Sales], [Invoices], [AvgSales]).

What I am looking for is a function which can parse this formula into a
t_sql query which runs the calculation. So, the formula
'[Sales]/[Invoices]' would be translated into (let's assume there are
no records with NULL or zero invoices):

update vw_Customers
set [AvgSales] = [Sales]/[Invoices]
from vw_Customers

I am able to do the above with simple calculations (where you can even
use sql functions e.g. year, len, ...). Now I would like to take this
one step forward into the possibility of using functions with more
variables.

For example. Let's assume, the user wants to add a rating (field called
'Rating') to his customers based upon the result of 'AvgSales. He
enters the formula 'if([AvgSales] > 2500, 'A', 'B')'.

If anyone could help me on this, I would be very grateful. Thanks.

MMike wrote:
> Hi,
> I have three tables in the following structure (simplified):
> Table 1: Containing the customers
> ---------------
> create table Customers
> (
> [cusID] int identity(1, 1) not null,
> [cusName] varchar(25) not null
> )
> Table 2: Containing the customer data fields
> -------------------
> create table Data
> (
> [datID] int identity(1, 1) not null,
> [datName] varchar(25) not null,
> [datFormula] varchar(1500)
> )
> Table 3: Containing the customer data values
> --------------------
> create table Values
> (
> [cusID] int not null,
> [datID] int not null,
> [valValue] sql_variant
> )
> In this structure the user can add as many data fields to a customer as
> he wants (e.g. Country, City, Email, Phone, ...). I have added triggers
> which create a view similar to a pivot (I am working in SQL 2000) and
> add triggers to the view so it is insertable, deletable and updateable.
> What I would like to do, is allow the user to create new fields where
> the values are based upon a calculation. This calculation would be done
> through a formula similar to what he would do e.g. in excel (this
> formula is stored in the dimFormula field then).
> An example might help. Let's assume the user created a field 'Sales'
> (containing last year's sales) and 'Invoices' (containing the number of
> invoices that were created for him last year). Now, he wants to create
> a field 'AvgSales' with the formula '[Sales]/[Invoices]'.
> (Note that through adding these data fields, the above view was created
> (let's assume it is called vw_Customers and contains the columns [ID],
> [Name], [Sales], [Invoices], [AvgSales]).
> What I am looking for is a function which can parse this formula into a
> t_sql query which runs the calculation. So, the formula
> '[Sales]/[Invoices]' would be translated into (let's assume there are
> no records with NULL or zero invoices):
> update vw_Customers
> set [AvgSales] = [Sales]/[Invoices]
> from vw_Customers
> I am able to do the above with simple calculations (where you can even
> use sql functions e.g. year, len, ...). Now I would like to take this
> one step forward into the possibility of using functions with more
> variables.
> For example. Let's assume, the user wants to add a rating (field called
> 'Rating') to his customers based upon the result of 'AvgSales. He
> enters the formula 'if([AvgSales] > 2500, 'A', 'B')'.
> If anyone could help me on this, I would be very grateful. Thanks.
> M

The best advice I can give you is to not try doing this with pure SQL.
You'll save yourself a lot of headache if you take some data that's a
little more "raw" and manipulate it in some other programming language
to get the desired result.|||Mike (michael.matthys@.hotmail.com) writes:
> In this structure the user can add as many data fields to a customer as
> he wants (e.g. Country, City, Email, Phone, ...). I have added triggers
> which create a view similar to a pivot (I am working in SQL 2000) and
> add triggers to the view so it is insertable, deletable and updateable.
> What I would like to do, is allow the user to create new fields where
> the values are based upon a calculation. This calculation would be done
> through a formula similar to what he would do e.g. in excel (this
> formula is stored in the dimFormula field then).
>...
> For example. Let's assume, the user wants to add a rating (field called
> 'Rating') to his customers based upon the result of 'AvgSales. He
> enters the formula 'if([AvgSales] > 2500, 'A', 'B')'.

I can only echo "ZeldorBlat" don't do this in SQL. If you had been on
SQL 2005, you could possibly have used CLR modules for the task.

But I wonder if you are not barking up the wrong tree entirely. Have
you looked at Analysis Services? I'm completely ignorant about Analysis
Services myself, but I would not be surprised if it has some support
for what you are trying to do.

If you are dead set on doing this in SQL 2000, you have to choices:
1) require that the user uses T-SQL syntax, for instance
CASE WHEN [AvgSales] THEN 'A' ELSE 'B' END
2) Define you own forumla language, and parse it in client code and
define the columns in the views as the users defines his formulas.

Beside AS, you could also investigate what 3rd party products out
there that may address your needs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Look up the EAV design flaw you have re-discovered and stop writing SQL
like this. SQL is not a computational language; it is a database
language.

Formatting spoils when export to HTML Format

I have problem here. when I overlap two or more tables one on other and
export to PDF, it works fine. But when exported to HTML, it doesn't hold the
absolute position and renders tables sequentially.
Thanks,
JigneshThe same thing happens when you overlap charts. Charts render as images,
and are placed side-by-side in HTML rather than overlapping. I presume this
is because the HTML code required to overlap these items (charts, images,
tables), especially while maintaining appropriate transparency, would be
very complex, fragile and browser-specific.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jignesh Nakrani" <Jignesh Nakrani@.discussions.microsoft.com> wrote in
message news:068CD644-4EFF-40A6-B2D7-E86205EFC331@.microsoft.com...
>I have problem here. when I overlap two or more tables one on other and
> export to PDF, it works fine. But when exported to HTML, it doesn't hold
> the
> absolute position and renders tables sequentially.
> Thanks,
> Jignesh|||Jeff,
Thanks for the reply. Hope something comes up with the solution.
"Jeff A. Stucker" wrote:
> The same thing happens when you overlap charts. Charts render as images,
> and are placed side-by-side in HTML rather than overlapping. I presume this
> is because the HTML code required to overlap these items (charts, images,
> tables), especially while maintaining appropriate transparency, would be
> very complex, fragile and browser-specific.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Jignesh Nakrani" <Jignesh Nakrani@.discussions.microsoft.com> wrote in
> message news:068CD644-4EFF-40A6-B2D7-E86205EFC331@.microsoft.com...
> >I have problem here. when I overlap two or more tables one on other and
> > export to PDF, it works fine. But when exported to HTML, it doesn't hold
> > the
> > absolute position and renders tables sequentially.
> >
> > Thanks,
> > Jignesh
>
>

Formatting Query Data

When I run queries against tables in MSSQL2000 it returns dates with a time component and has way too many zeros after the decimal point.

Is there any functions or something I can put in the query so my data comes back with only a date for the date and only two decimal points for the numbers?You are generally best off leaving the formatting to the code that receives the data from the database. But you can use the Convert function to format a date and the round function for numeric values.

SELECT Convert(varchar,DateField,101),Round(NumericField,2)
FROM YourTable

However, Round may not work quite as you expect. It will round the value but not necessarily format it for display as you might expect. Other date formats are available, check Sql Server Books OnLine under the convert function for the options.|||I agree with McMurdo, however, that the best place to handle this is outside of the database, in your code that is displaying the data.|||I agree, this is what I wanted - the ability to have the data formatted correctly from SQL. I tried for ages to find a way to do that on the basis of what I new about MySQL (which has a lot of functions for this).

But I'll look into it, as ideally I want my data coming in right from the SQL Query, rather than .Net doing it. Indeed, there is another thread in another forum arguing that as people kept giving me the whole Eval solution.|||Can you strip trailing spaces as well - I have a CUSTNMBR that is 5 characters long put it pads it with four spaces to to make it 9 (the length of the field).

I'll investigate the day, so I may have the answer this morning (UK Time).|||Use RTRIM or LTRIM to strip spaces.sql

formatting issures using tables and mutiple matrixes

Hi. I have a report that pulls all info from a single dataset. However, one
field "type" contains either "a" or "b". I have two matrixes, One for the
description and count of type a and another for type b. I also have data
from the dataset that needs to be both before and after each matrix.
When i layout the tables and matrixes, I measure them EXACTLY next to each
other by adding the position of one item and the width of it to determin the
position of the next. Also put them all in a header of another table with
zero padding. I also make the cangrow and canshrink atrributes to false to
keep all my rows aligned.
No matter what I do, the report always comes out unaligned. I've tried
rectangles already and they didnt work either. sometimes the report will
break on the tables for x number of rows and break for the matrixes at y
number of rows.
Does anyone know how to make matrixes and tables line up side by side,
return the same number of rows per page (this hsould be a no brainer because
its getting its data from the same dataset and there are no forced breaks)
AND make sure all the rows are the same width to ensure what is really four
tables looks like one?
I can provied the rdl if anyone wants to see it.Sorry, forgot to mention. SQL2005, Reporting Services 2005
"cr" wrote:
> Hi. I have a report that pulls all info from a single dataset. However, one
> field "type" contains either "a" or "b". I have two matrixes, One for the
> description and count of type a and another for type b. I also have data
> from the dataset that needs to be both before and after each matrix.
> When i layout the tables and matrixes, I measure them EXACTLY next to each
> other by adding the position of one item and the width of it to determin the
> position of the next. Also put them all in a header of another table with
> zero padding. I also make the cangrow and canshrink atrributes to false to
> keep all my rows aligned.
> No matter what I do, the report always comes out unaligned. I've tried
> rectangles already and they didnt work either. sometimes the report will
> break on the tables for x number of rows and break for the matrixes at y
> number of rows.
> Does anyone know how to make matrixes and tables line up side by side,
> return the same number of rows per page (this hsould be a no brainer because
> its getting its data from the same dataset and there are no forced breaks)
> AND make sure all the rows are the same width to ensure what is really four
> tables looks like one?
> I can provied the rdl if anyone wants to see it.
>

Formatting exported Reports in Excel

Hi All,
I'm using SQL Services 2000, and have a report with 3 tables. I export
this to Excel and use Subscriptions to email this report daily. I have
inserted page breaks after each table and so each table gets printed
on separate sheets in the Excel spreadsheet. All this is fine.
Now my problem are:
1) How could I format the sheets in such a way that they print neatly
on 1 page? Right now, they spill on to the next page when it is
printed.
2) Also, how could I name these sheets so that they represent the
report the user is viewing?
I tried many options in the table properties..but couldn't figure it
out!
Any help in these issues will be greatly appreciated!
Thanks in advance.
SSIf you have grouping in your report, when you export it goes into diferent
sheets depending on the pages, Dont give grouping if possible and no page
breaks then it prints in one page. you can check in the preview itself, that
it should show 1 of 1 page and asnwering your 2nd part, It is not possible to
do Sheet naming.
Amarnath, MCTS
"SMS" wrote:
> Hi All,
> I'm using SQL Services 2000, and have a report with 3 tables. I export
> this to Excel and use Subscriptions to email this report daily. I have
> inserted page breaks after each table and so each table gets printed
> on separate sheets in the Excel spreadsheet. All this is fine.
> Now my problem are:
> 1) How could I format the sheets in such a way that they print neatly
> on 1 page? Right now, they spill on to the next page when it is
> printed.
> 2) Also, how could I name these sheets so that they represent the
> report the user is viewing?
> I tried many options in the table properties..but couldn't figure it
> out!
> Any help in these issues will be greatly appreciated!
> Thanks in advance.
> SS
>|||On Mar 13, 12:18 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> If you have grouping in your report, when you export it goes into diferent
> sheets depending on the pages, Dont give grouping if possible and no page
> breaks then it prints in one page. you can check in the preview itself, that
> it should show 1 of 1 page and asnwering your 2nd part, It is not possible to
> do Sheet naming.
> Amarnath, MCTS
Amarnath,
I guess I'll need to remove the page breaks.
I'll try this. Thanks!

Formatting Data

I have a table with two tables with colums that have a number value.
Example
Column One Column Two
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
4.0000000000000001E-2 4.0
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
I need to format the data so that the both tables and colums are the same.
Please help!!!!!!!!!!!!!!!!!!!1)What are the existing column data types?
2)If they are different , do an ALTER TABLE r=to make the same data type.
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A8DAEF06-2F30-49D5-BBAA-87A37A0B5F52@.microsoft.com...
> I have a table with two tables with colums that have a number value.
> Example
>
> Column One Column Two
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> 4.0000000000000001E-2 4.0
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> I need to format the data so that the both tables and colums are the same.
> Please help!!!!!!!!!!!!!!!!!!!
>|||>> I need to format the data so that the both tables and colums are the same
. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It looks like "number value" means a FLOAT and a
DECIMAL, so you are both vague and ambigous.
It also shows that the poster does not understand a tiered
artchitecture. Formattig is always done in the front end and never in
the database. Doesn't anyone learn basic programming concepts any
more?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141916365.071352.130990@.j33g2000cwa.googlegroups.com...
same. <<
> It looks like "number value" means a FLOAT and a
> DECIMAL, so you are both vague and ambigous.
CELKO, that statement is both repetative and redundant.
On a serious note, it is possible that the OP is trying to change the data
type to avoid problems with precision, although we can't be sure since they
are, in fact, both vague and ambiguous.|||Ok
I am totally new to SQL or any programming for that matter.
The Data Type is “Float” and Length is “8” this is the same for both
columns
My goal is to write a query that would give me the results where the values
are not equal
With the Data like this I am getting all rows when I run that query.
Thanks
Chris
"Jim Underwood" wrote:

> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1141916365.071352.130990@.j33g2000cwa.googlegroups.com...
> same. <<
> CELKO, that statement is both repetative and redundant.
> On a serious note, it is possible that the OP is trying to change the data
> type to avoid problems with precision, although we can't be sure since the
y
> are, in fact, both vague and ambiguous.
>
>|||That's a start. Now we know that both columns are floats and you are trying
to compare them, but the values stored are physically different by very
small (insignificant) amounts.
The question is, what difference is significant? Do you want these values
to be rounded to two decimal places then compared, or do you need precision
of 8 places?
What do the values actually represent? What are you doing with them, and
how are they being populated in the first place?
Properly understanding the problem and the desired results is crucial to
developing the right solution.
The following code will convert the values to decimal data type with 2
decimal values:
select cast(column1 as decimal(6,3)) as Column1, cast(column2 as
decimal(6,3)) as Column2 from SomeTable
where cast(column1 as decimal(6,3)) = cast(column2 as decimal(6,3))
When dealing with database questions, providing DDL and sample data is often
helpful as well.
Check out the following link for more details.
http://www.aspfaq.com/etiquette.asp?id=5006
Also, I apologize if I insulted you. It was meant merely as a joke. As for
CELKO, don't worry about him, he beats up everyone who misunderstands SQL in
any way. Every time someone suggests that records and rows are the same
thing he drowns a kitten.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A154B16F-8E61-4714-85C0-7193EECE2FB8@.microsoft.com...
> Ok
> I am totally new to SQL or any programming for that matter.
>
> The Data Type is "Float" and Length is "8" this is the same for both
columns
> My goal is to write a query that would give me the results where the
values
> are not equal
> With the Data like this I am getting all rows when I run that query.
> Thanks
> Chris
>
> "Jim Underwood" wrote:
>
the
data
they|||This is how I wrote the query
select cast
([Value] as decimal(6,3)) as [Value], cast([P02_029_COTERM-RATE] as
decimal(6,3)) as [P02_029_COTERM-RATE] from dbo.Copy_P2_AFS
where cast([Value] as decimal(6,3)) <> cast([P02_029_COTERM-RATE] as
decimal(6,3))
and the result I am getting is as follows
Value P02_029_C0rterm-Rate
.036 3.580
.036 3.580
.040 4.000
.036 3.580
.036 3.580
.036 3.580
.040 4.000
.036 3.580
What I am doing wrong
Thanks
"Jim Underwood" wrote:

> That's a start. Now we know that both columns are floats and you are tryi
ng
> to compare them, but the values stored are physically different by very
> small (insignificant) amounts.
> The question is, what difference is significant? Do you want these values
> to be rounded to two decimal places then compared, or do you need precisio
n
> of 8 places?
> What do the values actually represent? What are you doing with them, and
> how are they being populated in the first place?
> Properly understanding the problem and the desired results is crucial to
> developing the right solution.
> The following code will convert the values to decimal data type with 2
> decimal values:
> select cast(column1 as decimal(6,3)) as Column1, cast(column2 as
> decimal(6,3)) as Column2 from SomeTable
> where cast(column1 as decimal(6,3)) = cast(column2 as decimal(6,3))
> When dealing with database questions, providing DDL and sample data is oft
en
> helpful as well.
> Check out the following link for more details.
> http://www.aspfaq.com/etiquette.asp?id=5006
> Also, I apologize if I insulted you. It was meant merely as a joke. As f
or
> CELKO, don't worry about him, he beats up everyone who misunderstands SQL
in
> any way. Every time someone suggests that records and rows are the same
> thing he drowns a kitten.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:A154B16F-8E61-4714-85C0-7193EECE2FB8@.microsoft.com...
> columns
> values
> the
> data
> they
>
>|||Actually, I should have noticed this sooner...
If you look more closely at your original data...
Column One Column Two
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
4.0000000000000001E-2 4.0
3.5799999999999998E-2 3.5800000000000001
3.5799999999999998E-2 3.5800000000000001
Note the "E-2" at the end of the first column. This is scientific notation
indicating that the decimal point should be moved two places to the left.
The numbers in teh first row above are in fact 0.0358 and 3.580, so they
will not be made equal through rounding. in these examples, column2 =
Column1 * 100.
What is this data, where does it come from, and why are you comparing the
columns?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:18C6145C-5D4C-4CBD-8739-3D4C77CDF214@.microsoft.com...
> This is how I wrote the query
> select cast
> ([Value] as decimal(6,3)) as [Value], cast([P02_029_COTERM-RATE] as
> decimal(6,3)) as [P02_029_COTERM-RATE] from dbo.Copy_P2_AFS
> where cast([Value] as decimal(6,3)) <> cast([P02_029_COTERM-RATE] as
> decimal(6,3))
>
> and the result I am getting is as follows
>
> Value P02_029_C0rterm-Rate
> .036 3.580
> .036 3.580
> .040 4.000
> .036 3.580
> .036 3.580
> .036 3.580
> .040 4.000
> .036 3.580
> What I am doing wrong
> Thanks
> "Jim Underwood" wrote:
>
trying
values
precision
and
often
for
SQL in
are
the
since|||>> I am totally new to SQL or any programming for that matter. <<
Okay, I will go easier on you, but SQL is a reallllly bad first
programming language. It is a declarative language that is used only
for data base access. Powerful but strange to someone with only High
School algebra and business procedures for his model of algorithms.
You can do a simple "WHERE col1 <> col2" and SQL will follow the IEEE
Floating rules. The next question is, since this is your first
programming language, do you know how floating point numbers work?
You will need to research this.|||Jim,
I am not sure how I should write the query to do what you are saying.
We have to ERP system and I need to make sure the tax rate in each system
are the same. These rate change each month. Can you correst the following
query to get the result I need.
select cast
([Value] as decimal(6,3)) as [Value], cast([P02_029_COTERM-RATE] as
decimal(6,3)) as [P02_029_COTERM-RATE] from dbo.Copy_P2_AFS
where cast([Value] as decimal(6,3)) <> cast([P02_029_COTERM-RATE] as
decimal(6,3))
Thanks
You are the best
Chris
"Jim Underwood" wrote:

> Actually, I should have noticed this sooner...
> If you look more closely at your original data...
> Column One Column Two
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> 4.0000000000000001E-2 4.0
> 3.5799999999999998E-2 3.5800000000000001
> 3.5799999999999998E-2 3.5800000000000001
> Note the "E-2" at the end of the first column. This is scientific notatio
n
> indicating that the decimal point should be moved two places to the left.
> The numbers in teh first row above are in fact 0.0358 and 3.580, so they
> will not be made equal through rounding. in these examples, column2 =
> Column1 * 100.
> What is this data, where does it come from, and why are you comparing the
> columns?
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:18C6145C-5D4C-4CBD-8739-3D4C77CDF214@.microsoft.com...
> trying
> values
> precision
> and
> often
> for
> SQL in
> are
> the
> since
>
>

formatting columns in a view

I have a view that I created from 4 SQL tables in order to query data for a report. I can't change the format of columns in the original table but would like to format the columns in the view as a different data type.

The original table has the values formated as varchar, the info in the columns is numbers and I would like to have the values changed to decimal when the view is queried.

Is this even possible? Any help would be appreciated.

You should be able to cast them in the select clause of your view (Assuming ALL of the data in those columns is numeric)

CREATE VIEW v1

AS

SELECT CAST(charcol1 AS Decimal(18,2)) AS numcol1, ...

|||Perfect. Thank you!

Format of tables

Hello,

I have the following problem; In my application I have certain items that
have properties.

Item Property
--- --------
ToyBear Hairy, Soft, Brown
ToyCar Brown, Plastic, Wheels
ToyBall Round, Soft, Brown, Plastic

As you might expect I want to do queries on the properties; so property
Brown should yield all items listed above and Plastic should yield only
ToyCar and ToyBall.
The amount of properties is limitless, so making a separate field per
property seems to be madness. My question is how can I make a (or more)
tables that will enable me to search for items by applying one or more
properties?? What structure do I need to accomplish this?

Kind regards, Darius Blaszijk"Darius Blaszijk" <dhkblaszyjk@.zeelandnet.nl> wrote in message
news:41060ef3$0$13577$fb624cd1@.morenews.zeelandnet .nl...
> Hello,
> I have the following problem; In my application I have certain items that
> have properties.
> Item Property
> --- --------
> ToyBear Hairy, Soft, Brown
> ToyCar Brown, Plastic, Wheels
> ToyBall Round, Soft, Brown, Plastic
> As you might expect I want to do queries on the properties; so property
> Brown should yield all items listed above and Plastic should yield only
> ToyCar and ToyBall.
> The amount of properties is limitless, so making a separate field per
> property seems to be madness. My question is how can I make a (or more)
> tables that will enable me to search for items by applying one or more
> properties?? What structure do I need to accomplish this?
> Kind regards, Darius Blaszijk

The obvious thing would be to have two tables - TB_Item and TB_Property.
TB_Property has a foriegn key referencing into TB_Item.

Therefore, you're definition would look something like this:

CREATE DATABASE TB_Item (
name VARCHAR (30),

CONSTRAINT PK_TB_Item
PRIMARY KEY (name)
)

CREATE DATABASE TB_Property (
itemName VARCHAR (30)
propName VARCHAR (30)

CONSTRAINT FK_TB_Item_name
FOREIGN KEY (itemName)
REFERENCES TB_Approver_Type (name)
)

Then, to populate the database for the example of ToyBear given above, do:
INSERT INTO TB_Item (name) VALUES ('ToyBear')
GO
INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Hairy')
INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Soft')
INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Brown')

Hope that is clear, and answers your question,

Rowland.|||"Rowland Banks" <banksr0@.hotmail.com> wrote in message
news:ce544k$6mp$1@.sparta.btinternet.com...
> "Darius Blaszijk" <dhkblaszyjk@.zeelandnet.nl> wrote in message
> news:41060ef3$0$13577$fb624cd1@.morenews.zeelandnet .nl...
> > Hello,
> > I have the following problem; In my application I have certain items
that
> > have properties.
> > Item Property
> > --- --------
> > ToyBear Hairy, Soft, Brown
> > ToyCar Brown, Plastic, Wheels
> > ToyBall Round, Soft, Brown, Plastic
> > As you might expect I want to do queries on the properties; so property
> > Brown should yield all items listed above and Plastic should yield only
> > ToyCar and ToyBall.
> > The amount of properties is limitless, so making a separate field per
> > property seems to be madness. My question is how can I make a (or more)
> > tables that will enable me to search for items by applying one or more
> > properties?? What structure do I need to accomplish this?
> > Kind regards, Darius Blaszijk
> The obvious thing would be to have two tables - TB_Item and TB_Property.
> TB_Property has a foriegn key referencing into TB_Item.
> Therefore, you're definition would look something like this:
> CREATE DATABASE TB_Item (
> name VARCHAR (30),
> CONSTRAINT PK_TB_Item
> PRIMARY KEY (name)
> )
> CREATE DATABASE TB_Property (
> itemName VARCHAR (30)
> propName VARCHAR (30)
> CONSTRAINT FK_TB_Item_name
> FOREIGN KEY (itemName)
> REFERENCES TB_Approver_Type (name)
> )
> Then, to populate the database for the example of ToyBear given above, do:
> INSERT INTO TB_Item (name) VALUES ('ToyBear')
> GO
> INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Hairy')
> INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Soft')
> INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Brown')
> Hope that is clear, and answers your question,
> Rowland.
ADDENDUM:
I just read through your post again and I missed a bit. To extract the
information, use somethign similar to:

SELECT i.name
FROM TB_Item AS i, TB_Property AS p
WHERE i.name = p.itemName
AND p.itemName = 'Hairy'

hope that helps,

Rowland