10-18-2004, 08: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.
10-19-2004, 07:25 AM
Do you mean that, your record fetched looks like this?
10-19-2004, 06:58 PM
OK, are these fields numeric or text? And are these records from the same field, but in a different record? meaning:
record 1 - 30
record 2 - 7
Field1 = 37
Or are these two different fields?
10-19-2004, 09:16 PM
These are records from 2 different records that have the same field
10-20-2004, 06: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.
10-21-2004, 04: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
SELECT Column1, Column2 FROM myTable WHERE NOT IsNumeric(Column2);
so given the Data below Column1 and Column2 in myTable
the output would be