We help IT Professionals succeed at work.

UNION ALL WITH WHERE CLAUSE - 1

Medium Priority
485 Views
Last Modified: 2012-05-11
Hi:
i am getting result using suggested query by  angelIII,
but data is not like what i wanted,
Please check attached file
desired and query data snap is in that file

Hope you understand my problem

Thanks in advance

SELECT CM.CM_Date AS Cdate, CM.Cash_MemoNo AS CmNo,0 as BookDate, 0 as LR_No, CM.CM_Total as CMAmt, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,0 as MemoNo, 0 as M_Date, CM.CNee as conName
from (CashMemo as CM
INNER JOIN ClientLedger ON (ClientLedger.CName = CM.CNee))
UNION ALL 
Select 0 as Rcpt_Date, 0 as CmNo, BD.Bdate as BookDate, BD.BiltyNo as LR_No, 0 as CM_Total, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,BD.CST as MemoNo, BD.GRDate as M_Date, BD.Consignee 
from (Bilty_Detail BD
INNER JOIN Receipt_CLNT CM ON (CM.G_Name = BD.Consignee))
UNION ALL SELECT Receipt_CLNT.Rcpt_Date, 0 as CmNo, 0 as BookDate, 0 as LR_No, 0 as CM_Total, Receipt_CLNT.Amt_Rcvd as RcptAmt,Receipt_CLNT.ChqDDNo as RefNo, Receipt_CLNT.ChqDDdate as RefDate, Receipt_CLNT.Amt_Mode as Mode, Receipt_CLNT.RcptNo as RcptNo, 0 as MemoNo, 0 as MDate, Receipt_CLNT.G_Name
from Receipt_CLNT
ORDER BY Cdate;

Open in new window

Query-and-Desire-Result.doc
Comment
Watch Question

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
It looks like you want to see descending date.  If that's the case, use

ORDER BY Cdate Desc;

Open in new window

Altaf PatniSoftware Developer

Author

Commented:
@aikimark:
No, Please check second table(Desired data), 3 rd Row in attached file.
and please check first table, (Query Data), 3 rd Row and 5 fth Row,  
it supposed to be single row, but query displayed multiple row with data from different different tables.

hope you understand.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
Ok.  Based what pattern I think I see, you want the smallest BiltyNo for a given BDate.

So, I think your second Select should look like this:

Select Distinct 0 as Rcpt_Date, 0 as CmNo,  BD.Bdate as BookDate, (Select min(Bilty_Detail.BiltyNo From Bilty_Detail Where Bilty_Detail.Consignee = BD.Consignee And Bilty_Detail.BDate = BD.BDate ) as LR_No, 0 as CM_Total, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,BD.CST as MemoNo, BD.GRDate as M_Date, BD.Consignee 
from (Bilty_Detail BD INNER JOIN Receipt_CLNT CM ON (CM.G_Name = BD.Consignee))

Open in new window

Altaf PatniSoftware Developer

Author

Commented:
@aikimark:
its not smallest, biggest, ascending, or descending problem,
we are not on the same page,
my problem is, one record related to different different tables should be in single row,

please check again above attached file.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
@crystal_Tech

In the original output, you have
0	0	16/08/10	288196
0	0	16/08/10	288195
0	0	16/08/10	288191

Open in new window


In the desired output, you are only showing the smallest value in the LR_No column.
0	0	16/08/10	288191

Open in new window

Note: I'm only showing the first four columns for brevity

The rows with the zero-zero-date-number pattern come from the second of your three unioned queries.  That is the query that I changed and posted above.

=====
Since you haven't yet described the rules for inclusion/exclusion, we can only base our suggestions on the data pattern you posted.  Maybe it is time for you to be specific in this respect, rather than asking us to check the attached (Word document) file -- I'm missing something that you think is obvious to the reader.

Commented:
Try the below, Please note that I'm just guessing on the relationship between your tables, please adjust accordingly.


select 
	CM.CM_Date as Cdate,
	CM.Cash_MemoNo as CmNo,
	t1.Bdate as BookDate,
	t1.BiltyNo as LR_No,
	CM.CM_Total as CMAmt,
	t1.Amt_Rcvd as RcptAmt,
	t1.ChqDDNo as RefNo, 
	t1.ChqDDdate as RefDate, 
	t1.Amt_Mode as Mode, 
	t1.RcptNo as RcptNo, 
	t1.CST as MemoNo, 
	t1.GRDate as M_Date, 
	t1.Consignee 
from CashMemo CM
left join ClientLedger on ClientLedger.CName = CM.CNee
left join (Select * from Bilty_Detail inner join Receipt_CLNT on G_Name = Consignee) t1 on CM.Cname = t1.G_Name
order by CDate;

Open in new window

Altaf PatniSoftware Developer

Author

Commented:
you are right i have to mentioned what i want and what is current result i am getting,
and thank you for helping me, :-)

Desired Result
 
Cdate	CmNo	BookDate LR_No	CMAmt	Amt_Rcvd ChqDDNo ChqDDdate Acc_Typ RcptNo MemoNo M_Date	   conN
18/03/11 100005	16/08/10 288195	1010	0	 0	 0         0	    0	   1	23/12/10   TAR

Open in new window


Current Result
 
Cdate	CmNo	BookDate LR_No	CMAmt	Amt_Rcvd ChqDDNo ChqDDdate Acc_Typ RcptNo MemoNo M_Date	   conN
0	0	16/08/10 288195	   0	0	 0	 0	   0	   0	  1	 23/12/10  TAR
18/03/11 100005	   0	      0	1010	0	 0	 0	   0	   0	  1	       0   TAR

Open in new window


Same record related to 4 tables should be in single row, but you can see its displaying result from single table to single row.

hope you understand,

Commented:
did you check my comment? http:#a35460029

Commented:
Another possibility is the below
select 
	max(CM_Date) as CDate,
	max(Cash_MemoNo) AS CmNo,
	max(BookDate) as BDate,
	max(LR_No) as LRNo,
	max(CM_Total) as CMAmt, 	
	max(Amt_Rcvd) as AmtRcvd, 
	max(ChqDDNo) as ChqDDNo, 
	max(ChqDDdate) as CHQDate, 
	max(Acc_Typ) as AccTyp, 
	max(RcptNo) as RecptNo,
	max(MemoNo) as MemNo, 
	max(M_Date) as MDate, 
	G_Name
from (
SELECT 
	CM.CM_Date, 
	CM.Cash_MemoNo,
	0 as BookDate, 
	0 as LR_No, 
	CM.CM_Total, 
	0 as Amt_Rcvd, 
	0 as ChqDDNo, 
	0 as ChqDDdate, 
	0 as Acc_Typ, 
	0 as RcptNo,
	0 as MemoNo, 
	0 as M_Date, 
	CM.CNee as conName
from (
	CashMemo as CM
INNER JOIN ClientLedger ON (ClientLedger.CName = CM.CNee))

UNION ALL 

Select 	0 as Rcpt_Date, 
	0 as CmNo, 
	BD.Bdate as BookDate, 
	BD.BiltyNo as LR_No, 
	0 as CM_Total, 
	0 as Amt_Rcvd, 
	0 as ChqDDNo, 
	0 as ChqDDdate, 
	0 as Acc_Typ, 
	0 as RcptNo,
	BD.CST as MemoNo, 
	BD.GRDate as M_Date, 
	BD.Consignee 

from (Bilty_Detail BD
INNER JOIN Receipt_CLNT CM ON (CM.G_Name = BD.Consignee))

UNION ALL 

SELECT 
	Receipt_CLNT.Rcpt_Date, 
	0 as CmNo, 
	0 as BookDate, 
	0 as LR_No, 
	0 as CM_Total, 
	Receipt_CLNT.Amt_Rcvd as RcptAmt,
	Receipt_CLNT.ChqDDNo as RefNo, 
	Receipt_CLNT.ChqDDdate as RefDate, 
	Receipt_CLNT.Amt_Mode as Mode, 
	Receipt_CLNT.RcptNo as RcptNo, 
	0 as MemoNo, 
	0 as MDate, 
	Receipt_CLNT.G_Name
from Receipt_CLNT
) a
group by G_Name
ORDER BY Cdate;

Open in new window

Altaf PatniSoftware Developer

Author

Commented:

@ralmada:
I didnt test your suggestion, cause problem is NOT max or minimum,
problem is i mention in post ID: 35460069,

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
@crystal_Tech

Please post pertinent data from your tables. (those tables and rows that could be combined to create your desired output)
Altaf PatniSoftware Developer

Author

Commented:
@ralmada:
there is no relation between all four tables, some fileds are same in some tables.

Commented:
>>I didnt test your suggestion, cause problem is NOT max or minimum<<
I'm using MAX to create unique records as you requested.

did you try http:#a35460143 then?
Altaf PatniSoftware Developer

Author

Commented:
@aikimark:
Please check attached file


@ralmada:
i tried your suggested query http:#a35460143 
but error displayed,
"Circular reference caused by alias 'ChqDDNo' in Query definition's SELECT list.


Detail-of-Tables.doc

Commented:
you need to change line 8 above with this

max(ChqDDNo) as ChqDDNo1,
Altaf PatniSoftware Developer

Author

Commented:
@ralmada:
It says "enter parameter value" ( Parameter Name " G_Name "
and "Cdate"
without put any value i clicked on ok and result is,  only one record where is the other records..?
 
CDate	CmNo	BDate	LRNo	CMAmt	AmtRcvd	ChqDDNo1	CHQDate	AccTyp	RecptNo	MemNo	MDate	Expr1
28-02-11 100005	20-12-10	295585	1010	300	N/A	20/03/2011	CASH	1	1	28-12-10

Open in new window


and this one record is also wrong
it supposed to be

Cdate	CmNo	BookDate	LR_No	CMAmt	Amt_Rcvd ChqDDNo ChqDDdate Acc_Typ RcptNo MemoNo M_Date	conN
28/02/11 100003	23/12/10	295793	828	0	  0	 0	    0	   0	  0	  0	SHA

Open in new window

Commented:
g_name should be conName


select 
	max(CM_Date) as CDate,
	max(Cash_MemoNo) AS CmNo,
	max(BookDate) as BDate,
	max(LR_No) as LRNo,
	max(CM_Total) as CMAmt, 	
	max(Amt_Rcvd) as AmtRcvd, 
	max(ChqDDNo) as ChqDDNo1, 
	max(ChqDDdate) as CHQDate, 
	max(Acc_Typ) as AccTyp, 
	max(RcptNo) as RecptNo,
	max(MemoNo) as MemNo, 
	max(M_Date) as MDate, 
	conName
from (
SELECT 
	CM.CM_Date, 
	CM.Cash_MemoNo,
	0 as BookDate, 
	0 as LR_No, 
	CM.CM_Total, 
	0 as Amt_Rcvd, 
	0 as ChqDDNo, 
	0 as ChqDDdate, 
	0 as Acc_Typ, 
	0 as RcptNo,
	0 as MemoNo, 
	0 as M_Date, 
	CM.CNee as conName
from (
	CashMemo as CM
INNER JOIN ClientLedger ON (ClientLedger.CName = CM.CNee))

UNION ALL 

Select 	0 as Rcpt_Date, 
	0 as CmNo, 
	BD.Bdate as BookDate, 
	BD.BiltyNo as LR_No, 
	0 as CM_Total, 
	0 as Amt_Rcvd, 
	0 as ChqDDNo, 
	0 as ChqDDdate, 
	0 as Acc_Typ, 
	0 as RcptNo,
	BD.CST as MemoNo, 
	BD.GRDate as M_Date, 
	BD.Consignee 

from (Bilty_Detail BD
INNER JOIN Receipt_CLNT CM ON (CM.G_Name = BD.Consignee))

UNION ALL 

SELECT 
	Receipt_CLNT.Rcpt_Date, 
	0 as CmNo, 
	0 as BookDate, 
	0 as LR_No, 
	0 as CM_Total, 
	Receipt_CLNT.Amt_Rcvd as RcptAmt,
	Receipt_CLNT.ChqDDNo as RefNo, 
	Receipt_CLNT.ChqDDdate as RefDate, 
	Receipt_CLNT.Amt_Mode as Mode, 
	Receipt_CLNT.RcptNo as RcptNo, 
	0 as MemoNo, 
	0 as MDate, 
	Receipt_CLNT.G_Name
from Receipt_CLNT
) a
group by conName
ORDER BY Cdate;

Open in new window

Altaf PatniSoftware Developer

Author

Commented:
all data  supposed to be like this
Cdate	CmNo	BookDate LR_No	CMAmt	Amt_Rcvd ChqDDNo ChqDDdate Acc_Typ RcptNo MemoNo M_Date	conN
25/11/10    0	0	  0	0	300	 NA	 20/03/11  CASH    1	  0	  0     TAR
28/02/11 100003	23/12/10 295793	828	0	  0	 0	    0	   0	  0	  0	SHA
18/03/11 100005	16/08/10 288195	1010	0	  0	 0	    0	   0	  1	23/12/10 TAR
19/04/11 100004	16/08/10 288196	550	0	  0	 0	    0	   0	  1	23/12/10 TAR
0	    0	16/08/10 288191	0	0	  0	 0	    0	   0	  1	23/12/10 TAR
0	    0	20/12/10 295585	0	0	  0	 0	    0	   0	  1	28/12/10 TAR

Open in new window

Altaf PatniSoftware Developer

Author

Commented:
now its asking me to put parameter name "Cdate"

Commented:
That doesn't mean anything to us. If the tables don't have a field that link them to each other, then you will never be able to get the desire output.

Just so you understand. to get this,

Cdate	CmNo	BookDate LR_No	CMAmt	Amt_Rcvd ChqDDNo ChqDDdate Acc_Typ RcptNo MemoNo M_Date	   conN
18/03/11 100005	16/08/10 288195	1010	0	 0	 0         0	    0	   1	23/12/10   TAR

Open in new window


SQL needs to know how row 1 and 2 are linked together. From the details below, there's no clear field in common, SQL cannot link them.

Cdate	CmNo	BookDate LR_No	CMAmt	Amt_Rcvd ChqDDNo ChqDDdate Acc_Typ RcptNo MemoNo M_Date	   conN
0	0	16/08/10 288195	   0	0	 0	 0	   0	   0	  1	 23/12/10  TAR
18/03/11 100005	   0	      0	1010	0	 0	 0	   0	   0	  1	       0   TAR

Open in new window


Commented:
>>now its asking me to put parameter name "Cdate" <<

that's probably because of line 72 should be

Order by CDate

or

Order by 1

Altaf PatniSoftware Developer

Author

Commented:

Please check BD data sample
may be you can get something to link with other table,

Note : data is the same, but column (field) name might be changed.
BiltyNo	Mode BDate	TruckNo	    Code	        Consignor	Consignee	        From_City	To	  Article	Description	Weight	Rate	ToPay	Paid	Pymt_mode	CR	HC	AOC	BC	Others	Total	Pvt_Mark	Declaration_val	Delivery_at	CST	Narration	Check	LsheetNo	GroupName	Ref_GM_No	pf	Tmp_Weight	CDate	      Service_Tex	User_Name	IDTNo	GRNo	GRDate	Chg_Weight	TotToPay	TotPaid	        G_Name	       Export_To	Quot_Type	I_Type	Art_Type	Rate_Type	CNorTinNo	CNeeTinNo	STaxPayBy	FOV	Door_Delv	Invo_No	Status_Rec	CnorPNo	CneePno
288195	N/A 16-08-10 WB/23-B/5073	2	ELMUN METAL PRODUCTS	TARIQ AND COMPANY	KOLKATA	        YAWATMAL	2	ALUMINIUM SEALS	25	300.00	600	0	TOPAY	        10	0	0	30	0	640	N/A	        5200	                JAIN SONS	1	N/A	        1	5	        VIDHARBA	5	        0	0	        07-07-1894	0	        TARIQ	         0		23-12-10	25	640	        0	JAIN SONS			                        CARTOON	         ARTICLE	19670226287	0	        TRANSPORTER	0	0	        36/10-11	3

Open in new window

Commented:
If the last query I've suggested doesn't produce the result you expect, then no, there's nothing that seems to link them together. So, you will have to work on your data to make the connections first. That means you will have to do it manually. Then you can create a query joining all tables.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
@crystal_Tech

Data, please.  Not screen shots.  CSV or Excel format would be nice.
Altaf PatniSoftware Developer

Author

Commented:
@aikimark:
Please check attached files
 
'''Bilty_Detail

Open in new window

Bilty-Detail.xls

 
'''CashMemo

Open in new window

CashMemo.xls
 
''''Receipt_CLNT

Open in new window

Receipt-CLNT.xls

 
'''ClientLedger

Open in new window

ClientLedger.xls
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Altaf PatniSoftware Developer

Author

Commented:

Please check
There is one column data is same in all tables

CName (ClientLedger)
G_Name (Receipt_CLNT)
CNee (CashMemo)
Consignee (Bilty_Detail)



aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
Not only aren't they the same column names, but the data you posted wouldn't give us experts a clue.  Neither Shanti nor Tariq are in the Bilty_Detail Consignee column.

Commented:
Are my comments not clear enough? There is no relationship between tables so you cannot join them. You need to revise your table design by adding the required columns to link them. I'm sorry but that's the answer to your question.

Commented:
I would suggest you read the following tutorial on proper database designing

http://www.phlonx.com/resources/nf3/
Altaf PatniSoftware Developer

Author

Commented:
@aikimark: >> but the data you posted wouldn't give us experts a clue.  Neither Shanti nor Tariq are in the Bilty_Detail Consignee column.
Sorry for that i forgot to add those name in data i posted,
Column name is different but data will be same,
let me describe a little working scenario
first entry is in Bilty_Detail
if Bilty_Detail have record and this record (Consignee) is equal to ClientLedger.CName then only CashMemo will add this record else nothing happen.
and if CashMemo have records it means Client have to pay certain amount. and if client paid then Receipt_CLNT have Record, ( Note : only Receipt_CLNT Records will display individual.)
hope you understand this scenario,
--------------------------------------------------------------  
CName (ClientLedger)
G_Name (Receipt_CLNT)
CNee (CashMemo)
Consignee (Bilty_Detail)


@ralmada: >>>>Are my comments not clear enough? There is no relationship between tables so you cannot join them. You need to revise your table design by adding the required columns to link them. I'm sorry but that's the answer to your question.

Crystal Clear , already i know that there is no relationship between tables, thats why i posted my Problem to you guys, (only i know that Nothing is impossible for you guys, it's my own experience, i learned so many things and still i am learning from you guys (Experts-Exchange)

i cant add any new column in Bilty_Detail , because this same data structure is everywhere ( Client side and Sub Client side ) Clients are using different different front end to insert data into bilty_detail table,
and this is the requirement from server side,

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
What kind of database is this?  You have "Access" in the tag, but a MySQL zone.
Altaf PatniSoftware Developer

Author

Commented:

yeh i am using MS Access 2003
Altaf PatniSoftware Developer

Author

Commented:
Thanks

Commented:
I'm sorry the answer is just No. There's nothing we can't do about it.

Commented:
Sorry, the above should read: There's nothing we can do about it.

If you can't add a new column in Bilty_Detail, there's no much that can be done.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
From my analysis, although you can join the four tables with the fields we've discussed, I don't think that is sufficient for SQL statements to be able to produce the output you desire.  It is the same conclusion that ralmada has reached.

Both of us could be wrong and some other expert might be able to provide a solution/query.  To help them, please create another set of data that could be used to create the query results you posted in your question text.  You can use separate worksheets in a single workbook.  There is no need to upload four different workbooks.
Altaf PatniSoftware Developer

Author

Commented:
thanks for your help and advice,
just want to know that
what field in Bilty_Detail need to add and what data in this field will be stored?

i am trying to send them (clients and sub clients) same table with new structure...(using automation process from Server)
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
You need to start by describing the meta data.  
* What do these tables represent?  
* How are the data in these tables related to one another?
* What are the occurrence relationships between these tables? (1-to-1, 1-to-many, 0-to-many, many-to-many)
Altaf PatniSoftware Developer

Author

Commented:
@ ralmada:
>>>>If you can't add a new column in Bilty_Detail, there's no much that can be done.
Name of the field, ?

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
@crystal_Tech

I think he was asking what fields he needs to add to facilitate a solution.

Commented:
>>@ ralmada:
>>>>If you can't add a new column in Bilty_Detail, there's no much that can be done.
Name of the field, ?<<

The name is irrelevant, you can give the field any name you want. What I was trying to say is that you need to add a field that will be used to link with the other tables. you will need to decide to which of the other tables this field will be linked.

So here's an alternative of what I think you need to do:

1) Add a unique ID column in ClientLedger that will identify the client. it could be an autoincrement so you will end up with something like

ID  CName
1   PATNI BROTHERS
2   SHANTI TRADERS
3  TARIQ AND COMPANY

2) You will need to add the ClientID column in the other tables so you can link them by client.

3) add a column in CashMemo with the BiltyNo that comes from Bilty_Detail. In that way you can join then by BiltyNo.

4) Then Receipt-CLNT can be either linked with CashMemo by adding Cash_MemoNo in Receipt-CLNT. The field needs to be populated with data from CashMemo table.


And again please check this link for reference:

http://www.phlonx.com/resources/nf3/ 
Altaf PatniSoftware Developer

Author

Commented:

@ralmada:
4) Then Receipt-CLNT can be either linked with CashMemo by adding Cash_MemoNo in Receipt-CLNT. The field needs to be populated with data from CashMemo table.

Three condition can be done, except fourth, because client can deposit more than cashmemo or less than cashmemo amount,

like

Receipt_CLNT
 
RcptNo	G_Name	            Rcpt_Date	Amt_Rcvd  Amt_Mode  ChqDDNo  ChqDDdate	 Narration  UserName
1	TARIQ AND COMPANY   25/11/2010	300	  CHEQUE    22222    20/03/2011	 N/A	    TARIQ

Open in new window


CashMemo
 
Cash_MemoNo  CM_Date	LRNo	Acc_Typ	Freight	OctSC	CM_Total  CCopy_Rcvd  Contact_No  CNee
100003	   28/02/2011	295793	CASH	693	25	828	  Y	      0	          SHANTI TRADERS
100004	   19/04/2011	288196	CREDIT	500	0	550	  N	      0	          TARIQ AND COMPANY
100005	   18/03/2011	288195	CASH	1000	0	1010	  N	      0	          TARIQ AND COMPANY

Open in new window


But i can add Client ID in both tables Receipt_CLNT and CashMemo
will it work..?

Commented:
You still need to link them in another way. The problem is that I'm not clear what data you have in CashMemo and Receipt_Clnt. I mean, I see the columns there, but what is the purpose of each tables. I can guess that you can have more than one cashmemo for the same receipt. If so, then you need to add RcptNo into CashMemo, something like this probably
RcptNo Cash_MemoNo  CM_Date	LRNo	Acc_Typ	Freight	OctSC	CM_Total  CCopy_Rcvd  Contact_No  CNee
1      100003	   28/02/2011	295793	CASH	693	25	828	  Y	      0	          SHANTI TRADERS
1      100004	   19/04/2011	288196	CREDIT	500	0	550	  N	      0	          TARIQ AND COMPANY
1      100005	   18/03/2011	288195	CASH	1000	0	1010	  N	      0	          TARIQ AND COMPANY

Open in new window

Altaf PatniSoftware Developer

Author

Commented:

>>>>I can guess that you can have more than one cashmemo for the same receipt.
it will not work because in cashmemo there is
CNee = TARIQ AND COMPANY and cash_memoNo = 100004 and CM_Total = 550,
now against this record client is paying only 300 instead full payment (550),
RcptNo =      1 and G_Name       = TARIQ AND COMPANY and  Rcpt_Date = 25/11/2010 and  Amt_Rcvd = 300

if TARIQ AND COMPANY paying rest of the amount, then only Receipt_CLNT will create not cash memo then i cant add receipt no, in cash memo

hope you understand thi

Commented:
Ok so then it seems It's the other way around. Meaning you have more than one receipt per cash memo, Right? If so what you need to do is add the cash memo   number in the receipt table.
Altaf PatniSoftware Developer

Author

Commented:
if 5 cashmemo then client can pay 15 times means 15 receipt_CLNT
and client can pay one time also means 1 receipt_CLNT.

Sir how can i manage this..?   :-(

Commented:
>>if 5 cashmemo then client can pay 15 times means 15 receipt_CLNT
and client can pay one time also means 1 receipt_CLNT.

Sir how can i manage this..?   :-(
<<

The same way. add the CashMemo Number in the Receipt_CLNT table.
Altaf PatniSoftware Developer

Author

Commented:
Ok
now Receipt_CLNT data want to individual, dont want to add in single row
still i need to add cashmemo number in the Receipt_CLNT

Commented:
yes
Altaf PatniSoftware Developer

Author

Commented:
ok
right now i am not working on this task, but in future i need your help,.

Thanks : -) aikimark
Thanks :-) ralmada

you both deserve more than 500 points,  but unfortunately here is only 500

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.