Help with SQL
Sir,
I have two tables and I need to show incoming amount from each against the range of days selected by the user. I also need to total the two amounts where the dates are the same. My database is access.
TableA
date amountOne
TableB
date amountTwo
The result I want to show is eg
Date amountOne amountTwo Total
02/01 1000 1000
02/02 500 1000 1500
02/03 0
and so on
Thank you very much for your time and consideration.
Adel
Software/Hardware used:
Software/Hardware used:
ASKED:
September 21, 2005 10:06 PM
UPDATED:
September 23, 2005 5:24 PM
RELATED QUESTIONS
Answer Wiki:
Are the dates in the table the primary key? If so, you can simply use the Access query designer to join the two tables on the date field. Drop the date from one of the tables in the first column, amountone from tableA in the second column and amounttwo from tableB in the third column. Then in the fourth column, enter Total: tableA.amountone + tableB.amounttwo.
If the dates in the tables are not unique. Meaning there can be multiple records with the same date, you must first create a query that will sum the amounts grouped by the date. Do this for each table that can have duplicate dates. Then create a query as describe above but use the queries justed created to sum the amounts grouped by date instead of the tables.
To see all answers submitted to the Answer Wiki: View Answer History.
For the inline view idea to work, you would need to “Union All”. Also for MS Access, the syntax for amountone:=null and amounttwo:=null is invalid plus it wouldn’t achieve what is wanted. You need to do this:
Select Date, sum(amount) As Total from (select Date, AmountOne as amount from TableA union all select Date, AmountTwo as amount from TableB) group by date;
Its been years since I used access, but its simpler to work on the query in access and save it. Then either (a) cut/paste the SQL into your code or (b) run your select on the saved query (like its a SQL server view). Views are supported by most major DBMS vendors. The saved query can even compute the total to a column called DailyTotal. Then you have a really simple SQL like:
select Date,DailyTotal from MySavedQuery
When you migrate from Access to another DBMS the saved query becomes a VIEW and you don’t have to change the backend code. This makes it easier to separate your program logic from DBMS specific SQL syntax. Plus, the view on the server should execute faster that passing up a complex join that needs to be parsed and analyzed.
Good luck