Hi everyone,

I have a database which holds information about Dogs stored in 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've got some nice looking forms together as well, but I need some help now that its come down to the generating queries and reports thing. I've done a couple of queries already (working out the average number of nights dogs stay, working out walking and feeding schedules etc.) but some of the more difficult things I'm having trouble with, so I'm 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 (which already has the totals on it) based on the Booking ID which the user 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 display results depending on that? In this case the Booking ID.

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

Number Of Dogs Boarded (at present and over a user-defined amount of 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 much appreciated. I have also posted a picture below of my relationships window so you can get an idea of how the database works, and the type of data you are dealing with. Changing any relationships at this stage is a definite no-go. : )

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

Thanks very much for any input.

After reading through that it seems a bit abstract. I'll try and be more specific here:


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


For the unallocated pens question, I need the system to simply display every pen without a dog in them at the present time. I don't know if this is actually possible because of the way my relationships work, but it would be great if it did work. So after opening the query the system simply shows a list of numbers (the pen numbers) with the block name 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 clue what I need here. Maybe two queries are needed: One to display all the dogs boarded at the present time (showing some fields from the pet table would be enough for each dog that is boarded) and then enother query where the user enters a start date, and then and end date. All the pets that have stayed between these two dats would then be displayed. Again, showing some fields from the pet table would be great.



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

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

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

Mmm...

You're saying that you won't change the structure, but yet you want contributors to make all this work for you? It's a bit like saying 'Please bake me a cake, but you can't use sugar and you must use olive oil'.

Your structure's not bad, but it's a bit confused around the pen/block area. Basically each pen is unique so it would have been a better idea to have a pen ID that would have eliminated the double join to tblBooking and tblWalk/Feed. That's going to be a problem sooner or later. There's some redundant fields, too. For instance, since you have start date and end date for your booking, you don't need the number of nights. Don't use special characters in your field names, eg CertificateSeen? You should really stick to numeric table IDs. Vet name is a poor choice for a primary key. It's not that uncommon to have two vets with the same name.

Basically for the invoices question I want to user to open the query and the system to ask for a Booking ID. The user enters a booking ID and then the details of that booking are displayed (with all the fields showing). Because the table called 'Booking' basically just shows everything that is needed on the invoice anyway, just showing this information will be enough. I think you're confused about holding, extracting and displaying data. You hold it in a table, manipulate it in a query and display it in a form or report. If you want a query to prompt for a user input you can add a prompt as a criterion, eg [Enter the Booking ID please]. However, I would have thought that a drop down list would have been a better approach. This would be a combo box on a form.
For the unallocated pens question, I need the system to simply display every pen without a dog in them at the present time. I don't know if this is actually possible because of the way my relationships work, but it would be great if it did work. So after opening the query the system simply shows a list of numbers (the pen numbers) with the block name next to them (one of the four blocks) that have no dogs in them. That's not hard. You need a query that joins tblBooking with tblPen and returns the pen details where the booking is null. I presume you'll want to place criteria on the dates of the bookings.

That's all I have time for now, I'm afraid.