help with complicated formula

[SamT]

Paul,

The Database design is goods enough for now. We will tweak it a bit as the Application design gets a little farther down the road.

It's time to start work on the Reports.

Make a new workbook named TDR Reports Design.v.01.xls

Put each Report on a different Sheet

Make the report look as much like the actual paper form that it is taken from.

If it is not taken from a paper form, make it look like the ones that are.

Do not add any colors except as noted below.

Use Borders (Underline or Box) to show where Values go.

Do not use any formulas.

If the Labels on a Report do not match the Column Label where the value comes from, change the DB Design Column Label to match the Report. Italicize the report label so I know the Column label has changed.

If any value on a Report can be calculated from various DB values, Obviously, there won't be a DB column Label to match the Report Label. Use a Box Border and Light Yellow colors to indicate that the Value is calculated. Use Cell Comments to show which Database Sheets are used in the calculations.

[crasherpaul]

Two Report Examples. The Estimated Pay is IMO, complete. The YTD is very rough

Do try to keep your artistic self in control until we have this App working. The App will keep changing until it does work, so any beuty will need to be redone again and again.

i dont understand what you mean by reports or what you want me to add. do you mean the weekly/fortnightly/monthly or Agency timesheets? or is it something else?

[SamT]

Paul,

A Form is where one enters data.

A Report is where one views data.

So... The reports you need to make are one for each SET of data you want to see at one time.

I already made an Estimated Pay, because I know that when I worked for hire, I always wanted to have an idea how much i had earned so far.

I kinda, sorta, showed a payslip, so one could compare paid hours, etc, to actual worked hours, etc, because paymasters make mistakes. I don't know enough about he UK to make anything resembling what works over there.

I know that you will want a Work Schedule that shows at least the last weeks actual work and the upcoming work planned as far as the boss/dispatcher can tell you, along with any approved holidays. This Report must let the driver know what hours he is restricted to and should let him plan his days off.

There are two species of Reports; What a driver needs to know and what are nice to know. Since your time to complete this App is growing short, concentrate on what he needs to know. It won't be a problem to add any more Reports later. In fact it will be quite easy and you probably won't even need my help after you've seen how to do it once.

The Reports finalize the Databases and the Databases finalize the Forms. Even after the App is put into production it is easy to add to if it is properly designed.

You've told me that a driver needs to know how many 9s and 11's he has left in this period, so that when dispatch tells him to take a load of coal to Newcastle, he can say, "hey, that's an 11 hour trip, and I've used mine up." That is a "need to know" report. The "Estimated Pay" Report is a "nice to know" Report. I only made it because it's the only Report I CAN make for you. I just don't know anything about driving in the UK. Well, I do know that you drive on the wrong side of the road.

Keep the Report simple and to the point. A driver doesn't need to know how many 9s and 11s he worked last week, he does need to know if he can work one today. He doesn't need to know how many holidays he has taken, he needs to know that he has one tomorrow.

Paul, consider your official drivers log. When you need information from it for some reason, what information is it that you need? Those bits of information are "need to know" Reports

There is probably a couple of situations that make you look in the log. Each situation probably makes you look at some different information than the others. Those situations are different Reports. Different Reports can have the some bits of the same information on them. That's how you know if you should combine two reports; if most of the bits are the same and only a few bits are different, then combine them. But if most of the bits of information are different, then they should be separate Reports.

I notice that you are still talking about

weekly/fortnightly/monthly or Agency timesheets

. There are no weekly and fortnightly and monthly anything. Code will expand the generic (Weekly by design) whatever into the period required. Design them with 7 days and let code do the rest. It is simple code.


Paul, you are running out of time. Let's concentrate on delivering a very simple App that only does what a driver needs to know.

This is why I say that:

Designing an App that has one form and two reports takes as much time as adding three more forms and 10 more Reports.

If it is very simple, you won't feel bad about giving it to 5 or 6 of your mates for Beta testing and promising them that if they are good beta testers, they can get the updates gratis. You do not want to be selling anything that has not been Beta tested, because it will ruin the reputation of your App forever.

A good Beta tester:

Actually uses the App;
Promptly tells you when you App errors;
Tells you what they don't like about the App;
Tells you what they do like;
Tells you what additional features they would like to see.

[crasherpaul]

so if i get this right then, for every tab on the userform you want me to create report or database on the spreadsheet?

also you seem pretty switched on with numbers as well, take a look at this website for tax and ni

http://tax-calculators.co.uk/index.html

and this leaflet which is as simple as it gets to understand with hgv driving. this is what i mean my 9/11 rest and 13/15 working day

https://www.gov.uk/government/upload...HGV_Driver.pdf

Well, I do know that you drive on the wrong side of the road.

lol


https://www.dropbox.com/s/p3re6gx74o...esign.v.08.xls

i did new pages, just wondering if this is what you meant?

[SamT]

Paul,

That's as simple as it gets? Holey NewCastle Brown, Batman, no wonder you need to computerize your schedule.

Both those links are very helpful, I doubt I would ever have been able to code the schedule without the HGV Driver sheet.

I will be working on the Schedule, because that is going to need someone very familiar with VBA code to design.

so if i get this right then, for every tab on the userform you want me to create report or database on the spreadsheet?

No. You really need to look at your App from the perspective of a slow minded driver who just wants it to show him what he "Needs To Know" to be safe and legal. Those are the only Reports you need to think about right now. The rest will come after Beta testing.

I'll tell you a stupid trick I sometimes use, (because it really and truly works.) I collect ball caps and hats. I have one ball cap with the logo of an auto parts store; this is my Redneck Hat. My Redneck isn't the brightest bulb on the shelf. I put him on when I have a hard time getting into the slow and dumb mode to decide what needs to be in a Report, then I'll put on a rather stylish hat, (a sophisticated professional,) to see what nice things to put in. Only then will I start actually designing the Report (or UserForm.) When finished, I'll put the Redneck persona back on to make sure the new version doesn't confuse him.



Here's the reason you need to ignore the current UserForm: All the code on a UF needs to be in the same Code page. Even the simplest UF takes a couple of screens of code. Each Page on the UF takes a few more screens to see all the code. The code for the curent UF is going to take 4-5 screens per Page. It have 11 or 12 Pages, which means 40 to 50 screens of code. That is a lot of scrolling.

When we get to designing the Forms, we will have a Main navigation form with links to all the sub forms. Each sub Form will be based on one of the current Pages. Each sub Form will have three buttons at the bottom; Previous, Main, and Next, so the User can easily navigate thru all of them. This keeps the code scrolling to a minimum. and makes the App much easier to use and understand for the User and for the Coder.

If you go back and look at the "TDR DB Design.v.01.xls" upload, you can see a rough example of what I mean.

I have already started some of the generic Form and Database Utilities code.

[crasherpaul]

so if i get this right then, for every tab on the userform you want me to create report or database on the spreadsheet?



No. You really need to look at your App from the perspective of a slow minded driver who just wants it to show him what he "Needs To Know" to be safe and legal. Those are the only Reports you need to think about right now. The rest will come after Beta testing.

i dont want to come across as seeming a little thick but i still dont know what exactly im supposed to be working on. could you elaborate on this just a bit more please. i word on what i thought i was supposed to do but was wrong and thank you for letting me know cos that saved me no end of waisted hours of work.

i know that the driver would need to have the ability to put up any given pay slip based on the work he has done in any given time period. so if he works weekly etc he can put up pay period (i know i know before you say anything).

i just need to know exactly what im supposed to work on. which reports/databases i mean.

[SamT]

Paul, You have great ideas. Remember, I've seen your work.

You are the architect, I am the builder. You tell me what and I can code it. If you tell me too many whats, I can tell you that it will take too long to code. I can tell you, the Architect, That you need a window, (Shift Date) in every room, (Database,) and that the window sizes have to come from the same Input box.

If I build something, you have to tell me if it fits the need. Take that Estimated Pay Report; Is it good enough; Does it show more or less than needed; Maybe that the driver only needs to see the total expected take home pay. Does he need to see something more, (I dunno, maybe the company's NI contribution?)

Imagine you have 4 friends, one drives for a company, one for an agency. One only drives part time, and the last owns his truck and drives for ??? I dunno how that works.

• As each one of your friends is getting ready for work, what do they need your App to show them?
• As each one first talks to Dispatch, what does he need your App to tell him?
• As each one starts the engine in his truck, What does he need from your App?
• While the truck is being loaded, what do they need to see in your App?
• When any of your friends stops for a break, what does he need to know. What about after the break?
• How about if they stop for petrol?
• Do an Overnight stop? The next morning?
• Have a tire repaired?
• Make the delivery?
• Leave the truck in the company yard at the end of the shift?

Save that list. Add the bits I left out. Put it on four spreadsheets or sheets of paper, What ever works for you. Name each sheet with the drivers job and add the App requirements for each

It will be very useful when we design Forms, too.

[crasherpaul]

im glad you put this list up

• As each one of your friends is getting ready for work, what do they need your App to show them?
• As each one first talks to Dispatch, what does he need your App to tell him?
• As each one starts the engine in his truck, What does he need from your App?
• While the truck is being loaded, what do they need to see in your App?
• When any of your friends stops for a break, what does he need to know. What about after the break?
• How about if they stop for petrol?
• Do an Overnight stop? The next morning?
• Have a tire repaired?
• Make the delivery?
• Leave the truck in the company yard at the end of the shift?

the reason being i forgot that here was a whole lot more information that was needed for example i have given a link to a pdf about driving hours and working time directive please look at pages 17 and 18 on WTD

https://www.dropbox.com/s/t2zvl0a5i1...ersion-2.4.pdf

thank you for the reminder. more work to consider lol

this is a better example of the uk driving regs.

also could you please take a look at what i have done so far on this workbook and let me know if this is ok

https://www.dropbox.com/s/aoo95fjhs6...ign%20v1.0.xls

[SamT]

The workbook looks pretty good. I'll get back to you soon on that.

For the WTD, I'm thinking.

[crasherpaul]

hey samT ive not heard from you in a while how are things progressing?

[SamT]

Paul,

Good news and bad news

Good: I've uploaded the revised workbook with changes and notes.

Bad: My legal counsel tells me not to help you design any Reports involving the WTD or VDO and to help with their calculations only with specific questions. As examples:

Help not allowed: Do I need to know the days worked in the last 26 weeks?
Help allowed: How do I find the days worked in the last 26 weeks?

Help not allowed: Should this report have a "Days Worked in last 26 Weeks" field?
Help allowed: Where should the "Days Worked in last 26 weeks" field be placed on the Report? (Esthetic and usability only.)

Note that the examples are from facts Paul has in his last upload.

Remember that all data is recorded in and only in Databases. All data is viewed with and only with Reports. Any Report that may get printed should be printed from a Worksheet Report, not a UserForm Report.

[crasherpaul]

Bad: My legal counsel tells me not to help you design any Reports involving the WTD or VDO and to help with their calculations only with specific questions. As examples:

Why would you need Legal Council there is no illegal activity happening here!!!!!

Help not allowed: Do I need to know the days worked in the last 26 weeks?
Help allowed: How do I find the days worked in the last 26 weeks?

All information regarding 26 week is taken from VDO again nothing illegal here!!!

Help not allowed: Should this report have a "Days Worked in last 26 Weeks" field?
Help allowed: Where should the "Days Worked in last 26 weeks" field be placed on the Report? (Esthetic and usability only.)
.

This information would go on the WTD page, again nothing illegal here!!!

I do have a one question why would you need to seek legal council. im just a little confused.

how soon do you think i will be before we can start looking at this being in Beta testing stage please.

[crasherpaul]

Hey SamT i have not heard from you in a while, any chance of an update please

[crasherpaul]

Come on SamT how you getting on, please respond

[SamT]

Paul,

Now that you have the Database and Report sheets designed, the next step is to design the data input forms.

First make sure the the DB and Report column Names have no Spaces in them, (replace spaces with an underscore "_")

Then use those as names for all the Form's input Controls, EXCEPT, add a three character prefix to the Control Names. Use "txb" for TextBox controls, "lbx" for ListBoxes, "cbx" for ComboBoxes, "opt" for OptionButtons and "chk" for CheckBoxes. Don't bother naming any Labels.

Don't write any code yet. Attach the workbook when you get the Form layed out, and I'll give you some coding help.

[crasherpaul]

SamT

please find a link below to the workbook with the userform, let me know what you think please.

https://www.dropbox.com/s/oec650kzov...sign.v1.01.xls

[Aussiebear]

I can't help but think that this is becoming a very involved project. One which possibly should have been based on a commercial fee arrangement. Crasherpaul you really do owe SamT a hell of a lot for the effort he's made on your behalf.

As to his statement that he is limited to the specific advice he's able to offer due to legal advice, there is no suggestion that what you are doing here is illegal. It's simply that later on any results derived from this project may be based on an inaccuracy which may lead to a legal liability. That is all.

Afterall any assistance offered here is done with the best intentions.

[snb]

I think this project is beyond the scope of this forum.

[crasherpaul]

I think this project is beyond the scope of this forum.

How? if it leaves here then thats it its all over

[crasherpaul]

I can't help but think that this is becoming a very involved project. One which possibly should have been based on a commercial fee arrangement. Crasherpaul you really do owe SamT a hell of a lot for the effort he's made on your behalf.

As to his statement that he is limited to the specific advice he's able to offer due to legal advice, there is no suggestion that what you are doing here is illegal. It's simply that later on any results derived from this project may be based on an inaccuracy which may lead to a legal liability. That is all.

Afterall any assistance offered here is done with the best intentions.

we are getting along just fine at the moment and i think SamT is aware just how much i appreciate what he is doing and he has taught me and is still teaching me alot so far, but thank you for your interest in this thread.