Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

A complex calculation in vba

Status
Not open for further replies.

gwag17

Programmer
Jul 28, 2004
12
0
0
GB
The problem I have been asked to overcome is the following:
A person can loan a book for any 12 months in a 36-month period, the loan does not have to be a continuous loan, and it can consist of various smaller loans e.g. 4 loans of 3 months.
I need to be able to calculate how long each person has had each book over this period.
As each day passes 1 is added to the length of the current loan.
If the person had the book at this point 36months ago 1 must be added to the current loan and 1 taken off the previous loan total of the book.
If the person did not have the book at this point 36months ago then 1 must be added to the current loan and 0 taken off the loan total.
If the person is 1 month off the 12 month limit I need to set a warning flag of some kind so we can send out a reminder.
 
Hi,

Have you developed any kind if logic? Please post your code.

Skip,
[sub]
[glasses] [red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [tongue][/sub]
 
Thank you for the reply.

The only code I have so far is to work out this date 36 months ago.

DateAdd("m",-36,date())

I then need to find out if the person had the book on loan that date, which I am having trouble with.

 
Havn't you designed some logic, maybe with some pseudo-code?

Skip,
[sub]
[glasses] [red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [tongue][/sub]
 
I have started to create some more code and here it is:

Private Sub cmdTotalDone_Click()

Dim loan1 As Integer
Dim loan2 As Integer
Dim loan3 As Integer
Dim loan4 As Integer
Dim loan5 As Integer
Dim loan6 As Integer
Dim totalloan As Integer


Dim dateout As Date
Dim dateback As Date

txtloanDate1.SetFocus
dateout = txtloanDate1.Text
txtreturned1.SetFocus
dateback = txtreturned1.Text


loan1 = DateDiff("m", dateout, dateback, vbMonday)

txtloanDate2.SetFocus
dateout = txtloanDate2.Text
txtreturned2.SetFocus
dateback = txtreturned2.Text


loan2 = DateDiff("m", dateout, dateback, vbMonday)

txtloanDate3.SetFocus
dateout = txtloanDate3.Text
txtreturned3.SetFocus
dateback = txtreturned3.Text


loan3 = DateDiff("m", dateout, dateback, vbMonday)

txtloandate4.SetFocus
dateout = txtloandate4.Text
txtreturned4.SetFocus
dateback = txtreturned4.Text


loan4 = DateDiff("m", dateout, dateback, vbMonday)

txtloanDate5.SetFocus
dateout = txtloanDate5.Text
txtreturned5.SetFocus
dateback = txtreturned5.Text


loan5 = DateDiff("m", dateout, dateback, vbMonday)

txtloanDate6.SetFocus
dateout = txtloanDate6.Text
txtreturned6.SetFocus
dateback = txtreturned6.Text

loan6 = DateDiff("m", dateout, dateback, vbMonday)

totalloan = loan1 + loan2 + loan3 + loan4 + loan5 + loan6

txttest1.SetFocus
txttest1.Text = totalloan

If totalloan >= 11 Then
chkExtensionRequired.SetFocus
chkExtensionRequired.Value = True
MsgBox "This person needs to apply for an extension", vbOKOnly = vbOK
End If

End Sub


I think it is a bit long winded this way, but it is the only way I can think of at the minute.
What i am looking at now is because a person is not always going to have more than 1 loan, I need to alter the code so that if there is no loan data then ignore it in the calculation. Also if the person has currently got the book on loan, there will be no returned date, so I need to use the system date as the marker for that.
 
Isn't your data in a table?

Why 6 loans?

All you have here is summing 6 DATE DIFFERRENCES and calling it totalloan????

What you posted is NOT logic, in fact it is ILLOGICAL!

Is this a homework assignment?

Skip,
[sub]
[glasses] [red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [tongue][/sub]
 
First of all, thank you for your help, but I am not a student of anything but life, I finished college about 3 years ago and I am just starting in the field of programming databases, hence the reason for it not being that logical.

The data is in a table.
It is in 1 table, but I am not sure whether it needs to be in more than that.
I will be getting the source data to populate all fields from another table, this will be updated on a daily basis. Using an update query.
Two fields in the table will be populated via coding, which are the total length of the loan, and if an extension needs to be applied for.
It is 6 loans because that is the way it must be.
The summing of the 6 loans is to give me a total length of the loan by person.
I need to be able to assertain whether a previous loan is older than 36months ago, but it would not be able to be based on just the loandate, it would have to be between the loan and return date as well.
 
What is the structure of your table then? Cummon, lets get all the pertinent information.

What the source data?

What is the logic that you have so far designed? As a programmer of 3 years, surely you have done such a design, yes?

Is each person making loans of books? To whom are they loaning books?

OR is each person [/b]BORROWING[/b] books from a library? Is there a limit of 6 books on loan to any one borrower?

Skip,
[sub]
[glasses] [red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [tongue][/sub]
 
skip, the guy is obviously stuck and all you can do is fire questions at him rahter than try to figure it out, your being about as helpful as a piece of poo,

gwag, will email you what i figure out!
 
FoleyQuaz ,

This is not a forum that THINKS for people.

I am trying to get him to disclose where he is, but I am NOT going to design his project for him.

Skip,
[sub]
[glasses] [red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [tongue][/sub]
 
Like I said, I left college 3 years ago, I didnt say I have been a programmer for all that time. I have just started programming in the last couple of months.
You are correct each person borrows a book and can only borrow it 6 times.

This is what I must calculate:

1. The date exactly 36 months ago.
2. The length of previous borrows.
3. The length of the current borrow.
4. The sum of 1 & 2.
5. 3 will give me the total length of loan, which is the code I showed earlier, I need to alter it because at the minute it calculates from the borrow date to the return date, but if the borrow date is more than 36 months ago it needs to be calculated from todays date 36 months ago, hence 1.
6.I then need a flag of some description to be the warning if it is approaching the 12 month period. I am using a check box which is set to true if 4 is >=11.

Unfortunately this is not my design or idea and the person that designed it is off and hasnt written a lot down, so I am trying to figure this out bit by bit.

The structure of the database is as follows:

Table: Books.
ISBN as PK
Title
author
Publishdate
genre

Table:Customers.
CustID as PK
surname
forename
address

Table:Loans
LoanID as PK
BorrowDate1
ReturnedDate1
BorrowDate2
ReturnedDate2
BorrowDate3
ReturnedDate3
BorrowDate4
ReturnedDate4
BorrowDate5
ReturnedDate5
BorrowedDate6
BorrowDate6
TotalLengthofLoan
ExtenstionRequired
ISBN as FK
CustID as FK

Each book can be borrowed 6 times for a max length of 12 months in any 36 month period.

Is this any better?

 
Skip,

Thanks for the help, writing it down in this way has helped me see it a little more clearly now, but I am still unsure as to the design of the loans table. I think that is the design flaw that is stopping me.

Alan
 
each person borrows a book and can only borrow it 6 times."

So at any point in time, a person can borrow, lets say, 10 book, but any one of those books cannot have been borrowed by that person more than 6 times?


Skip,
[sub]
[glasses] [red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [tongue][/sub]
 
Yes, but each book cannot be borrowed for more than 12 months in any 36 month period either.
 
In your opinion would it be better to have a seperate table for each borrow period?
Currently all borrows are held in 1 table, would it be better set out as follows:

Borrow table1
CustID AS FK
ISBN as FK
DateOut as PK
DateIn
Length

Have 6 of those tables and have them link into another table called Total Loans

CustID as FK
ISBN as FK
TotalLength as PK comprised from a total of the other tables
ExtensionRequired as yes/no. triggered if totallength >=11 as this gives 1 month to apply for extension.

Again thanks for your help

 
I would suggest that you familiarize yourself with database design principles
Fundamentals Of Relational Database Design

In light of that, I would use a different table structure.

Table: Books.
ISBN as PK
Title
author
Publishdate
genre

Table:Customers.
CustID as PK
surname
forename
address

Table:Loans
LoanID as PK
BorrowDate
ReturnedDate
ExtenstionRequired
ISBN as FK
CustID as FK



Skip,
[sub]
[glasses] [red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [tongue][/sub]
 
Thank you for the help and advice.
I shall now see what I can do.
Have a good 1.

Alan
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top