Database for a Cattery

[Wendy Brown]

I'm sure this is an easy answer, but just can't work it out; please help.

I am designing a cattery database for my GCSE coursework. I have the
following tables:

*Cats*
CatID
CatName
Breed

*Pens*
PenID
PenName
Type

*Joining Table*
PenID
CatID
DateIn
DateOut

I want to link these 2 together, so that Tiddles is staying in pen 2 for the
1st week of January, and Maxie is staying in pen 1 for the last week in
Augest, for example. But then I want to run a query to see which pens are
free on a particular date. I can't seem to work out what relationships I
need to create. Do I create a comp[ound primary key with both the in and
out dates? Or do I need a new field of a tick box that the pen is free on a
certain date? If so, how do I do it? I know how to create a relationship
with one field, but the uniqueness is the in and out date.

Please help - I can give you more information if required.

Wendy

 

[John Vinson]

But then I want to run a query to see which pens are
free on a particular date. I can't seem to work out what relationships I
need to create. Do I create a comp[ound primary key with both the in and
out dates? Or do I need a new field of a tick box that the pen is free on a
certain date?

That wouldn't be an appropriate PK - two different pens could have
identical in and out dates. I'd suggest creating a three-field PK on
PenID, InDate and OutDate. This won't prevent entering overlapping
dates, however - to do so you'll need VBA code in the Form event
updating the table.

To find a free pen is pretty easy:

SELECT PenID, <other fields>
FROM Pens
WHERE NOT EXISTS
(SELECT PenID FROM JoiningTable
WHERE [Enter desired date:] BETWEEN JoiningTable.StartDate AND
JoiningTable.EndDate);

 

[Guest]

Thanks John. I kinda see what your'e saying, but I just
can get it to work in ractice. Could I please send my
access file to you to look at? It is only small.

Many thanks,

Wendy

-----Original Message-----

But then I want to run a query to see which pens are
free on a particular date. I can't seem to work out what relationships I
need to create. Do I create a comp[ound primary key with both the in and
out dates? Or do I need a new field of a tick box that the pen is free on a
certain date?

That wouldn't be an appropriate PK - two different pens could have
identical in and out dates. I'd suggest creating a three- field PK on
PenID, InDate and OutDate. This won't prevent entering overlapping
dates, however - to do so you'll need VBA code in the Form event
updating the table.

To find a free pen is pretty easy:

SELECT PenID, <other fields>
FROM Pens
WHERE NOT EXISTS
(SELECT PenID FROM JoiningTable
WHERE [Enter desired date:] BETWEEN JoiningTable.StartDate AND
JoiningTable.EndDate);




.

 

[Guest]

Thanks John. I kinda see what your'e saying, but I just
can get it to work in ractice. Could I please send my
access file to you to look at? It is only small.

Many thanks,

Wendy

-----Original Message-----

But then I want to run a query to see which pens are
free on a particular date. I can't seem to work out what relationships I
need to create. Do I create a comp[ound primary key with both the in and
out dates? Or do I need a new field of a tick box that the pen is free on a
certain date?

That wouldn't be an appropriate PK - two different pens could have
identical in and out dates. I'd suggest creating a three- field PK on
PenID, InDate and OutDate. This won't prevent entering overlapping
dates, however - to do so you'll need VBA code in the Form event
updating the table.

To find a free pen is pretty easy:

SELECT PenID, <other fields>
FROM Pens
WHERE NOT EXISTS
(SELECT PenID FROM JoiningTable
WHERE [Enter desired date:] BETWEEN JoiningTable.StartDate AND
JoiningTable.EndDate);




.

 

[Guest]

Thanks John. I kinda see what your'e saying, but I just
can get it to work in ractice. Could I please send my
access file to you to look at? It is only small.

Many thanks,

Wendy

-----Original Message-----

But then I want to run a query to see which pens are
free on a particular date. I can't seem to work out what relationships I
need to create. Do I create a comp[ound primary key with both the in and
out dates? Or do I need a new field of a tick box that the pen is free on a
certain date?

That wouldn't be an appropriate PK - two different pens could have
identical in and out dates. I'd suggest creating a three- field PK on
PenID, InDate and OutDate. This won't prevent entering overlapping
dates, however - to do so you'll need VBA code in the Form event
updating the table.

To find a free pen is pretty easy:

SELECT PenID, <other fields>
FROM Pens
WHERE NOT EXISTS
(SELECT PenID FROM JoiningTable
WHERE [Enter desired date:] BETWEEN JoiningTable.StartDate AND
JoiningTable.EndDate);




.

 

[John Vinson]

Thanks John. I kinda see what your'e saying, but I just
can get it to work in ractice. Could I please send my
access file to you to look at? It is only small.

I'm sorry, Wendy, but I'm a self-employed consultant volunteering my
time on the newsgroups; private EMail support is only available to my
paying customers. If you'ld post the specific problem you're having,
including the relationships between your tables and the SQL of any
query that's not working right, I or one of the other volunteers
should be able to help.