neolithicau
08-11-2007, 09:08 PM
At the moment I'm in the process of making an Access database for my Dads new business venture. It's actually coming along really well in my opinion since I'm no expert (I'm drawing on knowledge from Year 12 which I did several years ago). At the moment it all works fine, customer details, asset management and various other reports related to that information.
Right now however, I'm having trouble with the Invoicing. Well, it actually works fine and adds everything up perfectly and generates a report with all of the appropriate information. It was all going fine until they detailed their pricing model to me.
I need to make Access filter out 6 days of the week (they don't charge for Sundays), then I need to make it work with a 3/4 day pricing model. For example, their minimum Hire is 3 days regardless of whether they only actually have it for 1 day or not.
Basically (well, the way it was described to me) the way it needs to be worked out is this:
For every 7 consecutive days:
3 days or less is a 3 day charge -> 4-7 days is a 4 day charge -> plus 1 day at a time until (back to the start).
Now, they have a daily price which means I can use my current system of getting a Date Difference (day counting between 2 dates) and multiplying it by the price specified. Right now my forumla is this (as part of a query):
DDiff: (DateDiff("d",[HireForm]![OnHire],[HireForm]![OffHire]))
PTotal: ([Price]*[DDiff])
Which works great for charging all 7 days of a week.
So basically I think all I need to work out how to do is:
A) Work out 6 days of the week - Taking away Sunday from the equation
B) Get it to work out a 3-day charge for a period that is actually less than 3 days (however it is possible to get them to work around this by just entering a 3 day period where the dates of On and Off hire are specified, but that's the dirty way of doing it)
C) Do a 4 day charge for a 7 day period
Here is a picture of the easiest way I could find to explain what they want. Don't ask me why they like losing money here and there on days they won't charge for:
http://users.on.net/~neolithic/SA/StupidDatabase.jpg
Additionally I've told them that if this isn't possible/too complicated then I will just remove the pricing function from the database and they'll have to work it out manually (which they didn't seem to object to if nessecary).
Any help with this would be greatly appreciated :).
Right now however, I'm having trouble with the Invoicing. Well, it actually works fine and adds everything up perfectly and generates a report with all of the appropriate information. It was all going fine until they detailed their pricing model to me.
I need to make Access filter out 6 days of the week (they don't charge for Sundays), then I need to make it work with a 3/4 day pricing model. For example, their minimum Hire is 3 days regardless of whether they only actually have it for 1 day or not.
Basically (well, the way it was described to me) the way it needs to be worked out is this:
For every 7 consecutive days:
3 days or less is a 3 day charge -> 4-7 days is a 4 day charge -> plus 1 day at a time until (back to the start).
Now, they have a daily price which means I can use my current system of getting a Date Difference (day counting between 2 dates) and multiplying it by the price specified. Right now my forumla is this (as part of a query):
DDiff: (DateDiff("d",[HireForm]![OnHire],[HireForm]![OffHire]))
PTotal: ([Price]*[DDiff])
Which works great for charging all 7 days of a week.
So basically I think all I need to work out how to do is:
A) Work out 6 days of the week - Taking away Sunday from the equation
B) Get it to work out a 3-day charge for a period that is actually less than 3 days (however it is possible to get them to work around this by just entering a 3 day period where the dates of On and Off hire are specified, but that's the dirty way of doing it)
C) Do a 4 day charge for a 7 day period
Here is a picture of the easiest way I could find to explain what they want. Don't ask me why they like losing money here and there on days they won't charge for:
http://users.on.net/~neolithic/SA/StupidDatabase.jpg
Additionally I've told them that if this isn't possible/too complicated then I will just remove the pricing function from the database and they'll have to work it out manually (which they didn't seem to object to if nessecary).
Any help with this would be greatly appreciated :).