?
Solved

UNION ALL WITH WHERE CLAUSE - 1

Posted on 2011-04-25
53
Medium Priority
?
443 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
0
Comment
Question by:crystal_Tech
  • 22
  • 19
  • 11
52 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 35459386
It looks like you want to see descending date.  If that's the case, use

ORDER BY Cdate Desc;

Open in new window

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35459416
@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.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35459584
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

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Author Comment

by:crystal_Tech
ID: 35459696
@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.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35459795
@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.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35460029
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

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35460069
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,
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35460099
did you check my comment? http:#a35460029
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35460143
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

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35460179

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

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35460241
@crystal_Tech

Please post pertinent data from your tables. (those tables and rows that could be combined to create your desired output)
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35460250
@ralmada:
there is no relation between all four tables, some fileds are same in some tables.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35460332
>>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?
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35460463
@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
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35460511
you need to change line 8 above with this

max(ChqDDNo) as ChqDDNo1,
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35460968
@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

0
 
LVL 41

Expert Comment

by:ralmada
ID: 35461003
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

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35461061
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

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35461080
now its asking me to put parameter name "Cdate"
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35461153
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


0
 
LVL 41

Expert Comment

by:ralmada
ID: 35461176
>>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

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35461341

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

0
 
LVL 41

Expert Comment

by:ralmada
ID: 35461363
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.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35461395
@crystal_Tech

Data, please.  Not screen shots.  CSV or Excel format would be nice.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35495184
@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
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 1000 total points
ID: 35495597
Like I've said before, there's nothing you can do with that data. There's no relationship between the tables, so you need to create them by adding key columns. No query can do that for you. you will have to do it manually.


0
 
LVL 46

Accepted Solution

by:
aikimark earned 1000 total points
ID: 35495752
While you can join three of the tables on this criteria:

CashMemo.CNee = ClientLedger.CName

ClientLedger.CName  = Receipt-CLNT.G_Name

Open in new window


I don't see any column data in the Bilty-Details table that matches any of the other three table's data.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35496642

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

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



0
 
LVL 46

Expert Comment

by:aikimark
ID: 35496813
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.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35496918
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.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35496938
I would suggest you read the following tutorial on proper database designing

http://www.phlonx.com/resources/nf3/
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35497962
@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,

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35497973
What kind of database is this?  You have "Access" in the tag, but a MySQL zone.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35497978

yeh i am using MS Access 2003
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35498204
Thanks
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35498565
I'm sorry the answer is just No. There's nothing we can't do about it.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35498600
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.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35498710
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.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35724339
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)
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35724474
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)
0
 
LVL 1

Author Comment

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

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35741039
@crystal_Tech

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

Expert Comment

by:ralmada
ID: 35742689
>>@ 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/ 
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35744095

@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..?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35746074
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

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35749975

>>>>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
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35751025
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.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35753283
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..?   :-(
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35754502
>>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.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35776211
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
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35780591
yes
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 35899987
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

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

830 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