trying to create query with complex calculations

[englishnewbie]

........Well, complex to me.

1. I need to list records in my table in order of priority.

for some fields i have a numerical value, so it's simple to build an expression. but for others there is a text field.

for example a=100 , b=200 c= 300

how do i convert these values (or other text entries) into numerical form and add to the total of the first expression, which deals with all the numbhers.?

2. to create an average of several columns i need to determine which columns have data in them.

for example, if the column values are 1, 1, _, 1 the average should be 1, 3/3. Not .75

i've done it befroe, but can't remember how.......
thanks,

neil

 

[LogicalmanUS]

englishnewbie,

Can you give a bit more information as to the schema. In your question you have:
for example a=100 , b=200 c= 300

does this mean the value of the field is 'a=100' or 'b=200' or 'c=300' etc, or do you mean the text field is polpulated with '100' or '200' or 300' ?

Logicalman

 

[englishnewbie]

hi!

no,

the table i am using for the query has text fields because this is useful elsewhere.

i am trying to prioritise tasks based on several variables, INCLUDING whatever is in this text field. it could be various things, but if it is one of a couple of words like "cust" or "prop" i'd like to add either 250 or 500 to a figure which has been calculated elsewhere.

basiucally, an expression has calculated a lot of numeric values elsewhere, but i want to add to this number based upon the text field.

is that any clearer?

thanks, neil

 

[Golom]

I think LogicalmanUS was trying to determine if the text fields contain text like "b=200" or do they contain "200" but just formatted as text?

If it's the later then you can use Val(Field) to convert the text to a numeric on the fly so, for example, you could perform a numeric calculation with something like

... (Val(Field1) + Val(Field2)) As [Sum12], ...

If the former then you need to do some parsing on the field to find the "=" sign and then just use the characters that follow it as your numeric value.

 

[LogicalmanUS]

englishnewbie,

Golom is perfectly correct in his belief.

From your last reply, it seems you are storing both text and numerical data in the same field, e.g.:

Cust=110
Prop=220

and you want to determine what is in the field, e.g. Cust or Prop, and then change the text to be

Cust=360
Prop=720

If this is the case, then there is a basic flaw in the schema. It would be better to split this column into 2, the first being a varchar to store the type (Cust, Prop etc) and the second an Int to store the value.

If it is not possible to change the schema, and the data is as described before, then you will need some code as described by Golom to parse out the type from the value, change the value, re-create the string and place it back in again. It's not difficult to write, but it seems this may be better resolved with a schema change, if possible.

It may be easier to understand the problem if you could post the table schema and a portion of the data as it appears in the table.

BTW neil, wherabouts in England? I hail from London then Dorset before emigrating to the US

Thanks,

Logicalman

 

[englishnewbie]

no, in the table i've just got text . some fields are "cust" some are "prop"

the query should convert these into numerical values.

is this any clearer?

in answer to your question kent - southampton - gloucester - bath and then emigrated to belgium, though i spend most of my time travelling to romania and lithuania!

.....so i need to remind myslef i'm english sometimes

 

[LogicalmanUS]

englishnewbie,

Here's the code you need:

<code>
select mytext,
case mytext
when 'Prop' then 250
when 'Cust' then 500
end
from tcase1
</code>

NOTES: mytext is the column containing the word you want to check and tcase1 is the table name.
The values returned are hard-coded in the syntax, yu could use a lookup (subquery) if this needs to be dynamic.

Kent - Southampton - Glocs - Bath, eh? So, a Brighton/Southampton/Swindon/Bristol fan then? lol

At least you are still (technically) in Europe. Losing one's heritage identity is easy in the US also - so I watch the Premier League each week!!

Logicalman

 

[englishnewbie]

good man! i watch as much premier league/champions league as i can, which is a lot in a county that shows BBC, dutch, belgian, french, italian german and spanish tv!

however, alas, i am actually a Villa fan, so will most probably be watching Nationwide Div 1 next season!

 

[englishnewbie]

meanwhile (and i'll probably post this as a new thread!):

i have two tables: company and people.

The link is the company field, which is text, a company name.

I've put these two tables together separately, importing the data.

I want to create a relationship between company field in one and company field in the other.
But, having done so, there seems to be no link.

I REALLY don't want to have to do these 500 entries manually, so what's wrong? 300 or so should match.....

 

[LogicalmanUS]

englishnewbie,

No probs, we'll get this done or I'm not a Fulham fan (and no laughing either LOL).

How did you link the tables? Was this in a diagram?

Try creating a Query using both tables, and link them via the company name, but alter the link so that the company table has precedence. You can see this by the fact the SQL statement will have a LEFT JOIN rather than an INNER JOIN. Make sure you select different fields from both tables and then look at the results. Hopefully, you should see (at least) some records returned having data in all columns. If not, try to reverse the link precedence and see the results again.

If there are no joined records returned, then it would appear that there are no matching values in the company name column on both tables. This could be a number of things, but I would make sure that there are no extra spaces in either of the columns data.

If you need to normalize these tables, then I would create an ID field on the Company table (If the company name is unique), make this the Primary Key, and then create a Foreign Key int column on the people table and popultae that with the PK matching the company name of the company table.

This can be done quite easily, by using an UPDATE query where company name = &quot;myCompany&quot;, for each company name.

Logicalman