Advise Needed

[Bob]

oops Ok, I need to set up a different table for Category so as they will
have there own ID number?...Thanks BOB

Thanks Arvin this is the query I set up, had to have aName function in it
so as un-named horses appear under their breeding and age:
SELECT tblRemarks.Category, tblRemarks.dtDate, tblRemarks.HorseID,
tblRemarks.Remark, tblHorseInfo.HorseName,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
tblHorseInfo.FatherName, tblHorseInfo.MotherName,
tblHorseInfo.DateOfBirth, tblHorseInfo.Sex
FROM tblRemarks INNER JOIN tblHorseInfo ON tblRemarks.HorseID =
tblHorseInfo.HorseID
ORDER BY tblRemarks.Category DESC , tblHorseInfo.HorseName,
funGetHorse(0,tblHorseInfo.HorseID,False);
This look OK to you looks good in datasheet View......Thanx.....Bob

So Bob,

Let's say you are looking to display the last record in each category for
each horse. The base query (i.e. the query that shows the base
information that you want to display) is all in 1 table. The query would
look something like:

Select HorseID, CategoryID, Max([DateField]) As MaxofDateField
From MyTable
Group By HorseID, CategoryID
Order By Max(DateField) Desc;

In addition to the above, you'd have other tables to join on HorseID, and
adding the HorseName, and join on CategoryID and adding the Category.
Obviously, you'd need to use your table and field names, but I think you
get the idea now.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Pat Thanks for the advise, I suppose the 39 tables is too much for just
storing some information
On my frmHorseInfo I had a subform that I used for keeping remarks for
horses,
It has 3 Boxes
1-Dropdown cb that has shortcuts to enter data in to [Remarks]
2-Date tb that enters today's date in when data is entered into
[Remarks]
3-[Remarks]- tb that holds remarks written in
It is linked to a table that has,
SrNo-Number-Serial Number to HorseID
dtDate- Date/Time-Date of remark
HorseID- Number-Horse ID Number
Remark- Text-Data entered
added=Category-Text-

I added the field Category to this tblRemarks, then added it to
frmRemarks, made a query qryCategory ,Is Not Null ,Unique Values Yes
Added a cb for qryCategory with control source Category
Added Category to my report that is filtered to only that horse , and
showed category fine
Looking at the table it all looks good category is being entered next to
the HorseID

So now I have to think of a way to show all horses last record in that
Category I select, and one horse with all data from That category
Am I on the right track now!!
Thanks for any In put.........Box


Please step away from the computer. Take a deep breath. Pick up a
piece of paper and a pencil. Use it. You have a lot of attributes and
a lot of the attributes have multiple occurrences which will require
separate tables. It is imperative that you get the table schema
correct or reporting from the mess will be next to impossible. Spend
some time reading about normalization and relationships. time you take
to do this now will be repaid 20-fold by eliminating rework and
reporting problems. Take a stab at creating a proper schema. Make
lists of related fields and pay attention to the number of instances
you need to keep. If you need to keep more than one, you need a new
table. Each field that in your current design is suffixed with a
number belongs in a separate table. I didn't count the repeating
groups but it looked like around 5. That means 6 tables. One for the
horse, and one for each of the repeating groups. Having more tables
doesn't make the database more complex, it makes the data extraction
easier. In the world of spreadsheets, you just add another suffixed
column if you find you didn't originally define enough. If you do that
in the world of relational databases, you will be changing
queries/forms/reports/procedures until your fingers fall off when with
a proper design all you would need to do is to add a row - no structure
or logic changes of any kind would be required.

Once you have taken a stab at restructuring the tables, post back and
we'll help you refine the structure.



I have about 100 fields in my table, I have extended the form to fit
all these in. I could move about 50 off them to another form and use
another table, would that be a better idea. And if I did how would I
keep the same CustomerID and Auto Number....Thanks for any help...Bob