BomberMan2K
Programmer
Hi,
I've got a problem with SQL in Access (though this is not an access issue) and I'd be glad to get some help.
I have 2 tables. the first is Table1, the second Table2.
Table2 has the Table1.id and a "place" counter for each item (for placing items in a menu and allowing to move it).
Now, when I add a new item to Table2, I check the maximum value for Place for the Table1.id I need to insert to, add + 1 to it and add the new item with the new "place" field.
The problem is when I insert new entry in Table1. When I run the "Max(place)" query in Table2 - I don't see anything for this entry (because it has no items for the new Table1.id I've just added) so when I need to add a new item I use this script:
qryMaxTable2:
"SELECT Max(Table2.place) AS Maxplace, Table2.table1id
FROM Table2
GROUP BY Table2.table1id;"
Insert Statement (used in ASP.NET Form):
"INSERT INTO Table2(name, table1id, place) SELECT @name, @table1id, maxplace+1 FROM qryMaxTable2 WHERE table1id=@table1id"
The problem is that the new table1id doesn't return any items and thus "maxplace" is empty and the code doesn't work (ASP.NET) - So I can't use the newly added entries to Table1 in Table2.
Would appreciate help in solving this.
Thanks,
Roman
I've got a problem with SQL in Access (though this is not an access issue) and I'd be glad to get some help.
I have 2 tables. the first is Table1, the second Table2.
Table2 has the Table1.id and a "place" counter for each item (for placing items in a menu and allowing to move it).
Now, when I add a new item to Table2, I check the maximum value for Place for the Table1.id I need to insert to, add + 1 to it and add the new item with the new "place" field.
The problem is when I insert new entry in Table1. When I run the "Max(place)" query in Table2 - I don't see anything for this entry (because it has no items for the new Table1.id I've just added) so when I need to add a new item I use this script:
qryMaxTable2:
"SELECT Max(Table2.place) AS Maxplace, Table2.table1id
FROM Table2
GROUP BY Table2.table1id;"
Insert Statement (used in ASP.NET Form):
"INSERT INTO Table2(name, table1id, place) SELECT @name, @table1id, maxplace+1 FROM qryMaxTable2 WHERE table1id=@table1id"
The problem is that the new table1id doesn't return any items and thus "maxplace" is empty and the code doesn't work (ASP.NET) - So I can't use the newly added entries to Table1 in Table2.
Would appreciate help in solving this.
Thanks,
Roman