Solved

How to fetch data from two table with comparision between date and column

Posted on 2010-11-08
9
468 Views
Last Modified: 2012-05-10
Dear Sir,
      I have two table No. 1 check Drawn Statement(CDS Table) 2nd One is Bank Statement table.

Table No. 1  field is as below :-
DATE  Office CODE      office NAME       CHEQUENO       AMOUNT
Table 2 field is as below :-
Date      Cheque No.      Amount

Both the table have data of months sep, oct, nov.
As u see both the table have cheque no field is comman.

Now I want a report of unmatched cheque of particular month. Say I want to report of month 9/2010. I want to know that which cheque issued in the month of 9/2010 but they don’t Appear in the month of 9/2010 in the bank statement. Similarly I want which cheque issued in the month of 9/2010 and they present in the bank in the month of 9/2010.
1.      Unmatched cheque report of 9/2010 out three months.
2.      Matched cheque report of 9/2010 out three months.
I want to do this in ms access please tell me the query of these reports.
Thanks

0
Comment
Question by:Manojtanwar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 4

Expert Comment

by:incerc
ID: 34083083
Hello,

1. Unmatched cheques

a) Using subselects :
SELECT * FROM CDS WHERE Date="9/2010" AND CheckNo NOT IN
(Select CheckNo from BankStatement WHERE Date="9/2010");

b) Using LEFT JOIN:
SELECT * FROM CDS LEFT JOIN BankStatement ON CDS.CheckNo = BankStatement.CheckNo
WHERE  BankStatement.CheckNo IS NULL AND CDS.Date=''9/2010" AND BankStatement.Date="9/2010";

2. Matched cheques

SELECT * FROM CDS INNER JOIN BankStatement ON CDS.CheckNo = BankStatement.CheckNo
WHERE  CDS.Date=''9/2010" AND BankStatement.Date="9/2010";

(using subselects, change "NOT IN" with "IN")

You need to modify the above queries in order to compare the month and year of the Date field, using the Month(Date) and Year(Date) functions. It depends also on how you keep the Date data.

Here is more on the Date/Time field manipulation on MS Access :
http://support.microsoft.com/kb/210276

Here is a list of most used MS Access functions :
http://www.techonthenet.com/access/functions/
0
 

Author Comment

by:Manojtanwar
ID: 34083469
Dear incerc:

i put that query for unmatched cheque but it works find until i have sepmonth data but when i insert sep and oct data in the both table. I put between date parameter on date field of cds table. but it compare sep data of cds table with sep and oct of bank statement table. then i put another between date parameter on  date field of bank statement but after that not data is fetch.    I put below noted query :-

SELECT [OP Divn Sohna CDS].Date, [OP Divn Sohna CDS].DCODE, [OP Divn Sohna CDS].D_NAME, [OP Divn Sohna CDS].CHEQUENO, [OP Divn Sohna CDS].AMOUNT, [Bank Statement OBC].Date
FROM [OP Divn Sohna CDS] LEFT JOIN [Bank Statement OBC] ON [OP Divn Sohna CDS].CHEQUENO = [Bank Statement OBC].[Cheque No#]
WHERE ((([OP Divn Sohna CDS].Date) Between [Start Date] And [End Date]) AND (([Bank Statement OBC].[Cheque No#]) Is Null) AND (([Bank Statement OBC].Date) Between [Start Date] And [End Date]));

Pls solve my problem.

Thanks

0
 
LVL 44

Expert Comment

by:GRayL
ID: 34085183
This do it?

SELECT a.ChequeNo, a.[Date], b.[Date] FROM [OP Divn Sohna CDS] a LEFT JOIN [Bank Statement OBC] b ON a.ChequeNo = b.[Cheque No#] Where Format((a.[Date]),"yymm") = Format(b.[Date],"yymm") AND Format(a.[Date],"yymm") IN ("1009","1010","1011");
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:Manojtanwar
ID: 34090147
Dear GRayL:
 This is also not working or i put wrongly. I attach excel file with data this file have two sheet one is with data of issue of cheque and second one is bank statement. i want report of  unmatched but comparing to same months like if cheque issued in sep then i have to compare with sep month data in bank statment. Remember bank statment having two months data sep and oct.

Thanks
sample.xlsx
0
 
LVL 4

Expert Comment

by:incerc
ID: 34092685
I'm sorry, in my left join select there was an error.

Please try this way :

SELECT [OP Divn Sohna CDS].Date, [OP Divn Sohna CDS].DCODE, [OP Divn Sohna CDS].D_NAME, [OP Divn Sohna CDS].CHEQUENO, [OP Divn Sohna CDS].AMOUNT, [Bank Statement OBC].Date
FROM [OP Divn Sohna CDS] LEFT JOIN [Bank Statement OBC] ON [OP Divn Sohna CDS].CHEQUENO = [Bank Statement OBC].[Cheque No#]
WHERE ((([OP Divn Sohna CDS].Date) Between [Start Date] And [End Date]) AND ((([Bank Statement OBC].[Cheque No#]) Is Null) OR (([Bank Statement OBC].Date) NOT Between [Start Date] And [End Date])));

Basically, the WHERE clause was wrong, in my example it should be :

SELECT * FROM CDS LEFT JOIN BankStatement ON CDS.CheckNo = BankStatement.CheckNo
WHERE CDS.Date=''9/2010" AND (BankStatement.CheckNo IS NULL OR BankStatement.Date <> "9/2010");
0
 

Author Comment

by:Manojtanwar
ID: 34100400
Dear
incerc:

This query working fine but every data repeat two times.

SELECT [OP Divn Sohna CDS].Date, [OP Divn Sohna CDS].DCODE, [OP Divn Sohna CDS].D_NAME, [OP Divn Sohna CDS].CHEQUENO, [OP Divn Sohna CDS].AMOUNT, [Bank Statement OBC].Date
FROM [OP Divn Sohna CDS] LEFT JOIN [Bank Statement OBC] ON [OP Divn Sohna CDS].CHEQUENO = [Bank Statement OBC].[Cheque No#]
WHERE ((([OP Divn Sohna CDS].Date) Between [Start Date] And [End Date]) AND ((([Bank Statement OBC].[Cheque No#]) Is Null) OR (([Bank Statement OBC].Date) NOT Between [Start Date] And [End Date])));
Pls resolve.
Thanks
Manoj
0
 
LVL 4

Expert Comment

by:incerc
ID: 34100587
In this case, please try select distinct , like this :

SELECT DISTINCT [OP Divn Sohna CDS].Date, [OP Divn Sohna CDS].DCODE, [OP Divn Sohna CDS].D_NAME, [OP Divn Sohna CDS].CHEQUENO, [OP Divn Sohna CDS].AMOUNT, [Bank Statement OBC].Date
FROM [OP Divn Sohna CDS] LEFT JOIN [Bank Statement OBC] ON [OP Divn Sohna CDS].CHEQUENO = [Bank Statement OBC].[Cheque No#]
WHERE ((([OP Divn Sohna CDS].Date) Between [Start Date] And [End Date]) AND ((([Bank Statement OBC].[Cheque No#]) Is Null) OR (([Bank Statement OBC].Date) NOT Between [Start Date] And [End Date])));
0
 

Author Comment

by:Manojtanwar
ID: 34108808
Dear
incerc:

it working fine sir, thanks, can u explain the this statement 'WHERE ((([OP Divn Sohna CDS].Date) Between [Start Date] And [End Date]) AND ((([Bank Statement OBC].[Cheque No#]) Is Null) OR (([Bank Statement OBC].Date) NOT Between [Start Date] And [End Date])));' how it works. I enter date one time and bank date not between that date. how it works pls explain.

thanks
Manoj
0
 
LVL 4

Accepted Solution

by:
incerc earned 500 total points
ID: 34110030

Hi, I'm glad it works :)

Please see these links about LEFT JOIN (and other joins :) ) :

http://msdn.microsoft.com/en-us/library/bb208894%28office.12%29.aspx

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.

Basicaly, as an exemple, if you have T1 with fields PK (as primary key), Date :
pk1, date1
pk2, date2

T2 with fields T1_PK (as foreign key from T1), Date :
pk1, date1
pk1, date3

Select T1.PK, T1.Date, T2.T1_PK, T2.Date From T1 LEFT JOIN T2 ON T1.PK = T2.T1_PK will give you :

pk1, date1, pk1, date1
pk1, date1, pk1, date3
pk2, date2, null, null

Now, having the info from the 2 tables, to find a NON MATCH in T2 for a given date existing in T1, all you have to do is filter through the WHERE clause.
- T1.Date must be btw the selected dates
- T2.T1_PK must be null  (or I could say also "T2.Date Is Null" !), or T2.Date must not be between the selected dates  (null means that no data was in T2 for the given key in T1).

Therefore, adding the
WHERE T1.date = date1 AND (T2.Date <> date1 OR T2.T1_PK is null)
the query will return you :

pk1, date1, pk1, date3

In your case, you don't have a given date, but an interval, however, the idea remains the same (I used equality for simplicity purposes).

I hope this explanation helps!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question