Allow more than order in an ORDER form.

[orange]

What exactly am I looking for? Query names 3A 3B are not very communicative.


I used query 3E and Table customers_Info to build new query.

Good luck

[kiko]

Yes. I already did the exact same thing that you did. But it gives me wrong information. If you run the one without firstname and lastname, it shows another customer spent rm300plus. If i run the new one with firstname and lastname, it shows another customer. It should show the same results if the query is correct right? Do u get my point here? I'm really sorry for troubling w my poor english.

[orange]

If you design the query to determine which CustId spent most, then adding a table to get the FName and LName won't change the query.

If your original query is not designed, or doesn't give consistent results, then that is the issue.

Take a calculator if you have to and sort out which CustId spend most. And SpendMost will involve
#of Books * agreed to SalePrice

10 Books @ 700 PriceUnits = 7000 spendunits

So get that first query on which CustId spend most set up , tested and verified before moving on.

[kiko]

Yes orange. I already did exactly as what you desribed. I even tried using your sample database that you made based on my work. It gives different values as well.

[orange]

Just used your data to build new query MyCustIdMostSpend

SELECT TOP 1 Customers_Info.CustomerID, Sum([Price]*[quantity]) AS MySpend
FROM (Customers_Info INNER JOIN Sales_Invoice ON Customers_Info.CustomerID = Sales_Invoice.CustomerID) INNER JOIN Sales_Details ON Sales_Invoice.Sales_Ref = Sales_Details.Sales_Ref
GROUP BY Customers_Info.CustomerID
ORDER BY Sum([Price]*[quantity]) DESC;

This returns CustId 106 MySpend 362.5

I used this query and the Customers_Info table

and still get CustId 106 MySpend 362.5 as per attached jpgs

Not sure where the issue is....

Are you doing some additional calculations??

[kiko]

Wow really?? Because I tried it, it gives me different results! I shall try again.

[kiko]

I gave it another tried and it worked this time. I think i must have been so confused and blurred. Really sorry for troubling you! But thanks a lot for your help orange!!

One last question. How do i marked this thread as solved?

[orange]

At top of thread , under thread tools is a drop down.
I checked solved.
Good luck.