Query to show last balance of each customer (1 Viewer)

[zibzaba]

HI
PLZ HELP ME.
I WANT A QUERY BASED ON QUERY , " Customer_Orders Query " IN WHICH I GET YHE LAST BALANCE OF EACH CUSTOMER , ONLY ONCE .I.E BALANCE SHEET OF ALL CUSTOMERS WITH EACH CUSTOMER NAME , WITH THE RESPECTIVE DATE OF EACH CUSTOMER. I HAVE TRIED BUT ALL IN VAIN.
PLZ HELP .....................

 

[btamsgn]

Pls send specific report sample to help.

 

[jesse]

First of all, your database needs some improvements. For example: don't use a name as an id. Try to read a good book about database when you have the time. IN the meantime, the query below will do what you want.

SELECT * FROM Customer_Orders WHERE customer_order_date=(SELECT MAX(customer_order_date) FROM customer_orders AS P1 WHERE P1.customer_id=Customer_Orders.customer_id);

 

[zibzaba]

thanx jesse . u solved my problem.

 

[zibzaba]

thanx jesse. u solved my problem

 

[zibzaba]

alphabetical list of customers

hi
but how can i get alphabetical list of customers with last balance and no duplicate entry,
thanx

 

[zibzaba]

plz help ! Query to show last balance of each customer

plz help me. how can i get alphabetical list of customers with last balance and no duplicate entry, in the mentioned db. thanx

 

[jdraw]

Based on Jesse's answer

SELECT * FROM Customer_Orders WHERE customer_order_date=(SELECT MAX(customer_order_date) FROM customer_orders AS P1 WHERE P1.customer_id=Customer_Orders.customer_id);
Reply With Quote

Adjust as follows

SELECT * FROM Customer_Orders  
WHERE customer_order_date=(SELECT MAX(customer_order_date) 
FROM customer_orders AS P1 WHERE P1.customer_id=Customer_Orders.customer_id)
[COLOR="Red"]Order By Customer_Orders.Customer_Name;[/COLOR]

 

[zibzaba]

but its not working jdraw

 

[jdraw]

but its not working jdraw

What part isn't working?
I just set up a database with Table and query and it works for me.
See attached jpg

 

[zibzaba]

i get the balance but the list is not alphabatical, and is with duplicate entries, by putting jdraw code.
plz help me. how can i get alphabetical list of customers with last balance and no duplicate entry, in the mentioned db. thanx
can anyone modify it for me......

 

[zibzaba]

plz help me. how can i get alphabetical list of customers with last balance and no duplicate entry, in the mentioned db. I have a query named Customer_Bal_Sheet. total number of customers is 86. but the balance sheet report gives 94 entries. i.e many entries are duplicate. actually, if a customer has 2 or more orders on a same date, then his all orders of that ddate appear in the query Customer_Bal_Sheet.
secondly ,the names are not in alphabatical order.

please help me . i am in big trouble .
thanks

 

[zibzaba]

btamsgn are u there.....
or can anyone help me..
its urgent,,,,,,,,,,

 

[DCrake]

You do not appear to have any grouping or summing in your queries you also need to incude a group by customer in your report as well.

 

[zibzaba]

Thanks David. but can u modify my given db for me. I am in a very difficult sitaution, and I tried but in vain........