Help on Query

[rhughr]

Hello,

I am trying to convert some T-SQL statements into an Access database
driven applicataion. I am trying to update a 1 row/1 column table with
the max value from another table. Here is the query:

UPDATE tblHIGH SET HIGHEST_NUM = (select max(CTR) from tblBOOK)

Access give me an error message (Operation must use an updateable
query).

Any thoughts on what I am doing wrong? Again, this syntax works on
Sybase.

Thanks.

 

[Tom Ellison]

Dear Hugh(?):

Access will run T-SQL as pass through. I recommend you consider this
alternative. There are numerous limitations to Access Jet (which is almost
certainly what you're trying to employ here) that you'd probably rather not
learn and with which you don't really need to fight.

Either use ODBC with an MDB or switch to ADP and use MSDE. Don't know what
these acronyms mean. Just ask!

Tom Ellison

 

[Pat Hartman\(MVP\)]

Access does not allow a query to update a table if the query contains any
aggregation. This shouldn't be a problem in a properly normalized database
because aggregate data should not be stored.

To do what you are asking, you can run a make table query to save the Max()
value. Then run an update query that uses that temp table rather than the
subselect.

 

[rhughr]

First off, thanks for the responses. I really appreciate it. When it
comes to MS and Access, I am all thumbs.

You mention the make table query, that is where I was going with this
originally but I could not get it to work. I will ask one question
about this query, but if you dont feel like going into depth please
feel free to point me to a good online reference. I will gladly do the
research.

I believe my original problem was that the make-table query dropped and
recreated the table each time. For this application, it will be run
monthy so I need a running count. The underlying linked table does not
have a Sequence, Counter, or even Timestamp column which I can use to
identify 'New' records. I might be making this too complicated, but
without being able to have a continuously incrementing AutoNum column I
was trying to create the little side-table that would only hold the max
number from the 'Staging' table that i was populating via a 3-step
macro that would delete *, then insert into, then update holder table
with max counter value.

Sooooo, if you know a way to do this in 1 step that would be great.
Otherwise please point me to some handy-dantdy web site and I will poke
around.

Regards,
Hugh


p.s. doI use a # to designate a temp table in Access?

 

[Pat Hartman\(MVP\)]

You don't actually need to save the calculated amount since it can be
calculated whenever you need it. Just add parameters to the query to prompt
for month end date or a date range if you might want to include multiple
months. Or, you can just use a totals query so you can show totals for all
months.
Select Format(YourDate, "yyyy/mm") as YearMonth, Sum(YourAmount) as
SumAmount
From YourTable
Group by Format(YourDate, "yyyy/mm");