Re: Kennel Reservation Relationship set up

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

In your post you said you needed to keep up with the "Kennels" during the
stay. I assumed each dog could be put in more than one kennel during a
stay. If all dogs spend their time in one kennel then you do not need to
have the KennelAssignmentDuringStay table, that information then can just go
into the stay table as a foreign key.

At least for a beginner you need to keep one form to one table in your
design. After you get that working you can advance to things like subforms
etc.

You need to have a form for each of the following:

Dog breeds (formDogBreeds)
Reasons for coming (formReasons)
Kennels (formKennels)
and

Finally

Stays (formStays)

Make sure you have some data in
Dog Breeds (use the form above to enter the data to see how it works)
e.g. 1 Cocker
2 Welsh Retriever
3 Irish Sitter
4 Jack Russel
etc.

Reasons for Coming (see above)
1 boarding
2 gun training
3 other
etc.
Kennels (see above)
1 Outside South Number 15
2 Inside Air Conditioned 12
etc.
Now you are ready to build your "lookup" queries
build a query: lkpBreed Select * from dogbreeds orderby dogbreed (use
the query designer and click on the fields, then set the sort criteria)
when you run this you should see:
1 Cocker
3 Irish Sitter
4 Jack Russel
2 Welsh Retriever
do the same for lkpReasonsForComing and lkpKennels (based on their
tables of course)

The purpose of these lookup queries is to make the display order independent
of the order entered.

Once you have something to use as a lookup then you can build your Stay Form
Open the Wizard
Base the form on Stays
Use Columnar (one record per form)
finish.
This gives you a 'rough form' to work with.
Select the Dog Breed field and right click it and change to a lookup field
Set the lookup data to lkpDogBreeds (set the number of columns to 2,
under format tab set the column widths to 0;2 (this hides the number and
shows the text)
Once you 'get the concept' of using lookup tables you can expand
to multiple columns etc.
Do the same for the ReasonForStay and KennelAssigned fields.

Save the form. then open it in normal view and now you should be on your
way to being able to enter data. You should be able to "pick" a breed,
"pick" a Reason for stay, "Pick" a Kennel , type in a StartDate and EndDate
without errors.

Of course all of this assumes your "Primary Key" for each table is set to an
AutoIncrement Number.

Ed Warren.


"HLarkin" <HLarkin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4F2DCBC3-336D-4A97-A43D-811EBF0B8F2E@xxxxxxxxxxxxxxxx
> Ed,
>
> First Thank you for your help. I'm sure I got the relationships down
> about
> what you said. The Kennels Table Key (KennelID) & Kennel assignment
> during
> the stay table Key (KennelID) shows a 1 to 1 relationship. Is this
> correct?
>
> I guess I am having problems with the form part or gathering information.
>
> I use the wizzard to create a quick form. I choose the following info:
>
> Doggy Name (from dogs table)
> Reason Text (from reasons for coming table)
> Datestart (from stays table)
> Dateend (from Stays table)
>
> It then asks me how I want to view the data: by doggy, reasons, or stays.
>
> No matter which way I choose I get some type of error. For Instance: If I
> choose the Doggy view, I get 2 forms (Doggy & an sub table)
>
> I type the dogs name in, then when I try to type the stay it gives me an
> error that I can't update field. I also can't type in a date, it just
> beeps
> at me.
>
> Do I need to set up a querie with another type of relationship?
>
> Any help is much appreciated... You have no idea on how many days I have
> been working on this.
>
> Thank you again!!!!!
>
>
>
> "Ed Warren" wrote:
>
>> Hmmm, to start you don't have the right structure for table 2: It should
>> not have 'data elements' as columns but as rows.
>> StayReasonID
>> StayReason
>>
>> then when filled with data it would look like
>> StayReasonID StayReason
>> 1 boarding
>> 2 Gun dog training
>> 3 Personal
>>
>> This allows you to add additional rows as they are needed without having
>> to
>> restructure the data tables.
>>
>> Try thinking of all this in terms of a 'story'
>>
>> We have a bunch of dogs that we take care of. We need to know why they
>> are
>> coming, when they are coming, how long they are going to stay and where
>> we
>> are going to put them during thier stay.
>>
>> So:
>>
>> We have:
>>
>> Dogs
>> (Key) DoggyID
>> Doggy Name
>> Doggy Breed
>>
>> Reasons for coming
>> (Key) ReasonID
>> ReasonText
>> Stays
>> (Key) StayID
>> (fk) DoggyID
>> (fk) ReasonID
>> DateStart
>> DateEnd
>> SpecialRequirements
>> Kennels
>> (Key) KennelID
>> KennelText
>>
>> Kennel assignment during the a stay.
>> (Key) KennelID
>> (fk) StayID
>> DateInKennel
>> DateOutKennel
>>
>> Relationships:
>> Each dog may have one or many stays (Dogs (1) --> (Many) Stays
>> linked
>> on DoggyID
>> Each stay may have one and only one Reason, but each reason may be
>> linked to many stays (ReasonsForcoming (1) --> (M) Stays) linked on
>> ReasonID
>> Each Stay may have one or many Kennel Assignments Stay(1) --> (M)
>> Kennel Assignments
>> Each Kennel Assignment may have one and only one Kennel but each
>> Kennel
>> my have one to many Kennel assignments
>> (kennels(1) --> (m) Kennel Assignments
>>
>> Perhaps additional tables will be needed (examples)
>>
>> Doggy Breeds
>> BreedID
>> BreedText
>> BreedSpecialCareItems
>>
>> Owners
>> Owner ID
>> OwnerLastName
>> OwnerFirstName
>> OwnerAddress
>>
>> EmergencyContactInformation
>>
>> The above should be enough to get started, but as you can see, there WILL
>> be
>> changes as you let this grow. The best of luck
>>
>> Ed Warren.
>>
>> "HLarkin" <HLarkin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:2B445C83-7245-464F-AFC3-761C5A18E917@xxxxxxxxxxxxxxxx
>> > OK, I'm new at access! I want to set up a way to keep track of dogs
>> > entering
>> > in our training program & leaving the training program.
>> > The tables I have set up are as follows
>> >
>> > Table 1) DogID, DogName
>> > Table 2) StayID StayType -why the dogs are here. boarding, training,
>> > personal, etc (Field1 boarding, Field2 Gun Dog training, Field3
>> > Personal)
>> > Table 3) ReservationID, Date In (when the dog is planning on entering),
>> > DogID
>> > Table 4) ReservationDetailID, ReservationID, StayID
>> > Table 5) KennelID, KennelName (name of each kennel the dog will stay in
>> > while here)
>> >
>> > I have a one-many relationships (with integrity inforced)
>> > from DogID 1 to dogID 3
>> > StayID 2 to stayID 4
>> >
>> > Table 5 I haven't even incorportated yet because I can't get the others
>> > to
>> > work.
>> >
>> > I am trying to create a form so I can add when a dog is going to
>> > "reserve
>> > or
>> > check into a kennel" and why they are going to stay.
>> >
>> > Every scenerio I try I get errors that I am changing the primary key,
>> > etc.
>> >
>> > I am stuck. I can type the dog's name, but not the date. Or if I type
>> > the
>> > reason they are here it changes the field data.
>> >
>> > Any hints. My therory is not becoming reality!!!
>> >
>> >
>> >
>> >
>> > ReservationID
>> >
>>
>>
>>


.

Relevant Pages

  • Re: Kennel Reservation Relationship set up
    ... If all dogs spend their time in one kennel then you do not need to ... Reasons for coming ... Dog Breeds ...
    (microsoft.public.access.gettingstarted)
  • Re: police horse/loose dogs
    ... After we moved to SC, we had to go back to Cincinnati for the weekend, ... I took him to the kennel where I board. ... They take a personal interest in the dogs -- and the long time boarders all get special attention. ... It is difficult to make our material condition better by the best law, but it is easy enough to ruin it by bad laws. ...
    (rec.equestrian)
  • North Carolina Dog Owners Alert
    ... North Carolina Dog Owners Alert ... a much wider net over many other kennel owners. ... The American Sporting Dog Alliance urges all North Carolina dog owners to ... that raise dogs for hunting, sporting, herding, show or working. ...
    (rec.hunting)
  • Re: Advice sought on rescuing a failing greyhound rescue organization
    ... Since a group that intends to kennel dogs needs reliable help to ... Not only are we faced with a total collapse of the local organization, ... I run a greyhound rescue group myself, and would like to know where on ...
    (rec.pets.dogs.rescue)
  • Re: Kennel Reservation Relationship set up
    ... I noticed several ways to create a query. ... Using the Lookup Wizard on the Dog Table (under data type, ... Do I set up a query for each item (reason, kennel) if I create a query using ... > Reasons for coming ...
    (microsoft.public.access.gettingstarted)