Tools Rental DB Help

[abusaif]

Hi to all!



I am working on a DB for rental Items. I have a tblMasterItem, tblIssuedItem + tblIssueDetails, tblReceivedItem + tblReceiveDetails. Everything is working fine, but I am stucked at a point that, Suppose I have issued 10 ladders on 1st of month to customer1 and again I issued 5 ladders on 10th of month to the same customer, he returned me back 4 ladders on 20th of Month. (Seriously I don't know whether I explained properly or not).
Now at the end of the month I want to bill him for the rent of ladders. first I want query to calculate upto end of the month for ladders which are still on rent. Secondly I want to calculate for the ladders which were Issued and returned back.

Can anyone help?

[rpeare]

Are your rentals calculated by the day, the week, or the month? I'm assuming by the day going by your description but if you have weekly/monthly rates as well that would change how to approach this question.

[Dal Jeanis]

I'm going to make the following simplifying assumptions.

(1) the number of ladders billable for a day is the net number that are "issued" at the end of the day.

(2) "Net number issued" is the number of ladders issued to a client prior to and including that date, minus the number of ladders received from that client prior to and including that date.

(3) Negative "net number issued" is an obvious mistake and should not result in a negative billing day.

(4) ladders are indistinguishable from each other, and if two different groups of ladders are issued and some number of ladders are returned, no attempt need be made to determine which ladders were returned.

Also, for purpose of the discussion, I'm going to stick to one client and "Ladder", and show you how to build up the information. This can be done in a lot fewer queries, but I want you to follow how the overall buildup of information is structured, rather than just handing you a mondo query that you have no hope of following.

The first two queries each flatten the information from the issued table and the received table in a way they can be unioned together in a meaningful way by date.

Query Q1:
Select tII.ClientID AS ClientID, tII.ItemType AS ItemType, tII.IssueDate AS TransDate, TID.IssueNumber AS IssueNumber, 0 as ReceiveNumber, TID.IssueNumber AS NetNumber
From tblIssuedItem AS tII

    (INNER JOIN tblIssueDetails AS tID ON ... add whatever join conditions are necessary to get the above fields)

' For testing, If you just wanted ladders for one client, then you could have this on the end: 
' WHERE clientID = "TheLadderGuysName"
' AND ItemType = "Ladder";


Query Q2:
Select tRI.ClientID, tRI.ItemType, tRI.ReceiveDate AS TransDate, 0 as IssueNumber, tRD.ReceiveNumber AS ReceiveNumber,  0 - tRD.ReceiveNumber AS NetNumber
From tblReceivedItem AS tRI

    (INNER JOIN tblReceiveDetails AS tRD ON ... add whatever join conditions are necessary to get the above fields)

' For testing, If you just wanted ladders for one client, then you could have this on the end: 
' WHERE tRI.clientID = "TheLadderGuysName"
' AND trI.ItemType = "Ladder";

Note that I'm assuming the ReceiveDate and IssueDate are dates, rather than timestamps. If the hour of checkout or return were also in that field, you'd get rid of that excess data here, to prevent strange results when you are summing and unioning below.

The next query unions those two together by date, giving you the net transaction amount on each date. Note that this query MUST return only zero or one records per date, or it can screw up the algorythm later in some versions of the code.

Query Q3:
SELECT ClientID, ItemType, TransDate, Sum(IssueNumber) AS IssueNumber, SUM(ReceiveNumber) AS ReceiveNumber, SUM(NetNumber) as NetNumber
FROM 
    (
    SELECT ClientID, ItemType, TransDate, IssueNumber, ReceiveNumber, NetNumber
    FROM Q1
    UNION 
    SELECT ClientID, ItemType, TransDate, IssueNumber, ReceiveNumber, NetNumber
    FROM Q2
    )
GROUP BY  ClientID, ItemType, TransDate;

This might give data something like this:
"TheLadderGuysName", "Ladder", 05/24/2013,  12,   0,  12
"TheLadderGuysName", "Ladder", 05/28/2013,   0,   3,  -3
"TheLadderGuysName", "Ladder", 06/04/2013,   3,   0,   3
"TheLadderGuysName", "Ladder", 06/07/2013,   0,  20, -20
"TheLadderGuysName", "Ladder", 06/09/2013,  10,   0,  10
"TheLadderGuysName", "Ladder", 06/11/2013,   0,   2,  -2

Now, things get interesting. We need to calculate the net number out on those days, and multiply by the difference in days. Or, we can create a dummy table for the billing days for that month and calculate the net number out at the end of each billing day, then just add them up. I prefer that approach.

Just for grins, let's get the answer for how many are out at the end of the day on June 7.

SELECT #06/07/2013# AS BillDate, ClientID, ItemType, Sum(IssueNumber) AS IssueBillable, SUM(ReceiveNumber) AS ReceiveBillable, SUM(NetNumber) as NetBillable
FROM Q3
WHERE TransDate <= #06/07/2013#
GROUP BY ClientID, ItemType;
Which returns a record like this:
"TheLadderGuysName", "Ladder", 06/07/2013,  15,  23,   -2
oops - looks like a booboo in the data.  I'll deal with that in the next query.

Let's assume we create a temp table with all the billing days for a month. Let's assume it's called tmpBillDays, has a single field BillDate, and it currently contains 30 records, one each with the dates of June 1 thru June 30. Here's the amount out at the end of each day in the table:

QUERY Q4:
SELECT Q3.ClientID, Q3.ItemType, tBD.BillDate, Sum(Q3.IssueNumber) AS IssueBillable, SUM(Q3.ReceiveNumber) AS ReceiveBillable, IIF(SUM(Q3.NetNumber)>0,SUM(Q3.NetNumber),0)  as NetBillable
FROM tmpBillDays AS tBD, Q3
WHERE Q3.TransDate <= tBD.BillDate
GROUP BY Q3.ClientID, Q3.ItemType, tBD.BillDate;
Which returns records like this:
"TheLadderGuysName", "Ladder", 06/01/2013,  12,   3,   9
"TheLadderGuysName", "Ladder", 06/02/2013,  12,   3,   9
"TheLadderGuysName", "Ladder", 06/03/2013,  12,   3,   9
"TheLadderGuysName", "Ladder", 06/04/2013,  15,   3,  12
"TheLadderGuysName", "Ladder", 06/05/2013,  15,   3,  12
"TheLadderGuysName", "Ladder", 06/06/2013,  15,   3,  12
"TheLadderGuysName", "Ladder", 06/07/2013,  15,  23,   0
"TheLadderGuysName", "Ladder", 06/08/2013,  15,  23,   0
"TheLadderGuysName", "Ladder", 06/09/2013,  25,  23,   2
"TheLadderGuysName", "Ladder", 06/10/2013,  25,  23,   2
"TheLadderGuysName", "Ladder", 06/11/2013,  25,  25,   0

And, Finally, we have the query you were looking for:

QUERY Q5:
SELECT ClientID, ItemType, Sum(NetBillable) As ItemBillDays
GROUP BY ClientID, ItemType;
which, assuming that no other activity occurred in the month, would look like this
"TheLadderGuysName", "Ladder", 67

Hopefully, you were able to follow each step as I outlined it.
1) Create a flat file of each kind of change transaction in a fixed format that has places for all the data you need of any kind, filling with zeros or changing signs as needed.
2) Union those together and sum by date so there is one net transaction per date at this point
3) Create a table of each date to be charged, and join it to all records less than or equal to that date to get the net number of items out at the end of the day.
4) Sum the results of the net number out for each day, giving your total number of item-days for the period in question.

This can be done in a single query, but it's more readable in two - Q1-Q2-Q3 and Q4-Q5.

It can also be done without the tblBillDate, but it's much less readable that way, and it requires some fumbling with the start and end dates. I always go for clarity in design when I have a choice.

[rpeare]

I was looking at dal's post and thinking it was more complex than what I was thinking.

I'm posting a sample database. My database assumes the following:

1. You are able to identify individual rental items by some sort of internal code
2. When you rent items you are recording the customer and the item they receive along with a out date (the day the item is rented) and an in date (the day the item is returned)
3. Each item has a specific rental amount (i.e. a 7 step ladder is rented for the same amount per day to anyone that comes in)

This allows you to know who has what piece of equipment and which items are still 'out' so you can track the item to the specific customer.

abusaif.zip

I have one query that's calculating the 'owed' amount

qryMonthlyCosts

you can conceivably build your invoices on this query without having to take a huge number of queries/sub queries.

[Dal Jeanis]

That's the great thing about identifying and enumerating the assumptions. If the ladders have serial numbers, for instance, and each individual item rented out can be identified coming back in, then that's a much easier thing to code. Something like this would get the number of days out for each line item.

SELECT 
   ...
   (identifying fields for the sub item)
   ...
   DateDiff("D", 
            IIF(IssueDate<FirstOfMonthDate, FirstofMonthDate, IssueDate),
            IIF(NZ(ReceiveDate, LastofMonthDate) < LastofMonthDate, ReceiveDate, LastofMonthDate)
            )  AS BillDaysFROM
       ((the tables and joins needed)

WHERE IssueDate <= LastofMonthDate
AND NZ(ReceiveDate, LastofMonthDate) >= FirstofMonthDate;

[abusaif]

Well thanks alot for you suggestions buth I got what I wished by creating simply another table transaction ID, ItemCode, Issuedate, IssuedQty, ReturnDate, ReturnQty, and Billing Date. Now when I created a query it worked fine by calculating issued Qty - Return Qty it gives Balance Qty and DateDiff in between issue date and return date and also a field with date diff of issue date and billing date. Any How its working fine

But Many many thanks for your kind attention and information. and hope the same in future.

Once again Thanks to both of you ,

[Dal Jeanis]

Glad you got what you needed!

I just wanted to check something...

When you say, "created another table", did you mean that during your reporting you use VBA to create a temporary table using the information in your other tables? That would be a good solution for keeping the process readable.

Or do you mean you've added another permanent table to your database and will be trying to keep them all in sync? That would be a problem for you later.

It's your database, and you're the guy who has to support it, so you should do what makes sense to you. I can tell you from experience that the second one is a minefield.