Member Avatar for awmantonio

I have program in VB.NET 2008 where I want to update the premamt cell value in the tblPrem Access table, where the premno is the maximum (autogenerated as 1,2,3...), but only within a specified set of common numbers in a column (contno) I am currently saving. This contno is from another table (tblMain) linked in this table (tblPrem) where it is the primary key, and just repeatedly generated after the clicking save button. Here is my current code which saves the data but does not do anything on the textbox1 + textbox2, and has an error message "At most one record can be returned by this subquery":

sqlQrySave = "UPDATE tblPrem SET premamt = " & textbox1 & " + " & textbox2 & " WHERE premno=(SELECT MAX(premno) FROM tblPrem GROUP BY contno);"

What I expect to happen is like this:

contno | premno | premamt
1................1.............45
1................2.............45
1................3.............45
1................4.............52 <----- textbox1 + textbox2
2................1.............12
2................2.............12
2................3.............12
2................4.............12
2................5.............18 <----- textbox1 + textbox2
3................1.............33
3................2.............33
3................3.............33
3................4.............33
3................5.............40 <----- textbox1 + textbox2


Anyone who can help me fix my code? Any help will be greatly appreaciated. Thanks!

  • 1 Contributor
  • 1 Reply
  • 124 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by awmantonio

Dani AI

Generated

briefly closed this thread saying the problem was solved. For future readers: the Access error about “at most one record” happens because the subquery returns multiple rows when you GROUP BY contno without tying each result back to the outer row. The fix is to either (a) use a correlated subquery so the MAX is computed per outer row, or (b) join the table to an aggregated subquery that lists each contno and its MAX(premno). Also avoid building SQL by concatenating textbox strings; use parameterized commands and convert text to numeric types first.

A simple, reliable Access pattern (correlated subquery) — update the premamt for the max premno per contno:

UPDATE tblPrem AS p
SET p.premamt = ?
WHERE p.premno = (
  SELECT MAX(p2.premno) FROM tblPrem AS p2 WHERE p2.contno = p.contno
);

Example VB.NET usage with OleDb parameters: parse the two textbox values into decimals, add them, then pass the result as the single parameter to the command.

If you prefer a set-based join, aggregate first then update:

UPDATE tblPrem
INNER JOIN (
  SELECT contno, MAX(premno) AS MaxPremno FROM tblPrem GROUP BY contno
) AS q
ON tblPrem.contno = q.contno AND tblPrem.premno = q.MaxPremno
SET tblPrem.premamt = ?;

For SQL Server you can use a CTE with ROW_NUMBER() to target the highest premno per contno:

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY contno ORDER BY premno DESC) AS rn
  FROM dbo.tblPrem
)
UPDATE cte SET premamt = @value WHERE rn = 1;

Tips: run the SELECT version of your query first to preview affected rows; validate/constrain numeric input (Decimal.TryParse); use transactions and backups before mass updates; and confirm whether premno is truly unique per contno (if not, use an autonumber primary key or composite key). Microsoft reference for Access UPDATE syntax is here: Update statement (Microsoft Access SQL).

Member Avatar for awmantonio

I already got the right code. I now close this thread. Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.