Dog boarding db ,date question

[linp]

I am using access 2007, limited knowledge of db design.
we run a dog boarding facility , in house , no kennels ; i have set up a simple db to start with ;a table with "name owner " "name dog " " arrival date " " departure date ", i can't figure out how to get the dates " filled in " between arrival / departure dates.
i want to be able to see for any given date which dogs are present.

Thank you in advance , lin

 

[Moniker]

You don't need the actual dates between arrival and departure to be stored anywhere. Just use comparisons.

ArrivalDate = #2/20/08#
DepartureDate = #2/28/08#

If #2/25/08# >= ArrivalDate And #2/25/08# <= DepartureDate Then
The mutt is still here
Else
The owners actually showed back up
End If

 

[linp]

Thank you for your reply ,could you point me as to how i could use this code ? do i put it in a macro or make a query with this code ?
Thanks

You don't need the actual dates between arrival and departure to be stored anywhere. Just use comparisons.

ArrivalDate = #2/20/08#
DepartureDate = #2/28/08#

If #2/25/08# >= ArrivalDate And #2/25/08# <= DepartureDate Then
The mutt is still here
Else
The owners actually showed back up
End If

 

[Moniker]

Without getting too technical, just make a parameter query out of it.

SELECT *
FROM YourTableName
WHERE YourTableName.ArrivalDate <= [CheckDate] AND
YourTableName.DepartureDate >= [CheckDate];

That will prompt you for "CheckDate". It will then return the records with an Arrival <= the date you enterd and a departure >= the date you entered.

Replace each instance of YourTableName with the name of the table you created.

 

[gemma-the-husky]

you MAY need to actually have a calendar table, where you can store details of which dog is occupying each kennel on a given day, to facilitate your calcs.

otherwise calculating exactly which kennels are in uuse on a given day might be a bit awkward, and throwing up a monthly tick chart, say would also be hard. It needs a bit of thinking about i would say, and perhaps revisiting after youve tried one solution out.

one issue to consider is that a kennel may have two occupants in a day - the outgoing dog, and the incoming dog. Again, not sure of the best way to handle this.


my husky knows about kennels,

 

[linp]

Thanks for your reply ,actually , we don't use kennels at all , all dogs can roam freely in and out the house and the grounds

Lin

you MAY need to actually have a calendar table, where you can store details of which dog is occupying each kennel on a given day, to facilitate your calcs.

otherwise calculating exactly which kennels are in uuse on a given day might be a bit awkward, and throwing up a monthly tick chart, say would also be hard. It needs a bit of thinking about i would say, and perhaps revisiting after youve tried one solution out.

one issue to consider is that a kennel may have two occupants in a day - the outgoing dog, and the incoming dog. Again, not sure of the best way to handle this.


my husky knows about kennels,

 

[linp]

Great , this is exactly what i want !

Would it be possible to get the dates " filled in " between arrival and departure ,so i will be able to view a table more like a " planning agenda ?like this but simpler , just a table .http://kennellink.com/sites/demo/code/onsiteReport.asp

Thanks for your help

Lin

Without getting too technical, just make a parameter query out of it.

SELECT *
FROM YourTableName
WHERE YourTableName.ArrivalDate <= [CheckDate] AND
YourTableName.DepartureDate >= [CheckDate];

That will prompt you for "CheckDate". It will then return the records with an Arrival <= the date you enterd and a departure >= the date you entered.

Replace each instance of YourTableName with the name of the table you created.