tricky question

[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

 

[Beetle]

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.

 

[Mark Andrews]

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

 

[BruceM]

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.

 

[Mark Andrews]

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