Calculate Number of Animals & Runs

[Kennellady]

I own a kennel and was wondering if there is a way to put in 2 different dates and get totals of animals during that time and totals of kennel runs?
Thanks.

Sorry, my reservation table includes Kind, New (y/n), Owner's Name, Pet's Name, Date In, Date out, Price, # of Units needed, and # of dogs. I need to put in dates ie: 5-10-06 to 5-25-06 and get a total of dogs and a total of units so we don't overbook. When I made this program I have little idea of how to do it and now years later I'm trying to tweak it. Thanks again

 

[pbaldy]

Probably, but with no idea of your table structure, nobody will be able to give a more specific answer than that.

 

[Kennellady]

Table info

I guess it's really obvious that I'm an Access and a forum newbie.

My input form asks for:
Kind
New (Y/N)
Owner's Name
Pet's Name
Date In
Date Out
Cost per animal
# of dogs
# of units

From this I'd like to be able to use a query to put in dates and get total dogs and units.

ie: enter 5-5-06 to 5-20-06 and get totals of the # of pets coming in and the totals on the # of units in use during those days. Hope this is what is needed. Thanks again

 

[pbaldy]

Can I assume that you would need the sum for each day? I'm guessing that you're comparing to how many dogs you can handle at one time, so the sum of dogs for a 2 week period would not be relevant, rather how many are scheduled each day? If you could upload a sample of your data and what you want the output to look like, I'm sure we can come up with something. It's a lot easier with sample data to play with.

 

[Kennellady]

That's what I need a daily count. We have 88 runs and don't want to overbook and hash marks are less then perfect. All I need is to pull it up on the screen, don't need to print it out. I'd like a date for each day and the dog and runs totals. Thanks a million. JoAnn

 

[Kennellady]

Paul, I'm not too sure how to upload a sample. Sorry, I'm not too computer savy. JoAnn

 

[pbaldy]

Sorry JoAnn, I missed your last responses. To upload a sample, compact it and then zip it. When you're in the regular reply section here (not the quick reply), you should have a button down under "Additional Options" that says "Manage Attachments". Browse to your file, then hit "Upload".

 

[Kennellady]

Kennel database zio

Hope I did this right. JoAnn

 

[Kennellady]

Again

Doesn't look like the file went.

 

[Kennellady]

Trying again

Hope this file will go this time.

 

[Kennellady]

Again

Maybe this time will be the winner. JoAnn

Paul, I followed your directions, but the file doesn't seem to be here. Sorry

 

[pbaldy]

Is the zip file under the 393k limit noted on the "Manage Attachments" page?

 

[Kennellady]

No it's 411. I compacted it, forgot to do that and will upload again. Thanks JoAnn

 

[pbaldy]

Let me know if this result is in the format you're looking for, and if the numbers are correct. I checked 1 and it looked correct to me.

 

[Kennellady]

That's exactly what I'm looking for. To think I met my husband in '83 in computer programing school, but the languages we learned are nothing like todays. Did the kennel database with Access 97 and for the life of me can't figure out how to do things to upgrade it. There's a couple of things I want to do before we retire in the next few years, and it will probably take me that long to do it. Duh, JoAnn

 

[pbaldy]

Okay then, I did 2 things. First I created a table, "tblDates" with one field, "DateField". At this point, that table has 4 records, May 1st through 4th, which is why the result I showed you has those 4 dates. Then I created a query:

SELECT tblDates.DateField, Sum([#4].UNITS) AS SumOfUNITS, Sum([#4].PETS) AS SumOfPETS
FROM tblDates LEFT JOIN [#4] ON tblDates.DateField >= [#4].DATEIN AND tblDates.DateField <= [#4].DATEOUT
GROUP BY tblDates.DateField;

There may be a better way, but I used tblDates as a temp table to force the query to give me one record for each of the dates in the selected range. What I would do is create a VBA process where I entered my dates on a form and then clicked a button to run the query/report. The process would empty tblDates, then create a record for each date in the newly selected range, then open the query/report. It would be a fairly simple loop combined with an insert to the table. Let me know if you need help with that.

By the way, the "#" in the table name isn't a good idea, nor are the spaces and slashes in other object names.

 

[Kennellady]

Paul, you are an angel, will work on this tonight. One thing, I need to differenciate between # of cats and units and # of dogs and units. I not only was making this database like a blind man, I misspelled owener in one place and every time I tried to fix it I screwed things up, so I figured if it almost did what I wanted then that was fine. Again thank you, JoAnn

 

[pbaldy]

You should be able to add the "Kind" field to both the SELECT clause and the GROUP BY clause. Just worked for me.

 

[raskew]

JoAnn -

Paul's solution is absolutely elegant. I'd never encountered the logic he used to return totals by day.

Your database is due for a major overhaul. As you may or may not be aware, there's nothing 'relational' about the design. It's totally denormalized (e.g. 63 instances of the same customer being listed more than once (using different customerIDs, etc.)

What really concerns me that you've provided real people's names, addresses, phone numbers and--in some case--Social Security numbers. Were I to find my info there as a result of having patronized your business, you could bank on an attorney on your doorstep come Monday morning. Might want to think about deleting the attachment and instead provide a 'sample' with ficticious info.

Having said that, it's a real good problem and hats-off to Paul for his solution.

Best wishes,

Bob

 

[pbaldy]

Bob, you're too kind, but thanks!

JoAnn, I agree with Bob's comments, particularly with the SS# bit. I would delete the attachment.