tricky question

  • Thread starter Thread starter Mark Andrews
  • Start date Start date
M

Mark Andrews

I have a list box with three columns (AccountID, AccountName, OppDollars)
populated by this query:

SELECT tblAccount.AccountID, tblAccount.AccountName,
Sum(tblOpportunity.OppDollars) AS OppDollars
FROM tblAccount INNER JOIN tblOpportunity ON tblAccount.AccountID =
tblOpportunity.AccountID
GROUP BY tblAccount.AccountID, tblAccount.AccountName
HAVING (((Sum(tblOpportunity.OppDollars))>50000))
ORDER BY Sum(tblOpportunity.OppDollars) DESC;

I would now like to do the following:
- update the underlying tblAccount table and change a column named
"Sequence" to be the row number of the record in the list box.

So in summary:
- tblAccount has 10,000 records,
- the query above returns 450 records with accounts with over $50,000 of
opportunity dollars (sorted desc by oppdollars)
- I would like those 450 records to be updated in tblAccount with numbers 1
thru 450 (or 0 thru 449 would be fine as well)

Options I know:
- I could run through the records in the list box and run an update query
for each one (that seems like it might take a while).

I tried using DCOUNT in the above query to generate a sequence number but
can't get it to work? If I had that I could join this query to the table
and run an update query.

Thanks in advance for any help,
Mark
 
It sounds like you want your table to sort records in a particular order,
which is a waste of time and effort. Tables are for storing data. If you
want the data sorted you use a query (which you are doing already).
Let go of the notion that your records need to appear in the table in
some given order.
 
Sean,

It would be nice if you would either answer the question asked or ignore
this post. In some cases having a field in a table to control sorting is
needed (for other queries to use) because you can't join various queries and
still have an updatable recordset.

Mark
 
Sean did answer the question. There most likely is no good way to
accomplish what you want. If having the table sorted in a particular order
is essential then your design may be unworkable. There are ways such as a
ranking query to accomplish the row numbering you want. This link has more
information:
http://allenbrowne.com/ranking.html
One of the options discussed in the article is writing the results to a
temporary table. That may allow you the flexibility you need.
 
Thanks for the info. I ended up writing records to a temp table and joining
that to the query. I am just about to add another post
to see if anyone has better ideas to approach my issue (perhaps I didn't
explain my issue well enough).

Didn't mean offend anyone, I'll take all the help I can get.

Mark
 
Back
Top