how would I produce this type of form

[Mark Andrews]

Lets assume I have a simple database with two tables:
- tblClients
- tblSales

where each client can have multiple sales.

I would like to create a form to edit clients and sort the records
descending by total sales. Only client information will be edited.

How would I approach that?

Thanks,
Mark

 

[Dale Fye]

You should be able to create a query for your form that looks something like:

SELECT tblCients.*,
DSUM("Sales", "tblSales", "ClientID = " & tblClients.ClientID) as TotSales
FROM tblClients
ORDER BY DSUM("Sales", "tblSales", "ClientID = " & tblClients.ClientID) DESC

This should be updateable (although the TotSales) figure obviously won't be.

Now use this query as the basis for your client form.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Mark Andrews]

Thanks! Any idea how much slower that would run compared to
something like the normal query:

SELECT tblClients.*, Sum(tblSales.Sales)
FROM ...
ORDER BY Sum(tblSales.Sales) DESC

I might have many thousands of records in the long run.

Thanks again,
Mark

 

[Larry Linson]

Dale... the "rating" option is not available unless someone is using
particular software or responding through some third-party site. It looks a
little silly, because you are asking for an option not available to
many/most users of this site, and do not even explain to them how/where to
post the rating.

The vast majority of responders here do so just because they want to be
helpful, not because they are trying to be "rated". Those of us who are
regulars here, and have been for many years, do appreciate anyone who
answers questions, but just wanted to offer my personal opinion on "rating
requests".

Larry Linson
Microsoft Office Access MVP

 

[Larry Linson]

With what I would think are reasonable table and field names, this won't
work unless you include a subquery. On the other hand, with what I think
are reasonable table and field names to match, Dale's doesn't work for me,
either.

It's trivially easy to do with two queries, a totals query that calculates
the SumOfSales grouped by ClientID, and a query with the tblClients joined
to that totals query. Both Queries were built in the Query Builder and their
SQL is:

The final query:

SELECT tblClients.ClientID, tblClients.ClientName,
qrySalesByClient.SumOfSales
FROM tblClients LEFT JOIN qrySalesByClient ON tblClients.ClientID =
qrySalesByClient.ClientID
ORDER BY qrySalesByClient.SumOfSales DESC;

qrySalesByClient:

SELECT tblSales.ClientID, Sum(tblSales.Sales) AS SumOfSales
FROM tblSales
GROUP BY tblSales.ClientID;



And, because predicting timing is not all that accurate, I would hesitate to
waste time creating just one set of SQL with a subquery until I decided that
the query was running long enough that such an approach might pay back the
time and effort.

If "many" is less than "several tens of" in your prediction, you may just
never be able to improve on the simplest approach.

Larry Linson
Microsoft Office Access MVP

 

[Dale Fye]

Larry,

Not sure why my recommendation doesn't work for you. The OP specifically
indicated that he wanted to create a form to edit his client information,
but wanted to sort the clients by total sales, in descending order.
Unfortunately, you cannot achieve that functionality (updateable recordset)
with your solution.

I concur that this is probably not the cleanest SQL, but it fulfills the
OP's request, and should not be too significantly slower.

Dale