Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-05-26 : 08:00:45
|
| Dave Mackenzie writes "Hey Guys,Windows: WinXp Pack 2SQL: SQL 2000Access: 2000I've got a question for you, hopefully you can help me, I've searched the Microsoft website to no avail. I'm working on a Microsoft Access project for an Inventory Tracking Project, and I have run into a wrinkle.I have an SQL statement that does the following - returns any product that falls under the Minimum Stock level and needs to be re-ordered. - Upon reorder, it groups the products by supplier and creates a seperate Purchase Order for each supplier with the listed products.- Here's the problem, when reordering I need to create a Unique POID, like 00075 or 00076. So i use a max funtion to return the last POID then add one, this works . . . for the first one, it then goes on to give the same number to ALL the Purchase Orders, no incrementing.In the purchase Orders table, where the info is going, the Purchase Order ID is a number field and NOT the PK. The PK is an autonumber field, I made the POID field seperate so that if the PO's are deleted the numbers can be reused without any hassle for the user.Do you guys have any ideas?Thanks for your time and expertise.Dave." |
|
|
jhermiz
3564 Posts |
Posted - 2005-05-26 : 13:10:49
|
Maybe a design issue, but why 00075..etc..is this your business logic or something you chose? Should of probably been an autonumber to make life easier..either way I dont see how it is using the same one if you are using a MAX() + 1 call.
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-26 : 15:24:09
|
| Are you doing this as a set? If so, the max number is not being incremented because the individual rows are not being updated iteratively, so the highest number is the same for every row in the set.If POID is a number field as you claim, then it will trim 00075 to just 75. You would have to format it during output to get the leading zeroes back.Why would you want to reuse a POID? Once it has been used, even if it gets deleted, isn't the number now spoiled? It used to refer to one purchase order, but now it refers to something entirely different because the first one was deleted? It's not really an ID number then, is it? This sounds like a bad idea. Is there any other compelling reason to re-use POIDs?Actually, it's debatable whether you should allow POs to be deleted. Perhaps they really should be marked as invalid or have a DeletedFlag on them, but not really delete the data. That way you can always account for the POID and it's true instance that it identifies.---------------------------EmeraldCityDomains.com |
|
|
|
Hyukevain
Yak Posting Veteran
66 Posts |
Posted - 2005-05-28 : 14:35:37
|
| DECLARE @POID VARCHAR(5)DECLARE @CURSOR CURSORDECLARE @PRODUCT VARCHAR(255)DECLARE @SUPPLIER VARCHAR(255)SET @CURSOR = CURSOR FOR SELECT PRODUCT, SUPPLIER FROM [TABLE1] WHERE STOCKLEVEL < Something GROUP BY SUPPLIEROPEN @CURSORFETCH NEXT FROM @CURSOR INTO @PRODUCT, @SUPPLIERWHILE @@FETCH_STATUS = 0BEGINSELECT @POID = RIGHT('00000' + CONVERT(VARCHAR, ISNULL(MAX(POID) + 1, 1)), 5) FROM [Purchase Order]INSERT INTO [Purchase Order](POID, PRODUCT, SUPPLIER) VALUES(@POID, @PRODUCT, @SUPPLIER)FETCH NEXT FROM @CURSOR INTO @PRODUCT, @SUPPLIERENDCLOSE @CURSORDEALLOCATE @CURSORSomething like this ?? |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-31 : 13:59:04
|
| Hyukevain, that code is in SQL Server syntax, and I do not believe it will work in Access (this is the Access forum). Second, I would avoid using a cursor. Third, I still think the idea of reusing POIDs is questionable.---------------------------EmeraldCityDomains.com |
|
|
|
Hyukevain
Yak Posting Veteran
66 Posts |
Posted - 2005-05-31 : 21:16:08
|
| Dave using SQL Server. The code can be store as stored procedure, cek at the top of the thread and cursor works fine with me. |
|
|
|
|
|
|
|
|
Record Count