non SQL person needs SQL expression! 
Author Message
 non SQL person needs SQL expression!

I am only an occasional DB programmer with limited SQL experience. I've
written an app for someone that reads in 2 simple tables consisting of
(code, price) pairs - no other tables

What I want to do is combine these two tables into one and sum on each of
the codes. I'm fine with creating the third table in the database with
DAO/VB, its purely the SQL expression (if feasible) to populate the values
in the new table. There could be several thousand entries in each table. Its
probably easier to show with a diagram:

Table 1          Table 2          ==>   SUBTOTAL
Code Price       Code Price             Code Price
1    123         1    33                1    612  (=123+456+33)
1    456         2    44                2    44
3    55          5    66                3    55
15   76          15   10                5    66
...              ....                   15   86

My modest knowledge of SQL suggests to me it is some sort of "TABLE SUBTOTAL
UNION ALL..." expression but
the bit to do the addition is stumping me completely

Can anyone SQL Gods help me out with this expression?

Cheers




Thu, 01 Nov 2001 03:00:00 GMT  
 non SQL person needs SQL expression!
How about creating a simple view union'ing both tables:

create view v
as
select code, price from table1
union
select code, price from table2

The view would contain each row from both tables.  If you wanted the sums by
code you could select the sum from the view:

select code, sum(price) from v group by code

What you were probably missing was the group by; it creates one result row for
each group by expression (code in this case).

Quote:

> I am only an occasional DB programmer with limited SQL experience. I've
> written an app for someone that reads in 2 simple tables consisting of
> (code, price) pairs - no other tables

> What I want to do is combine these two tables into one and sum on each of
> the codes. I'm fine with creating the third table in the database with
> DAO/VB, its purely the SQL expression (if feasible) to populate the values
> in the new table. There could be several thousand entries in each table. Its
> probably easier to show with a diagram:

> Table 1          Table 2          ==>   SUBTOTAL
> Code Price       Code Price             Code Price
> 1    123         1    33                1    612  (=123+456+33)
> 1    456         2    44                2    44
> 3    55          5    66                3    55
> 15   76          15   10                5    66
> ...              ....                   15   86

> My modest knowledge of SQL suggests to me it is some sort of "TABLE SUBTOTAL
> UNION ALL..." expression but
> the bit to do the addition is stumping me completely

> Can anyone SQL Gods help me out with this expression?

> Cheers





Thu, 01 Nov 2001 03:00:00 GMT  
 non SQL person needs SQL expression!
Hi Howard,
    I hate to ask these questions but I do feel they are necessary before I can
help with a solution. Sounds like your doing this the hard way. Why do you have
this data in 2 tables? Also if you want to store the sums for some reason fine,
but normally in db programming you get you totals to generate in a report.
Please give more details. thanks.

John :)

Quote:

> I am only an occasional DB programmer with limited SQL experience. I've
> written an app for someone that reads in 2 simple tables consisting of
> (code, price) pairs - no other tables

> What I want to do is combine these two tables into one and sum on each of
> the codes. I'm fine with creating the third table in the database with
> DAO/VB, its purely the SQL expression (if feasible) to populate the values
> in the new table. There could be several thousand entries in each table. Its
> probably easier to show with a diagram:

> Table 1          Table 2          ==>   SUBTOTAL
> Code Price       Code Price             Code Price
> 1    123         1    33                1    612  (=123+456+33)
> 1    456         2    44                2    44
> 3    55          5    66                3    55
> 15   76          15   10                5    66
> ...              ....                   15   86

> My modest knowledge of SQL suggests to me it is some sort of "TABLE SUBTOTAL
> UNION ALL..." expression but
> the bit to do the addition is stumping me completely

> Can anyone SQL Gods help me out with this expression?

> Cheers





Thu, 01 Nov 2001 03:00:00 GMT  
 non SQL person needs SQL expression!

The solution Dana gave got round the problem for me

But to satisfy your curiosity!! :-)

The data is just (code, value) pairs. They can come from a number of
different sources.
There is no problem with normalisation and such like as the data comes in
single tables
from a number of external sources - other applications and hand held data
collection
devices. Typically there are 2 sources though, hence 2 tables. This query is
to
summarise them (subtotal them) prior to processing in a more regular VB App.

Based on the sql Dana gave, I created QueryDefs to generate this subtotal
from
the 2 external tables and that seemed to work fine and nice and quick.

Howard
Edinburgh, Scotland



Quote:
> Hi Howard,
>     I hate to ask these questions but I do feel they are necessary before
I can
> help with a solution. Sounds like your doing this the hard way. Why do you
have
> this data in 2 tables? Also if you want to store the sums for some reason
fine,
> but normally in db programming you get you totals to generate in a report.
> Please give more details. thanks.



Thu, 01 Nov 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Error 20639: SQL Expression error - Error in creating SQL Expression COM Parser (cpeaut32)

2. Need help entering SQL Expressions in CR

3. Need Regular Expression to strip non-numeric characters from string

4. VB/Access question from a dedicated SQL person!

5. SQL -- Needing a Hint with SQL statements.

6. Using Vbscript and SQL-DMO to connect to SQL Server and run a T-SQL script

7. changing ACCESS sql ro SQL sql

8. Problem with SQL expressions.

9. Data type mismatch in SQL criteria expression - Access 97

10. SQL expression to pass value to textbox in form

11. Building SQL Expressions in Code

12. Acc20: Working Querydef's SQL Property Yields Unsupported Join Expression

 

 
Powered by phpBB® Forum Software