Thought processes - still planning things

[Ani]

Hi folks
Please can you help with my thought processes for my first ever hobby db. I have done the following:

I have created 7 tables, one of which is a jctn table.

I have joined the tables.

I have normalised the tables and added LookUp parts to the fields with options.

I have read about creating forms and created and deleted forms for some practice.

I have read and looked at how queries are created and how/why it is good practice to create forms using queries.

I have written down what queries I will need to perform, that will be useful to me. Some are obvious and will require a few fields from several tables. These make sense to me and I should not have a problem creating, using the queries to create the forms.

I now have a number of questions in my head which I would greatly appreciate advice about

How do I know if the relationship joins on my tables are correct?

Should I create forms and queries to test the joins or is there more to read?
I have read sections about what the types of joins 'do' and have a basic understanding.

The major question in my head is "how do I bring together, all the information, I need to perform my end task?"

My end task is writing a letter which advises the customer about the health of their horse. This letter is based on information the customer provides and results of the tests I perform and is formed from my interpretation of the information, my knowledge and experience.

The information needed is placed in 4 tables-
CustomerDetails - just FirstName and LastName needed
HorseDetails - all fields except ID's - total of 14 fields
PastureManagement - all fields except ID's - total 14 fields
Results - total 4 fields
Should I create a query for this as well and then worry about how it is displayed afterwards? What would be the most efficient way to achieve this or do I need to approach this in a different way?

It is quite a chunk of information, at present I gather the paper forms together and sit at my pc flicking through the forms to write the letter. Oh perhaps I have just answered my own question! I am interested to know how others would deal with this though.

I hope I have written this in a way that makes sense. Please let me know if I need to think about this in a different way.
Many Thanks
Ani

 

[NigelShaw]

Hi,

have you looked at this?

http://www.equinemax.com/equinesoftware/smallbusiness

I part made a program similar for my wife a few years ago but i havent done anything on it for a while. I'll read through your post a little in depth later and reply to some of your answers


Cheers

nidge

 

[RainLover]

You have mentioned Joins but have not mentioned Relationships.

I would suggest that you create your Relationships then post a Pic of them here.

It is a good way for us to get an understanding of what you want to track.

Then we could give you some feedback.

The other thing to do which is better is to post the actual database.

 

[Ani]

You have mentioned Joins but have not mentioned Relationships.

I would suggest that you create your Relationships then post a Pic of them here.

It is a good way for us to get an understanding of what you want to track.

Then we could give you some feedback.

The other thing to do which is better is to post the actual database.

Oh how amazing! You would look at what Ive done! Thats awesome! Thank you.

Errm the joins and relationships thing has me slightly confused. Its the terminology. From what I understand a table is a 'relation' so I wasnt sure how to explain that Id put the lines on joining the tables. I will try and put the actual db on....errm looking at how I attach it!
Thank you
Ani

 

[RainLover]

A Table has a Relationship with another Table.

This is done by joining Fields. Namely the Primary key in one to a Foreign key in the other.

Pleanty of information on the WWW.

 

[Ani]

Hi RainLover
attached db as done so far. Thank you. Im very grateful that you will look at it!
Ani

 

[RainLover]

Ani

Could you please convert to A 2003. I don't have the latest versions.

Thanks.

 

[Ani]

Oh sorry...I just noticed the sentence about attaching as 2003 Access. Should I save again and re-attach?
Ani

 

[RainLover]

Yes Please

 

[Ani]

I dont seem to have the option to save as an earlier version. Sorry, please bear with me I will try and work it out.
Ani

 

[RainLover]

Top left corner of the screen.

There should be a Save As somewhere.

Search Access Help

 

[Ani]

Yes. The save as option does not give me the choice to save as an earlier version. I just looked at this
http://office.microsoft.com/en-us/access-help/change-the-default-file-format-HA010341530.aspx

I dont have a 'Popular' on the options dialog window. Ive tried saving the file with mdb ext.
Can attach and see if it works?

 

[RainLover]

Use Google.

Search Save Access 2007 as Access 2003.

Sleep time for me.

I can use another machine tomorrow to convert it myself.

I will get back to you sometime over the week end.

Bye for now.

 

[Ani]

OK Thank you. Sleep well! G'night.
Ani

 

[Ani]

Use Google.

Search Save Access 2007 as Access 2003.

Sleep time for me.

I can use another machine tomorrow to convert it myself.

I will get back to you sometime over the week end.

Bye for now.

I found this,
http://office.microsoft.com/en-us/a...er-file-format-HA010341553.aspx#_Toc258329153
Hopefully it has worked.
Ani

 

[RainLover]

Got it.

For a First attempt very good.

Problems in Naming. Don't use special characters like &. and - or / (), Don't have age, have DOB and calculate the age. Some Field names appear far too long. Save a bunch of typos now and shorten them.

Always use Date Data Type for Dates. Do not use text.

Normalise more. e.g. tblHorse details is about the animal itself. Things that don't change. A new Table tblHorseHistory would store LastWormDate, TypeWormer, CurrentIssues.

The results appear for a person not a Horse. Wrong linking.

Horse Name is used in three tables, It only needs to be in one. tblHorseDetails.

Please review the above and rethink overall. Post back again with second draft.

 

[Ani]

Got it.

For a First attempt very good.

Problems in Naming. Don't use special characters like &. and - or / (), Don't have age, have DOB and calculate the age. Some Field names appear far too long. Save a bunch of typos now and shorten them.

Always use Date Data Type for Dates. Do not use text.

Normalise more. e.g. tblHorse details is about the animal itself. Things that don't change. A new Table tblHorseHistory would store LastWormDate, TypeWormer, CurrentIssues.

The results appear for a person not a Horse. Wrong linking.

Horse Name is used in three tables, It only needs to be in one. tblHorseDetails.

Please review the above and rethink overall. Post back again with second draft.

Thank You! I was expecting more things to be wrong! Will follow through with your suggestions. Im not sure I should abbreviate field names though. I find it difficult to remember abbreviations in correct orders unless they are standard ones e.g. 'avg' but I will try.
You didnt have much sleep time!
Almost time for me to go up the wooden hill, not sure I will sleep well with the wind making everything creak!
Thanks again
Ani
PS the suggestion of table Horse History is quite apt, my original paper form is called Horse Health History and has all the questions/information on it from the Horse details and Pasture tables!

 

[Ani]

Start by removing any table-level lookups. They will cause nothing but trouble once you start writing queries. There are lots of posts here regarding why lookups belong on forms NOT on tables.
Until you get some experience, you'll just have to try them out. With the lookups gone it will be a little tedious but you can use forms instead of typing directly into the tables.

Thank You! Panic...just looked at the Lookups and I dont know how to remove them...more reading I think!
Almost bedtime now though.
G'Night
Ani

 

[Beetle]

Panic...just looked at the Lookups and I dont know how to remove them...more reading I think!

This thread might help.

 

[RainLover]

Thank You! I was expecting more things to be wrong!

Who said I was finnished.

A lot more to do yet.

e.g. City. Is this not repeating Data. It requires a separate table as a lookup.

How many more things like City can you find. More importantly how many things like Horse History can you find.

As you have included Financials this Database could blow out. Do you use a Financial package like MYOB. If you do we could vary the Database quite a lot.