Help With 3 Queries Please...

[David Heath]

Hi there,

I have a database which holds information about Dogs stored i
kennels.

There are four blocks, and each block has 20 pens (one dog per pen).


I've managed to sort out the relationships and all the tables and I'v
got some nice looking forms together as well, but I need some help no
that its come down to the generating queries and reports thing. I'v
done a couple of queries already (working out the average number o
nights dogs stay, working out walking and feeding schedules etc.) bu
some of the more difficult things I'm having trouble with, so I'
helping someone could lend a small helping hand.

The other three I'd like to achieve are:

Generating Invoices - Basically just shows the Booking table (whic
already has the totals on it) based on the Booking ID which the use
enters when opening the report/query. (Can it do this for a report?
Also how do I get the database to ask the user a question and displa
results depending on that? In this case the Booking ID.

Displaying Unallocated Pens - Basically just a list of the unallocate
pens at the present time. The results would be cool if they coul
display the block name, then the pen number.

Number Of Dogs Boarded (at present and over a user-defined amount o
time!) - I have no idea how I'm going to do this one.


I appreciate this is a lot to ask, but some pointers would be very muc
appreciated. I have also posted a picture below of my relationship
window so you can get an idea of how the database works, and the typ
of data you are dealing with. Changing any relationships at this stag
is a definite no-go. : )

http://www.games-portal.co.uk/misc/relationship.png

Thanks very much for any input

 

[David Heath]

After reading through that it seems a bit abstract. Here are some mor
specific pointers:


Basically for the invoices question I want to user to open the quer
and the system to ask for a Booking ID. The user enters a booking I
and then the details of that booking are displayed (with all the field
showing). Because the table called 'Booking' basically just show
everything that is needed on the invoice anyway, just showing thi
information will be enough.


For the unallocated pens question, I need the system to simply displa
every pen without a dog in them at the present time. I don't know i
this is actually possible because of the way my relationships work, bu
it would be great if it did work. So after opening the query the syste
simply shows a list of numbers (the pen numbers) with the block nam
next to them (one of the four blocks) that have no dogs in them.


Finally, the number of dogs boarded question even I don't have a clu
what I need here. Maybe two queries are needed: One to display all th
dogs boarded at the present time (showing some fields from the pe
table would be enough for each dog that is boarded) and then enothe
query where the user enters a start date, and then and end date. Al
the pets that have stayed between these two dats would then b
displayed. Again, showing some fields from the pet table would b
great.



I am sorry if it's still to abstract, but any progress at all on any o
those would be greatly appreciated.

I do have one final question: If I was to create a report based o
those queries, would the report also prompt the user for input like th
query does? E.g. If the query asked for a booking Id, then I made tha
query into a report, would opening the report also ask the user for
booking ID or would I not be able to do that?


Anyway, thanks very much everyone, I hope that is enough to work from