Aggregate function question

[BomberMan2K]

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

 

[PHV]

qryMaxTable2:
"SELECT Nz(Max(Table2.place),0) AS Maxplace, Table1.table1id
FROM Table1 LEFT JOIN Table2 ON Table1.table1id=Table2.table1id
GROUP BY Table1.table1id;"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BomberMan2K]

Nope.

Thank you for your help - but it still doesn't see the new item.

I think an aggregate function doesn't return a null, rather it returns nothing at all in such circumstances.

Thanks,
Roman.

 

[PHV]

And this ?
"SELECT Max(Nz(Table2.place,0)) AS Maxplace, Table1.table1id
FROM Table1 LEFT JOIN Table2 ON Table1.table1id=Table2.table1id
GROUP BY Table1.table1id;"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BomberMan2K]

Still not...

 

[PHV]

Still not...
Seems like the LEFT join don't work?????

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BomberMan2K]

Actually, you don't need the join... I just care about the Table1id - so I don't really want the join.
I've checked and the results are the same with or without the join... so the problem is not there.

It seems like an easy problem ) but it's not...

 

[PHV]

the results are the same with or without the join
Could you please post the code you tested with the join ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886