MAXIMUM DATE,NULL

[Guest]

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.

 

[Chris M]

Maybe you could use the 'nz' function to convert null values into a date?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!!

 

[Guest]

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

 

[Guest]

hi,

did u resolved ur problem...i query i given will work fine...

pls let me know if that helps u

Thanks

Sunil.T