 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005MAXIMUM DATE,NULL
I have read previous posts, but cannot come up with a solution that works. I have a database which tracks invoices per client. Based on this, I am trying to create a query which returns the "plan name" with latest date the client was invoiced, or, if the client has never been invoiced, a blank field with the plan name. I have two tables, Invoices (primary key Invoice ID) and Plan Table (primary key Plan ID). I've tried the Max expression, grouping by Max, and have looked at the DMAX function, but I can't figure out how to use it. If I use the max expression, I don't know how to include null values. Any help is greatly appreciated. Maybe you could use the 'nz' function to convert null values into a date? >I have read previous posts, but cannot come up with a solution that works. >I [quoted text clipped - 13 lines] > > Any help is greatly appreciated. hi , i will give u a small eg.. TBL_CUST ------------ ID TEXT NAME TEXT TBL_CUST_LOAN -------------------- ID TEXT AMT TEXT DT DATE/TIME suppose the data in tbl_cust is: ID NAME 1 sunil 2 sujith 3 viswa 4 pradeep 5 param 7 kjsfjksadfasfj and in tbl_cust_loan is ID AMT dt 1 100 1/1/2000 2 200 1/1/2001 1 100 1/1/2000 3 400 12/31/2005 3 200 12/31/2004 4 1000 12/12/2006 if u fire the below query , SELECT TBL_CUST.id, TBL_CUST.name, max(TBL_CUST_LOAN.dt) FROM TBL_CUST LEFT JOIN TBL_CUST_LOAN ON SCOTT_CUST.ID = TBL_CUST_LOAN.ID GROUP BY TBL_CUST.id, TBL_CUST.name; IT WILL GIVE id name Expr1002 1 sunil 1/1/2000 2 sujith 1/1/2001 3 viswa 12/31/2005 4 pradeep 12/12/2006 5 param 7 kjsfjksadfasfj I think this is ur need ...so u can implement this according to ur need Thanks With Regards Sunil.T hi , i will give u a small eg.. TBL_CUST ------------ ID TEXT NAME TEXT TBL_CUST_LOAN -------------------- ID TEXT AMT TEXT DT DATE/TIME suppose the data in tbl_cust is: ID NAME 1 sunil 2 sujith 3 viswa 4 pradeep 5 param 7 kjsfjksadfasfj and in tbl_cust_loan is ID AMT dt 1 100 1/1/2000 2 200 1/1/2001 1 100 1/1/2000 3 400 12/31/2005 3 200 12/31/2004 4 1000 12/12/2006 if u fire the below query , SELECT TBL_CUST.id, TBL_CUST.name, max(TBL_CUST_LOAN.dt) FROM TBL_CUST LEFT JOIN TBL_CUST_LOAN ON SCOTT_CUST.ID = TBL_CUST_LOAN.ID GROUP BY TBL_CUST.id, TBL_CUST.name; IT WILL GIVE id name Expr1002 1 sunil 1/1/2000 2 sujith 1/1/2001 3 viswa 12/31/2005 4 pradeep 12/12/2006 5 param 7 kjsfjksadfasfj I think this is ur need ...so u can implement this according to ur need Thanks With Regards Sunil.T Thank you for your help. When I type this, replacing my table names and fields, I get the error "join expression not supported", and it's pointing to "left join tbl_cust_loan on scott_cust.id = tbl_cust_loan.id" I'm not sure what I'm doing wrong, and, what is "scott"? Thanks!! > hi , > i will give u a small eg.. [quoted text clipped - 53 lines] > > Sunil.T hi, Scott is a Orcle default user name ..actually i imported those 2 tables from oracle ..so its prefixed with scott..thats all... first u have to remove that Scott from the table name...u have to rename my table names with ur table names...then it will work fine..... if not...goto the QUERY DESIGN, one relationship diagram will be there...select the link between two tables , double click it and select the second option and re run the query... Thanks Sunil.T > Thank you for your help. When I type this, replacing my table names and > fields, I get the error "join expression not supported", and it's pointing to [quoted text clipped - 60 lines] > > > > Sunil.T hi, did u resolved ur problem...i query i given will work fine... pls let me know if that helps u Thanks Sunil.T > hi, > [quoted text clipped - 75 lines] > > > > > > Sunil.T |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.