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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Randym
    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;
    1,740 pointsBadges:
  • BeerMaker
    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
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: