2

In Access have a simple table layout:

enter image description here

And I want to add data to t_Bild. So I have created the query: 

INSERT INTO t_Bild ( B_ID, M_ID, Dateiname, Datum )
VALUES (11, 8, "someName.png", DATE());

It works. But I need to get the max value for B_ID dynamically so the query inserts a new private key automatically. So I have modified the query:

INSERT INTO t_Bild ( B_ID, M_ID, Dateiname, Datum )
VALUES ((SELECT 1+ MAX(B_ID) FROM t_Bild), 8, "someName.png", DATE());

But the problem is that each time I run the query no data is inserted. I also do not get any error message back. I have checkt the query: 

SELECT 1+ MAX(B_ID) FROM t_Bild;

And it works fine.

How can I achieve to add data to t_Bild with a valid private key?

Note: I am not able to set the private key to autonumber because I forgot to set it while modelling the database. Now Access does not allow me to set the private keys to autonumber anymore.

TT.
  • 15,774
  • 6
  • 47
  • 88
jublikon
  • 3,427
  • 10
  • 44
  • 82

1 Answers1

4

You can use the insert into...Select variant:

INSERT INTO t_Bild ( B_ID, M_ID, Dateiname, Datum )
   SELECT 1+ MAX(B_ID), 8, "someName.png", DATE()
   FROM t_Bild GROUP BY 2,3,4
Haytem BrB
  • 1,528
  • 3
  • 16
  • 23
  • Works fine! But why GROUP BY 2,3,4 ? – jublikon Jan 20 '16 at 16:28
  • 1
    You cannot calculate an aggregate value like "Max" without a "Group by", and the 2,3,4 are another way to write: group by M_ID, Dateiname, Datum. You can refer to this question for more: http://stackoverflow.com/questions/7392730/what-does-sql-clause-group-by-1-mean – Haytem BrB Jan 20 '16 at 16:31