New at this

big_manu
10-18-2004, 07:41 PM
Using Access I have a query where i have done a union select and was wondering if I can get the had the infomation added together Ex.

One comlumn that says "done" and below it one line says "7" and the second line says "30".

I was wondering if I can get it to say "37" instead of breaking it out into two lines.

I'm kinda new at this so any help would be appreciated.

Thanks!

NEOLLE
10-19-2004, 06:25 AM
HELLO big_manu,

Do you mean that, your record fetched looks like this?

RECORDFETCHED
---------------
done
30
7

big_manu
10-19-2004, 04:52 PM
yes

MKoslof
10-19-2004, 05:58 PM
OK, are these fields numeric or text? And are these records from the same field, but in a different record? meaning:

Field1

record 1 - 30
record 2 - 7

in query:

Field1 = 37

Or are these two different fields?

big_manu
10-19-2004, 08:16 PM
These are records from 2 different records that have the same field

MKoslof
10-20-2004, 05:23 PM
The problem is, you are trying to do a relational database scan (each record is evaluated, not each field within the record). So, records are evaluated as a whole, not by the individual fields within them. You can select various fields from within a tables records, do comparisons on these fields, etc. But each record is evaluated as a whole..meaning you can't simply add two fields without accounting for the entire record it is contained within.

Do you have a primary key field? If so, you can make pseudo row numbers in your query. Then, you can create a sub query that takes the value of each two records and merges them. This would get ugly..but I think you could do it.

Dennis DVR
10-21-2004, 03:06 AM
in addition to MK's suggestion using the Subquery you also need to use the union to add the non numeric value to the result of the query, in your case the word "Done".

you could try something like: (Access Database)

SELECT myTable.Column1, CStr(IIF(t.Field2 IS NULL,0,t.Field2)) AS Column2
FROM myTable LEFT JOIN [select sum(Val(Column2)) as Field2, Column1
FROM myTable WHERE Val(Column2)>0 GROUP BY Column1]. AS t ON
t.Column1=myTable.Column1 WHERE t.Field2 <> 0
UNION
SELECT Column1, Column2 FROM myTable WHERE NOT IsNumeric(Column2);


so given the Data below Column1 and Column2 in myTable

Column1 Column2
1111 Done
1111 30
1111 7
2222 50
2222 20
3333 10
4444 5
4444 2
5555 Done


the output would be

Column1 Column2
1111 37
1111 Done
2222 70
3333 10
4444 7
5555 Done

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum