Whats the best way............

[kevmeister123]

I have a project to do in Access. I'm not really a newbie, but only ever used it very basically in the past and so i'm still learning as I go.

I've learned how to create tables, set properties for field types etc.
I've learned how to create forms and subforms and link to tables.
I've learned how to create queries.
I think i've learned how to create relationships (although it still a little guess work)

What my project is, is the management of sheep.

I have a table & form for when I purchase a sheep.
I have a table & form for when I sell a sheep.
I have a table & form for when a sheep is lost (or died)
I have a table & form for new born lambs.
I also have a table & form for "Current Stock"

This is the problem. What I need to know is...How simple it would be to make the "Current Stock" Form/Table = Live.

Probably the Current Stock should be a query instead, and so each time it is run it would be "current" but again i don't know how to go about it.

Basically instead of entering a sheep into purchased, and then into Current, I would enter it into purchased then it would automatically be displayed in current. Then if I click a magical "SOLD" button, (without changing the "purchased" table) that particular sheep in Current, would move into the sold form/table.

I know this seems like a lot, and am not really asking someone to "do it for me", just really need to know how easy, and what's the best way to go about it.

Thanking you kindly.

Kev

 

[Remou]

Do you have 5 tables? I think this is too many. You need a table for sheep and a flag (or two) to say whether (wether ) it is a newborn, sold, lost or died. That way, a newborn lamb becomes a sheep at the proper date, passing through hogget etc, as required, similarly, a purchased sheep is immediately current. Current is then just a count of all the records in the table that have not been closed by death, sale or loss.

 

[addy]

OK

A little more detail is needed, for example, your purchase table - how much detail are you storing:

Date of purchase?
Cost?
Purchased from?

Selling table:

Date of sale?
Cost?
Sold to?

Lost table:

Date lost / died?
Reason / circumstances?

etc. etc.

Give us a few more details and we'll try and help!

 

[addy]

In addition:

Remou is correct if you are only keeping very basic information, such as for purchases, if you only need to know the date purchased then you can reduce the number of tables.

If, however, you need more info (see my first post) then you may need additional tables and you would use am ID field to uniquely identify each sheep which could be used to relate the various tables.

 

[kevmeister123]

Thank you for your replies.

My tables do actually contain a lot more data, i was trying to simplify to try to explain (sorry).

My purchase table has:
ID(prmy), Breed (selection from a breed table), Category (selection from a category table), Date of Purchase, Moved From (from an address table) Moved To (address table), Paint Mark, Flock Tag #, Cost.

Sold table has:
ID, Breed, Category, DofB, PaintMark, FlockTag, Value, SellPrice, Sold To, Date of Sale.

Born has:
ID, Breed, Category, DofB, PaintMark, FlockTag, Sire, Dam, Docked?.

Lost/Died has:
ID, Breed, Category, DofB, PaintMark, FlockTag, Value, Age(At loss), Date(at loss), Status (lost/dead)


So there are lots of common fields, and the main common fields are references to another table.

With so many tables etc, it just seems all to perplexing at the moment. %-)

 

[Remou]

Addy
Surely this still resolves to one sheep table? Is not purchase details a separate table? Similarly, details of death may need to be kept for government/veterinary reports but that too would be a separate table, as would health information etc etc.

 

[Remou]

You need one sheep table, this will simplify things. Breed, Category, DofB, PaintMark, FlockTag, Value/Cost needs to be recorded once, along with Status, Date of Birth and other such details that apply to all sheep. Additional tables would show:

Purchase table:
ID (Foreign Key to Sheep table), Date of Purchase, Moved From (from an address table) Moved To (address table), Cost.

Sold table:
ID (Foreign Key to Sheep table), SellPrice, Sold To, Date of Sale.

Born table:
ID (Foreign Key to Sheep table), Sire, Dam, Docked?.

I wonder about this table as you may wish to include sire and dam for all sheep, so it is possible that this table can be scrapped.

Lost/Died table:
ID(Foreign Key to Sheep table), Date(at loss), Status (lost/dead)

Date at Loss will give you age at loss, if Date of Birth is included in the sheep table. I wonder about this table too, as you may wish to included an end date for all sheep, with a status to show what the end is: loss, death, sale etc.

 

[kevmeister123]

right, i've re worked it as you said. I now have 1 "Sheep" table with the "common" data. and removed the fields no longer needed in the other tables. I then linked the NON PRIMARY ID field in each table to the Primary ID field in the sheep table.

It looks pretty good. I then used the wizard to create a form, it will create a "Sheep" form, with a subsheet "Purchase" form, but not the other way around.

Is this the best way to do it?

or should i create a new "Purchase" form, then a "Sheep" form, then drag the sheep form onto the "Purchase" form?

 

[Remou]

That sounds about right. You can even create a master sheep form, with several subforms, if you wish. The tab control is good for this kind of set up. If an event can occur twice in the life of a sheep, you can create a primary key in the events tables (purchase, for example) by combining ID and Date, if the event can only occur once, the ID can be both the primary key and the foreign key.

Make sure that the subform refers to a form, not a table. In Access 2000, Autoform will create a form and subform, but the subform references a table.

Here is some useful reading, in case you have not seen these articles yet.

Relational links

http://r937.com/relational.html

http://www.alphabetcitydataworks.com/fundamentals.htm

(same as above, different format)
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

SQL Joins

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

List of reserved words in Access 2002 and Access 2003

http://support.microsoft.com/kb/q286335/

 

[IGPCS]

i still dont understand why you r using so many tables you could use 1 table with some more fields and then set the forms as you want the data by the type of data you enter and to get results from 1 table is much easier and you can get much more results like that

 

[kevmeister123]

Remou thank you very much for your help it is certainly easier that way.

Although IGPCS you do have a point. I was wanting to keep the data seperate thinking it would be neater and easier to control. But i've now amalgamated all the data into one table and it is a lot easier to just show the fields I choose on my seperate forms. and it is also easier to create a query too.

Thanks everyone for your help. I think i have my database looking a lot simpler and easy to manage now.

many thanks

kev

ps. i'm sure i'll be asking a few more questions later on in a different thread if you don't mind. :O)

 

[IGPCS]

i specificly never mind i like toi help and share with others my opinions it gives me confident with what i do

you welcome