BorlandTalk.com

Pawel · Guest

I'm trying to figure out if I can do the following task with queries or if I
have to write a query along with some code.

(I'm simplifying everything)
I have two tables.
Table1 has a code field.
Table1 does not hold duplicates of the code.
Table2 has a code field, an integer field and a positiveNegative field.
Table2 may have multiple entries with the same code.

What I need to do is use table1 to grab a list of codes. Then look up these
codes in table2 and total the associated integer values using
PositiveNegative to determine weather the integer is positive or negative.

One idea is to link table1 and table2 grabbing only the codes of interest.
Then ordering the query by code. Take the resulting query and write code
that goes through it, from beginning to end, totaling and restarting the
tally every time a new code is encountered. But I'm thinking there could be
a sleek query that could do all the work?

Iman L Crawford · Guest

"Pawel" <thejaffe21 (AT) hotmail (DOT) com> wrote in
news:40f449d5 (AT) newsgroups (DOT) borland.com:

Del Murray · Guest

You select data from table 1, you join it to table 2 on the code key, you do
a "sum" function in the sql statement and you do a "group by". Since this is
homework (very astute observation by Iman), you will have to dig for the
rest .. good luck.

Pawel · Guest

nope

i'm just the only programmer here

p
"Iman L Crawford" <ilcrawford.at.hotmail.dot.com> wrote

Pawel · Guest

how can i do a 'sum' function that only sums entries with the same code and
decides weather to treat that code as positive or negative depening on
another field?

or how do i tell it to sum only entries with the same code and the same
pos/neg field.. that way i can just do a simple compare of two 'sum'
functions.

i went with doing a join then group by and going through all the records
manually.. it was pretty quick and works well..

the reason i usually post to the group is that im the only person here and
my main sql resource is 'teach yourself sql in 24 hours'. i like to see if
other people have different ideas on how to do things.. i was thinking there
was some sort of sql function that i was totally missing..

anyways i guess my 'homework' is done now but if you have any ideas about
writing a more efficient query it would be appreciated.

p

"Del Murray" <Del.Murray (AT) N_S_CreditHawk (DOT) Net> wrote

Iman L Crawford · Guest

"Pawel" <thejaffe21 (AT) hotmail (DOT) com> wrote in
news:40f59811$1 (AT) newsgroups (DOT) borland.com:

Del Murray · Guest

the case statment will let you test a field and then return the postive or
negative of another field (field * -1) for negative