Manojtanwar
asked on
How to fetch data from two table with comparision between date and column
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
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
ASKER
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
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
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");
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");
ASKER
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
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
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");
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");
ASKER
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
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
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])));
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])));
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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/