help with my sheep database

[kevmeister123]

I hope my thread is in the right forum this time.

Anyway, I need some advice regarding my sheep stock database.

At the moment, say for example I buy 10 sheep. I create 1 new line and in the quantity field I put 10 And change the status to "Current Stock". That's how Defra like it, simple.

However, my problem is, say I bought 10, then sell 1.

The long answer is create a new line with the same details for the 9 sheep that have not been sold (Status Current), then create a new line for the 1 sheep that i have sold and give it a "Sold" status.

Is there an easier work around, or perhaps a way of automating the process. Or is "Too" involved? If it is then i'll leave it as is.

Thanks for any help and advice in advance.

Kind Regards

Kev

 

[mp9]

I think your best bet would be to create 10 lines in your table when you buy the 10 livestock, one for each sheep. Then each sheep has its own status. When you later sell a sheep all you have to do it change the status of that sheep.

If you're buying lots of sheep at a time, you can bulk add multiple lines using an Append query.

For Defra reporting, you can use something like SELECT COUNT(*) FROM tblSheep WHERE STATUS='Current Stock' to count how many sheep are still current.

 

[kevmeister123]

thanks for your help mp9, originally i was going to do it that way as it's easier to get my head around it that way, only it would take a lot lot longer to enter all the data into the database, 10 is just an example as say we after lambing we sell 500 lambs, entering 500 new lines would take quite a while, that is why defra only expect you to log flock movements not sheep movement. But what i think i'm gonna do is simply enter a new line for the 9 sheep still current, then just edit the old line to 1 and a status of sold. That way it's still fairly simple (albeit not perfect), in that i only enter 1 extra line at each new event, and it is still recorded as defra want it.

thanks again for your help

Kev

 

[mp9]

Or you could just maintain a table of sheep "transactions".

E.g.

Date NumberOfSheep Transaction
1/1/2005 100 Purchase
1/4/2005 300 Lambing
1//9/2006 250 Sale

Then you could query at any time to determine current stock as the sum of Purchase transactions and Lambing transactions minus the sum of Sale transactions.

 

[kevmeister123]

That is a good idea, someone else did mention a transactions table, but I didn't really understand what they meant. But yes that does sound good, plus it keeps intact the original purchase details as well.

I'll give that a try.

Thanks again mate much appreciated

Kev