Kennel Reservation Relationship set up

[Guest]

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

 

[Ed Warren]

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.

 

[Guest]

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]

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.

 

[Ed Warren]

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.

 

[Guest]

Ed,

Real quick (hopefully) question. while trying to set up the "lookup" query,
I noticed several ways to create a query. Which way do you recommend with
your explaination below.

1. Using the Lookup Wizard on the Dog Table (under data type, where I can
choose number, text, etc.)
2. Using the Lookup tab in the Table design section (right next to general)
3. Using the query wizzard or query design page.

Do I set up a query for each item (reason, kennel) if I create a query using
the query wizzard or design page.

Again thank you for your help. I was able to make progress, but still
couldn't get the bugs out, but I think I am trying to many way & methods at
one time.

Again thank you.

 

[Ed Warren]

Either 2 or 3 --- You don't want to have the table structure tied to a
lookup field. Using either the query wizard (query-new ... etc) or using the
query builder in the lookup component are exactly the same thing. Anymore
I always build and save the queries using the query wizard and save the
query as a lkpxxxx query, that way it is available for use the next place I
need to have the same lookup list.

Keep on truckin, you're making progress.

Ed Warren

 

[Guest]

Ed,

IT WORKED!!!!!!

Thank you very much. You don't know how much I appreciate your help!!!!
Take Care,
Helena