compare and combine data in two tables

[Man6434]

For 2007
Origin st Origin city Destn St Destn city Paid Amount

A B C D 230
E F G H 489
I J K L 589
For 2006
Origin st Origin city Destn St Destn city Paid Amount

A B C D 259
E F G H 489
M N O P 589

I want to compare Paid amount for 2007 and 2006 in a single table across all the lanes in the following format.
How should i write a query or a form or a report to accomplish that? some field will be empty as shown


Origin st Origin Destn st city Paid amt2007 Paid amt 2006
A B C D 230 259
M N O P - 589

 

[Remou]

Welcome to Tek-Tips, Man6434

You can use the query design screen to build such a query. Add both the tables and then drag the link field(s) from one table to the other, matching each up. Add the fields that you want to the grid by dragging or double-clicking. Set and criteria by typing on the Criteria line. You should end up with something like this (SQL View):

[tt]SELECT tbl07.[Origin st],tbl07.[Origin city],tbl07.[Destn St],tbl07.[Destn city],tbl07.[Paid Amount] AS Paid2007,tbl06.[Paid Amount] AS Paid2006
FROM tbl07
INNER JOIN tbl06
ON tbl07.[Origin st]=tbl06.[Origin st]
AND tbl07.[Origin city]=tbl06.[Origin city]
AND tbl07.[Destn St]=tbl06.[Destn St]
AND tbl07.[Destn city]=tbl06.[Destn city][/tt]

It is rarely a good idea to have spaces in field names, but perhaps the field names are examples?

You may wish to look at these links:

http://www.tek-tips.com/threadminder.cfm?pid=701

http://msdn2.microsoft.com/en-us/library/Aa140015(office.10).aspx

http://msdn2.microsoft.com/en-us/library/Aa139977(office.10).aspx

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

http://sqlservercode.blogspot.com/

http://r937.com

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

As for Tek-Tips, please be sure to read the FAQs, especially faq181-2886