Question Kind of a unique problem (1 Viewer)

[fedwa001]

Afternoon,

I have recently taken on a new project as one of my hobbies is growing beyond an Excel Spread Sheet. I breed ball pythons. I have begun building the database in Access. I have tables and forms and a few quiries that all work as far as general information for each snake. My next step is to track all the daily information such as: feed, feeding dates, next feed date, shed, shed date and so on and so forth. Esecientially I need to be able to look back for data to build charts and reports to help me improve my husbandry practices. I'll be honest I have no clue where to start on this portion of the design. Any help would be greatly appreciated. Attached are a few screen shots of what I have so far...HELP lol

 

[plog]

You gave a good plain english description of your project, but the images are lacking. Its best to set up your relationships in the Relationship Tool and then post a screeen shot of that. From that we can see all your data and make sure its structured properly to capture all you want.

From the 1 table you did post, I see that you aren't really capturing a history of data. You capture last weight, last bodily movement, etc., but you don't keep a record of all the others. It seems you would just keep overwriting this data. You would never know the weight of a snake at a particular time. I could see that being helpful in what you stated was the goal. For that you would need a new table, one specifically to capture the weight and date of each snake.

Again, a full screen shot of the relationship screen woudl be helpful.

 

[fedwa001]

That's the part I'm trying to figure out. How to track the history for each snake? I'll try to get more pictures for you in a bit.

 

[fedwa001]

Would I have to create a table for each individual reptile. Depending on how good a year is there could be a couple hundred

 

[plog]

You should have a Snake table:

Snakes
snake_ID, autonumber, primary key
snake_Name, text, name of snake
snake_DOB, date, date snake was born
snake_Gender, text, gender of snake
...etc...

Then I would have a table to capture all the Attributes you wanted to track (Weight, Shedding, BodilyMovements, etc.)

Attributes
att_ID, autonumber, primary key
ID_Snake, number, foreign key to Snakes table
att_Type, text, would hold if Bodily Movement, Shedding, Weight, etc.
att_Date, date, date attribute occured/measured
att_Value, number, would hold weight, size of bodily movement, amount of shedding, etc.

 

[plog]

No, 1 table for all your snakes. You should read up on normalization:

https://en.wikipedia.org/wiki/Database_normalization

 

[fedwa001]

You gave a good plain english description of your project, but the images are lacking. Its best to set up your relationships in the Relationship Tool and then post a screeen shot of that. From that we can see all your data and make sure its structured properly to capture all you want.

From the 1 table you did post, I see that you aren't really capturing a history of data. You capture last weight, last bodily movement, etc., but you don't keep a record of all the others. It seems you would just keep overwriting this data. You would never know the weight of a snake at a particular time. I could see that being helpful in what you stated was the goal. For that you would need a new table, one specifically to capture the weight and date of each snake.

Again, a full screen shot of the relationship screen woudl be helpful.

Hope this helps.

 

[fedwa001]

You should have a Snake table:

Snakes
snake_ID, autonumber, primary key
snake_Name, text, name of snake
snake_DOB, date, date snake was born
snake_Gender, text, gender of snake
...etc...

Then I would have a table to capture all the Attributes you wanted to track (Weight, Shedding, BodilyMovements, etc.)

Attributes
att_ID, autonumber, primary key
ID_Snake, number, foreign key to Snakes table
att_Type, text, would hold if Bodily Movement, Shedding, Weight, etc.
att_Date, date, date attribute occured/measured
att_Value, number, would hold weight, size of bodily movement, amount of shedding, etc.

Okay, so I already had the two tables you mentioned in the above quote. I just need to read this article you sent lol

 

[plog]

Tables with only 1 real value field shouldn't exist. That means Sex and stateT shouldn't be tables. Instead you simply store they value they relate to in BallPythonT.

Also, your relationship screenshot is very lacking, i can't see all the fields in BallPythonT and the initial table you posted isn't anywhere in there.

 

[Svencanz]

In the Snake table, you probably want to have the basic background data. You can then add various EVENTS table that link back to the Snake table. You could have a Feed table for instance (as this being an event)
Or your Events table could contain different classes of even (Feed, Weight, or whatever) that could be implemented as a drop-down or a code. You then have Date and Comment fields for the rest of the Event record.

 

[fedwa001]

Tables with only 1 real value field shouldn't exist. That means Sex and stateT shouldn't be tables. Instead you simply store they value they relate to in BallPythonT.

Also, your relationship screenshot is very lacking, i can't see all the fields in BallPythonT and the initial table you posted isn't anywhere in there.

Okay I think I am sorta getting it. I cleaned up the relationships. I do have 1 table on there you may notice that says Basic_Morph. this field only has one column but it has 281 entries. I can delete that one too if I can figure out how to copy and paste lol

 

[Svencanz]

From your image Relation2.jpg it's also clear you need a Breeder table. Unless every breeder only breeds one snake

 

[fedwa001]

From your image Relation2.jpg it's also clear you need a Breeder table. Unless every breeder only breeds one snake

I don't think I need one because I have the Morph Table. It has 281 of the basic morphs. Eventually I will Add the 4,000 something designer morphs.