2

Here is a sample of my data:

enter image description here
ID - autonumber ~ is their primary key
Week - literally the week piglets were born
Sow Order - Sow number

Rules:
1. If piglets were born the same week, regardless their Sow. Piglet numbering continues
2. If piglets were born next week, numbering returns to 0001 then Rule 1.

The way piglets were to be added is, week + sow + (number of piglets). For example, (10), it will generate ten piglets 0001 to 0010 in week=01 & sow=01.

The problem is, what if the user had a mistake on the number of piglets to be added? Based on the picture, 01 01 has two piglets, what if its not just 2? Say, its 3. So user need to delete it and then numbering MUST be adjusted to.. 

01 | 01 | 0001  
01 | 01 | 0002  
01 | 01 | 0003  
01 | 02 | 0004  
01 | 02 | 0005

What is the proper method to do this kind of stuff?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
AdorableVB
  • 1,383
  • 1
  • 13
  • 44
  • you are generate the Piglet number. explain clearly how to generate he piglet number – Sathish Feb 13 '14 at 10:14
  • 1
    Why must you re-number the existing piglets from sow 02? Why can't the newly-discovered 3rd piglet from sow 01 be numbered 0005? The reason I ask is because once you assign an ID number to a "thing" and you use that ID number somewhere else (i.e., in another table) then changing the ID number of an existing "thing" usually should be avoided if possible. – Gord Thompson Feb 13 '14 at 10:21
  • well, user wants it to be numbered continuously, since adding piglets depends on how many piglets were (which data come by batch) so it must be following the protocol of each of pigs earnotch number from the farm. This input error is very unusual, but from a data managing software, this is important. @SATSON it generates piglets based on how many the user input. – AdorableVB Feb 13 '14 at 10:31
  • Do you want this to be done using pure SQL or with the help of your backend? – chris_techno25 Feb 13 '14 at 10:46
  • the ID is usually abstract and as Gord points out, it is not a good idea to change them (ever). the requirement that they be consecutive or editable **really** means there is another user field (and/or it is not a candidate pr the PK). ID therefore could be autonumber and probably never shown to the user, while a `UserTag` column displays "000N". The user could then edit them or your code could resequence them as needed, and nothing breaks. Anything that can change (like a phone number) is a bad idea for a PK. – Ňɏssa Pøngjǣrdenlarp Feb 13 '14 at 13:40
  • I will not change PK cause its auto-numbered. I just wanted to know how can I adjust `PigletNumber` if there will be deleted or newly added piglets so that data will not be redundant. Or am I the one not getting what you guys try to say? might as well check out what @GordThompson answered. – AdorableVB Feb 14 '14 at 01:11
  • sounds like there is supposed to be a sow-piglet counter ID which your DB is missing. the Piglet text number could/should just be formatted from that sow-piglet ID. – Ňɏssa Pøngjǣrdenlarp Feb 14 '14 at 01:46

1 Answers1

1

If the Access database version is at least Access 2010 then event-driven data macros can be used to enforce the numbering scheme. For a table named [Piglets]

PigletsTable.png

with data macros for [AfterInsert]:

AfterInsert.png

[AfterUpdate]:

AfterUpdate.png

[AfterDelete]:

AfterDelete.png

and a named data macro [RenumberPiglets]:

RenumberPiglets.png

the renumbering takes place automatically, even if the table is updated from an external application (e.g., VB.NET or C#).

For Access database versions 2007 and earlier, similar logic would have to be written into the data-entry form(s) used to update the table.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • I have downloaded your sample, but how can I open the macro that you created? Whenever I click Create>Macro, it creates a new one. How can I see the one that you did there? I tried to delete and its auto-adjusting, I just need to study more. thanks – AdorableVB Feb 14 '14 at 01:47
  • @AdorableVB Have a look at the documentation [here](http://office.microsoft.com/en-ca/access-help/create-a-data-macro-HA010378170.aspx#_Toc264895170). – Gord Thompson Feb 14 '14 at 02:23
  • Thank you for this, I will incorporate your work to my app, coz I am past on my deadline. lol though I've not really understood it all, I will after I finish this. many thanks to you :) – AdorableVB Feb 14 '14 at 03:48