Prev: How to user Dataset created by Visual Studio 2008
Next: Little stevie is soliciting again.
From: strive4peace on 3 Jul 2008 12:53
you're welcome, JSSherm (what is your name?)

I added on under Klatuu's message with comments about storing the data

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




saintjohn4059 via AccessMonster.com wrote:
> Hi Crystal,
>
> The species selection is aviary, bovine, canine, equine, and feline (no
> exotics or other species offered care). This is going to be a no-kill
> facility with large volumes of animals and the text values can be highly
> repetitive (i.e. dogs and/or cats named Sam) and with the unique number the
> hard copy records can be better managed (much like a human medical care
> office that files with a numbering system). Plus the ID numbering system
> allows for better cooridnation of the animal through the various kennel
> procedures such as medical and labratory procedures, outside services
> required, grooming, adoption and/or fostering, etc. Thank you for the
> "strive4peace" link and I will check it out, and may you too have an awesome
> day! :)
>
> Sincerly,
> JSSherm
>
> strive4peace wrote:
>> this is possible ... but what about different species that begin with
>> the same letter?
>>
>> the unique ID of a record does not need to be shown to the user; it
>> really doesn't matter what it is; the user can pick from text values and
>> a numeric ID can be stored
>>
>> read this:
>>
>> Access Basics
>> 8-part free tutorial that covers essentials in Access
>> http://www.AccessMVP.com/strive4peace
>>
>> Warm Regards,
>> Crystal
>>
>> *
>> (: have an awesome day :)
>> *
>>
>>> Hi, I'm brand new to MS ACCESS and I am using MS ACCESS 2007. I would like to
>>> make a drop down list of different animal species for an animal shelter
>> [quoted text clipped - 6 lines]
>>> Is any of this possible? As a newbie I can create a table with a progressive
>>> ID# list but cannot figure out how to give each item a unique ID#.
>
From: Klatuu on 3 Jul 2008 13:04
Hi, Crystal,

I agree about the primary key. I did not address that in my previous post,
but it is a good point.

I'm not sure I agreee about not storing the species identifier. I'm not
sure a cat is going to change into a dog (although one of my cats acts like a
dog), nor will anyone confuse a dog with a cat.

The numbering technique I suggested uses different numbering for each
species. You would have a D00001 and a C00001. You say you could calculate
the species. How would you propose to do that?

--
Dave Hargis, Microsoft Access MVP


"strive4peace" wrote:

> adding on...
>
> Yes, it would be best to store the information separately ...
> I would NOT make this the Primary Key for the table; multi-field keys
> are more difficult to work with than single-field keys; this combination
> can be unique in the table so it can be the identifier for human eyes.
>
> Personally, I would use an autonumber field in the table for the Primary
> Key -- and use that in related tables as the foreign key. The users
> never need to see this number
>
> ~~~
>
> The only reason to store the letter identifier would be if the species
> is changed (maybe a mistake was made when the record was created) and
> the animal is already tagged -- but if the Species is edited and you
> would then tag it differently, then I would not store the species letter
> at all -- you can calculate it anytime...
>
> {Hi Dave <smile>}
>
> Warm Regards,
> Crystal
>
> Access Basics
> 8-part free tutorial that covers essentials in Access
> http://www.AccessMVP.com/strive4peace
>
> *
> (: have an awesome day :)
> *
>
>
>
>
> Klatuu wrote:
> > It would be best to use two fields to do this. Once using the letter
> > indentifier for the species and another for the unique number. You can then
> > use queries to combine the two fields for display. To assign the number, you
> > can use the After Update event of the combo box to assign the number and
> > present it in an unbound text box. You should have a hidden text box that is
> > bound to the unique number field. For example purposes:
> >
> > Table fields
> > SpeciesID - Text(1)
> > AnimalNumber
> >
> > Form Controls
> > cboSpecies - Your combo to select a species bound to SpeciesID
> > txtAnimalNumber - Hidden control bound to AnimalNumber
> > txtUniqueID - Display Control
> >
> > Make the Control Source of txtUniqueID =cboSpecies & txtUniqueID
> >
> > Private Sub cboSpecies_AfterUpdate()
> >
> > Me.txtAnimalNumber = Nz(DMax("[AnimalNumber]", "tblAnimals", "[SpeciesID
> > = """ & Me.cboSpecies & """"), 0) + 1
> > Me.ReCalc
> >
> > End Sub
> >
> > The DMax find the highest existing number for the species selected. If no
> > records exsit for the species, the Nz converts the Null that would be
> > returned in that case to a 0, then 1 is added to it to get the next number.
> > The ReCalc causes cacluated controls to be recalculated. this is necessary
> > because populating a bound control programmatically will not recalc
> > calculated controls.
>
From: John W. Vinson on 3 Jul 2008 13:13
On Thu, 03 Jul 2008 11:49:07 GMT, "saintjohn4059 via AccessMonster.com"
<u12572(a)uwe> wrote:

>Hi Crystal,
>
>The species selection is aviary, bovine, canine, equine, and feline (no
>exotics or other species offered care). This is going to be a no-kill
>facility with large volumes of animals and the text values can be highly
>repetitive (i.e. dogs and/or cats named Sam) and with the unique number the
>hard copy records can be better managed (much like a human medical care
>office that files with a numbering system). Plus the ID numbering system
>allows for better cooridnation of the animal through the various kennel
>procedures such as medical and labratory procedures, outside services
>required, grooming, adoption and/or fostering, etc. Thank you for the
>"strive4peace" link and I will check it out, and may you too have an awesome
>day! :)

It's best NOT to mix meanings within a field. The species really doesn't
belong in the identifying number field; they are two different fields with two
different purposes. The animal-shelter database I've developed just uses a
sequentially assigned numeric identifier; at the shelter's insistance, I've
bent my purity a bit and include the year in it - e.g. 08-02481 would be a
"SoftSlip" identifier (the name if for historical reasons) issued during
fiscal year 2008. There's a separate field, entered using a combo box, for the
species (and another for the breed, for that matter).

If you embed the species in the unique identifier you'll need some VBA code to
assign it, the maintenance will be a chore (Hey! C-0451 got put into the
system, but that must have been a transcription error, he's a poodle!!), and
you'll have to restructure your database if you decide to start accepting
ferrets, wildlife, or llamas; or you might decide that you want to break the
"avian" down below the taxon Aves.

If you'ld like to discuss the issues of animal shelter database systems
offline (free of charge) contact me at jvinson <at> wysard of info <dot> com.
Edit the punctuation and remove the blanks.
--

John W. Vinson [MVP]
From: strive4peace on 3 Jul 2008 15:12
Hi Dave,

"I'm not sure a cat is going to change into a dog (although one of my
cats acts like a dog), nor will anyone confuse a dog with a cat."

LOL! ... our cat is as big as a dog! ... and our large dog 'looks up'
to our cat, who is the king of the house! Our dog is also learning,
from the cat, how to be a mouser <smile>

No, the species would not change -- but there may be a data entry error
-- and that is one reason I usually use artificial instead of natural keys

"You would have a D00001 and a C00001. You say you could calculate
the species. How would you propose to do that?"

if you have, for instance:

Species
- SpeciesID, autonumber
- Species, text

Animals
- AnimalID, autonumber
- SpeciesID, text
- AnimalNumber, long integer

and SpeciesID is a combobox on the form with the following properties:

RowSource:
SELECT SpeciesID, Species
FROM Species
ORDER BY Species

Columncount --> 2
ColumnWidths --> 0;1.5
ListWidth --> 1.7

then, to calculate AnimalNumber, you could do this:

'~~~~~~~~~~~~~~~
dim strSQL as string _
mAnimalNumber as long

dim db as dao.database _
, r as dao.recordset

if isNull(me.SpeciesID) then exit sub

strSQL = "SELECT S.SpeciesID " _
& ", Max(A.AnimalNumber) as MaxNum " _
& " FROM Species as S INNER JOIN Animals as A " _
& " ON A.SpeciesID = S.SpeciesID" _
& " WHERE A.SpeciesID = " & me.SpeciesID

set db = currentdb
set r = db.openrecordset(strSQL, dbOpenSnapShot)

if r.eof then
mAnimalNumber = 1
else
mAnimalNumber = r!MaxNum +1
end if

me.AnimalNumber = mAnimalNumber

r.close
set r = nothing
set db = nothing
'~~~~~~~~~~~~~~~~

in addition to running when a new record is made, this code would also
run on the AfterUpdate event of SpeciesID

anytime the "human identifier" is required, you could join the Species
table to get the letter

.... but since it is only one character, I would make the human
identifier a text field and store the combined value

Animals
- AnimalID, autonumber
- SpeciesID, text
- AnimalCode, text, 7
(assuming you won't have more than 100,000 animals in a species; if you
will, you can change the length)

then, to calculate AnimalCode, you could do this:

'~~~~~~~~~~~~~~~

dim mAnimalCode as text _
mNum as long

if isNull(me.SpeciesID) then exit sub

mNum = nz(dMax("cLng(right(AnimalCode,6))" _
,"Animals","SpeciesID" = me.SpeciesID),0) + 1

me.AnimalCode = left(me.SpeciesID.column(1),1) _
& format(mAnimalNumber, "000000")

'~~~~~~~~~~~~~~~~



Warm Regards,
Crystal

*
(: have an awesome day :)
*




Klatuu wrote:
> Hi, Crystal,
>
> I agree about the primary key. I did not address that in my previous post,
> but it is a good point.
>
> I'm not sure I agreee about not storing the species identifier. I'm not
> sure a cat is going to change into a dog (although one of my cats acts like a
> dog), nor will anyone confuse a dog with a cat.
>
> The numbering technique I suggested uses different numbering for each
> species. You would have a D00001 and a C00001. You say you could calculate
> the species. How would you propose to do that?
>
First  |  Prev  | 
Pages: 1 2
Prev: How to user Dataset created by Visual Studio 2008
Next: Little stevie is soliciting again.