PDA

View Full Version : help with complicated formula



crasherpaul
07-29-2013, 10:01 AM
Hi everyone


i need some help with my worksheet, ive scoured many youtube videos and other sites to help but to no avail. i have attached my worksheet for people to take a look at to try and get a grasp of what im on about.


i need some formulas to work out time worked and it needs to incorperate overtime and if someone has a sick day, holiday or just a day off. i need this to be one formula, i also have a section that need to calc working day eg 13 or 15 so if someone works >=13hrs then the box needs to show 13hrs and if 13hrs and 01min it need to show 15. also i need a warning of some sort either by changing the cell colour to red or have pop up to day that if they use a 15hr then they have 2 more 15's left and if they use another the needs to say 1 more left and if last one is used then zero left. this need to calc per weeks. similarly for the driving hours. a driver may extend only twice per week.

oh and when holiday is entered it only pays 8 hours. all info is entered on the userform.

could someone help as im getting desperate now.


many thanks
Paul

SamT
07-29-2013, 04:17 PM
May I suggest that your project will be easier to code if you use the Annual sheet to record all the information found on both it and the pay sheets on a worked day by worked day basis. The code can fill in the missed dates with "Day Off" on the pay sheets. Do not place any formulas on the sheets, let code do the work.

That you consider the pay sheets as merely reporting data from the Annual sheet and only the data since the last pay day, and the year to date totals.

That you do not give the drivers a way to acess the Setup sheets, or to enter data from a date earlier than the last date on the annual sheet. for example, when they "forget' to enter a days work report. Leave those tasks for the office personnel.

I would also make the Setup and Annual sheets xlVeryHidden and only allow office personnel to even view them.

I noticed that your upload has a lot of code that is not used. In the future, please delete any such code or forms when you upload examples.

crasherpaul
07-29-2013, 10:08 PM
the reason im doing all this is because as a driver myself i want to know exactly what im gonna get paid, because if the office makes a mistake with the wage i will know exactly how much they have short changed me by.

as for making the Setup sheet being very hidden i will be doing that before giving out this workbook to other drivers.

however i still need help on this worksheet please


May I suggest that your project will be easier to code if you use the Annual sheet to record all the information found on both it and the pay sheets on a worked day by worked day basis. The code can fill in the missed dates with "Day Off" on the pay sheets. Do not place any formulas on the sheets, let code do the work.


i would like to see your suggestion on this as i really dont know what you mean

SamT
07-31-2013, 10:59 AM
If this is a Drivers workbook, then the above comments about the office don't apply,as the driver is the office :)

I took a short look at your book. Please give me a little time to think about it, as I did see some issues (for example; many input boxes that probably have the same date in them,) and I might have some more suggestions to improve the UserForms.

The work you have put into this is really impressive.:clap:

What I meant by using the Annual sheet as a database was that every time the Save button on any form is clicked, all the data from all the Forms' input boxes be saved to the database sheet.

The data inputs to the Pay To Date sheet should be retrieved from the database sheet as needed. The formulas for Projected pay can be on the sheet. If you have a means for the driver to enter actual pay, that data should be stored on the DB sheet when saved.

The schedule sheet is obviously a special case, since the driver can only input future schedules as he learns them, and it needs to pull past data from the DB sheet, and it probably be easiest to have some formulas on it. As I see it, it only needs to show date from the last few days and for any future days where the schedule is known. VBA code can scroll these rows for you.

crasherpaul
07-31-2013, 11:18 AM
SamT please let me say thank you for quite a comprehensive reply i was not expecting anything like that :D. i would be very interested in seeing your ideas, what i would like to do is upload the workbook again since i have done some changes to the workbook and have many ideas that i want to apply to the worksheet. i never realised that this was going to get so involved but as a driver myself i want to make sure that other drivers get the best from this and not have it being some very simple time sheet.

there is also this idea that im after doing as well

i have a cell N3 where i put lets say Aug13 and i have cell that hopefully put in 1 aug 2013, what im after is this.


if N3 is August or aug13 then i need either 29 or 30 rows created and filled with the number of days in the month, the reason for this is that there are 30 or 31 days in a month as you know.


is this possible?

i really would love to collaborate with you on this. i am planning on selling this for about £10 per copy and if this goes well would love to share it with you. sorry to admin if this line is wrong.


look forward to hearing from you soon

my per email is flyinghigher2011@gmail.com

crasherpaul
07-31-2013, 11:21 AM
i also came across this little sheet that i would love to incorporate as well. with this they could scroll through past months and if needed have the ability to print it.

SamT
07-31-2013, 03:23 PM
I'll check both of them out.

This code always returns the number of days in the current month. The way it works is that the zeroth day of next month is the last day of this month.


Sub test()
Dim X
X = DaysInMonth
X = DaysInMonth("Nov")
End Sub


Function DaysInMonth(Optional MnthStr As String) As Long
'Reurns days in current month if MnthStr Is missing

Dim Mnth As Long
If MnthStr = "" Then
Mnth = Month(Now)
Else
Mnth = Month("1/" & MnthStr & "/2000")
End If

DaysInMonth = Day(DateSerial(Year(Now), Mnth + 1, 0))
End Function

SamT
07-31-2013, 04:23 PM
I think you're talking about the Payslip sheet in the EPIF book.

A little bit about nomenclatures and definitions I use. Databases (In Excel) are tables with labels in Row 1 and/or each column is a Defined name range.

Reports are temprary and contemporaneous, that is they are designed to be a report of information about a specific time and are to be viewed or printed, but do not store any information. They are analogous to paper forms and are usually designed around an existing paper form. They can be on a worksheet or on a UserForm.

Forms are always UserForms. So if I were to say "you need a report for that" you can use a worksheet or a UserForm, but if i said "You need a Form," I mean a UserForm only. Forms are used to enter or edit or occasionally, view, (like a report,) records in a database.

UserForms only print as images, so it is usually best to use Worksheet reports for printing.

For now, hold off on designing any more forms or reports. The very first thing to design in any middling to large app is the database, because

Function Follows Data Structure

I'm attaching a list of all the input boxes on the main form. I leave it to you to understand what data they represent. You will need these to name the columns in the Database sheet. Just put the data names in the top Row as column labels. The Data names should be understandable by any trucker.

When done, the database sheet should have a column for each different piece of data. Don't worry if the table seems to be getting too wide, it's not really for humans to look at anyway. That's what Reports are for. Now I know that I said not to design any reports right now, but you do need to take a good look at all reports desired to make sure there is a data column for each bit on the reports.

Since you only have around 90 controls on the Form, I don't think that we'll run out of columns, even in my Excel 2002 version, so go ahead and group the columns with empty columns between groups, if you like.

One thing, the first column must be for the DATE of the entries. That is the main lookup for all entries, even though we'll be doing lookups on other columns, too.

SamT
07-31-2013, 06:47 PM
Paul,

Since I have done this before I started a database driven layout for you.

After you have gotten all the DB column Names, fill out the vbaLists sheet as far as they go.

When that is done, I will make new names for the form controls according to a formula that makes coding much simpler. Then I'll give you a list of the changes so you can do all the renaming of the controls. :devil2:

BTW, on vbaLists, the columns of "Column Numbers" should be continuous, but leave blank cells in the "Column Names" and "Control Names" columns to represent empty columns on the DB sheets. The purpose of this is to make it very easy to modify the DBs and UserForms.

crasherpaul
08-01-2013, 08:30 AM
i had issues with formulas and links due to the fact that this workbook was edited in office 2003 and im working with 2010 not sure how to get around it. also i make some notes that you may be interested in.

crasherpaul
08-01-2013, 08:39 AM
ignore the workbook but please take a look at my redesigned userform

crasherpaul
08-01-2013, 10:26 PM
SamT id like you to take a look at the full driver time sheet and you will see everything that i have on it. inc my databases. let me know what you think.

SamT
08-02-2013, 05:44 AM
Paul,

I think that you still don't understand the differences between Data Bases and Reports.

I know... That anybody who tries to use one table for both purposes is doomed to failure. To use a vehicular analogy, they will have to pull the engine to change the oil and step out of the cab to set a turn signal.



Let me offer you this thought experiment. A flight of fancy, if you will: You find a young man with lots of promise and take him on as an apprentice driver. You try to teach him the best routes, the best places to over night, and all the things a successful driver should know. All day long all this kid does is tell you how he would do things and expect your approval about those things.

That's the way I'm beginning to think about you. You're the kid.

Don't get me wrong, Paul. You have lots of potential, your UserForm shows that you have a very good sense of detail, you are obviously very enthusiastic about this project, and you even have something I don't; a good sense of esthetics.

Now I might be a grumpy old man who doesn't know his arse from the tailpipe of a 7.5T lorry, but I've been driving this application building process for a lot longer than you, and if you really want to make a good, easily usable and easily maintained Driver Log, you're going to have to listen to me.

I know that It doesn't seem like my previous hints are going anywhere usable and that you really can not see the light at the end of that particular process tunnel. I am not going to put the entire development process into one post. But if you do the things I ask as I ask them, you will get to a usable product much faster.



One last chance.

Paul, there is one step I haven't mentioned yet and it is the very most important step of all. I will bet you a Pound against a biscuit that you haven't done it either.

Write down on paper, so you can see it often, in 30 words or less, exactly what you want this application do accomplish. I've done it for your App and I did it in 22 words. Don't count the first 3 words: "This Application will ..."

Obviously, you won't get into any detail in 30 words, but that is the idea. You need an overall goal.

Paul, get back to me when you've got that description ready.

crasherpaul
08-02-2013, 08:26 AM
This Application Will........track wages, Nights out, expenses, working day, Holidays, enable editing of certain details. work history, view vehicle details with dates, hours and overall pay. (24 words) i think that about cover is.

SamT
08-02-2013, 08:30 AM
Details. Don't want no stinkin details.

How about


This Application will give drivers an easy way to record and view all the information that they need to know in a well organized fashion.

SamT
08-03-2013, 10:09 AM
i would be interested in seeing your thoughts on the time-sheet but there are some thing the i saw on the last sheet you sent over that i would not need which i mentioned. however i would have one question and that you would be this: would you need to change the layout of my userform or is it ok. see i did/do like the idea of being able to view history(s) of pay, night out history etc. and also have the ability to change a night out location if things change.

as you rightly stated before i am new to this and yes i did try to add alot of features in this to try to help the not just myself but other driver. my over all though was to show the userform only and not the worksheet. so they double click the worksheet but they only ever see the user form and not the worksheet unless they click a button to show work sheet from the user form (your thoughts on this please.)

i am will and trying to learn but my head spins with all these ideas that i have and everything just seems to get away from me. im really sorry SamT

Oh yeah, beginners enthusiasm. I remember it well.
i am will and trying to learn but my head spins with all these ideas that i have and everything just seems to get away from meThe only way I have found to deal with these idea is to write them down. Go back to the last book I uploaded, Truck Driver Report 02.xls (http://www.vbaexpress.com/forum/attachment.php?attachmentid=10348&d=1375321620), and insert a new sheet behind the Control Panel sheet. Name it "Ideas." Stretch Column "B" to the Right until just enough of Column "C" is showing to act as a border. Format Column "B" with "Wrap Text." In Column "A" write a brief, one line, description of the idea. In the next Row, column "B," write as much about the idea as you need to. Skip a row and repeat with the next idea. I still use this process when developing an App.


my over all though was to show the userform only and not the worksheet. so they double click the worksheet but they only ever see the user form and not the worksheet unless they click a button to show work sheet from the user form (your thoughts on this please.)In my mind, I see the App as an integrated object, where the user can see all the relevant sheets at will. I would have the workbook open to the Control Panel sheet, from which he can either open a form, or browse the sheets. I would only let him open a form from the control panel. This reinforces the idea that the CP is the way to edit the sheets and provides a consistent User Interface. A consistent navigation system is important to a well designed App.

IIRC, I haven't spoken of your current UserForm yet. That is because it is still too early in the App design process to put any time in thinking about the Form itself. I think of the current version of the UserForm as just another "Idea" Sheet. It's sometimes handy as shown by the ease of my getting a list of Data Points for you, but not necessary.

Briefly, these are the steps that IMO are required to develop an App like yours:

I: Develop the database


Create a list of every possible data point you can think of. See my "Company Database" sheet and your "VDO" Sheet. Name this sheet "Data Points List"

Use two columns, the first for the name of the data point and the second for a brief description of it. The second is mostly for me, but you will find it useful, too.
The Data Point "Date" should be at the top of the list.

Its description is "The Calendar date of which all records are pertinent to."
It is the only Data Point (DP) with the simple name "Date." All other Date names should be uniquely descriptive such as "VDOPrintDate," "ShiftStartDate," ShiftEndDate." etc. I'm not saying that you will have ShiftDates, that's just an example.


Using the description column, insure there are no duplicate data points. Indicate in the description which different place this DP is used.


Using a different sheet for each group, organize the DPs into related groups like the "Company Database" and "VDO" sheets.

Name each sheet according to its group
Put the lists in row 5 or 6 on each DP group sheet.
Using the Descriptions, make sure that each group sheet has its own copy of the relevant duplicated DP.

DP name (calendar) "Date" should be at the top of each groups sheet's list.




Review all the groups to insure that the requirements in steps 1 and 2 are met.
On each Group sheet, copy the list of Names and in cell "A1" PasteSpecial >>Transpose.
From now on, we will no longer refer to DPs and Groups, They have now transformed in to Field Names and Database Tables.
Format each Date and Time Field (Entire Column) to suit.
Save the workbook with the name "Database Descriptions."

Anytime in the future when you modify a database, edit this workbook to match, adding notes to describe what, when, why.
Keep Data Points List up to date.




Normally I would allocate several days for all the above, but you (and I helped a little bit with the list of DP Names,) have already done most of it. By the time you complete the above, you will have a very good feel for what your App can and should provide any truck driver and most important, what Form(s) you will need. The completed "Database Descriptions" is half the work of developing the UserForm(s), so do not think that it is not necessary.

Each of the below major steps will also take a few days. Only a few because of all the preparatory work done in step I. As you get to each step below, I will be expanding the minor step lists, so you will know what to expect.

II: Lay out the Userform(s)

Save book as "TDR.v.01." Suggested name.



Use the Data Points List to know what controls you'll need.

More info later


Open "Database Descriptions" and add the sheet "vbaList" to the end.

Delete all DP lists, leaving only Field Names.
Complete "vbaLists."
Make Lists of Suggested Form Control Names
Add database type code to Database sheets and "vbaLists."

I already have this code, that only needs localization to work in any DB type sheet.





III: Code the Form(s)

Increment the Version number to .02

IV: Add Reports Sheets, Increment Version number
V: Repeat II, III, & IV as needed.
VI: Beta test.
VII: Repeat step V as needed.
VIII: Sell the Application :)
IX: Repeat Step VII as needed

You will have noted that with each major step, the work book gets saved with a new name. It is not until the end of step V that it actually gets a working name. Be sure and keep a copy of each workbook with a different name, even if it's just a minor revision number change.

I usually keep my projects in their own folder with the following sub-folders:

bas
Archives
Helps
Examples

bas is where I export to and import from any modules I want to move from book to book.

Paul, take whatever time you need to understand what is in this post. As questions about it if you need to, but really try to fully understand it.

crasherpaul
08-05-2013, 01:10 AM
SamT i made a start on the workbook but did not want to go to far only to find that i had not followed your instructions properly. I only ask one thing SamT and that to remember that i am a complete novice at this and will make mistakes, i do understand that you are an expert and pritty much know everything about designing things like this where as i am not so please be patient and i only have around 3 weeks left to complete the thing and putting in around 10 to 12 hours on this every day.

i have made a few changes to the userform as well to take into account different things i need.

thank you for your patients

SamT
08-05-2013, 03:23 PM
I have done about half the work needed on the Databases. Your form is so hard to use and understand. and your abbreviations so opaque that I could not finish them.:banghead:

All that is really left for you to do on the DBs is figure out the names that I couldn't and fill in the column Labels and the DB Descriptor tables, (those are the three-column lists I put in row 5 or 6, column "A", and below on each DB sheet. Leave those lists there! Note that the name of this workbook is DB Design! It is a permanent record of the DB layouts.

Note that the control names and the Column Names should closely resemble each other. Try not to use abbreviations. New drivers might not understand them, I certainly won't, and they make coding harder. Obviously, a well known abbreviation is okay, (ODO for odometer, Rcvd for received, etc.) Look at all the examples I provide on the DB sheets. (DB for database) :yes

Note that the workbook is an xls type book. Always try to save all your coding and design and development work in older style books so that you can sell your product to drivers who are still using older versions of Excel. I recommend saving all books as Excel Version 2000 if you can. All the books I send you will be in version 2002, which will probably be good enough, because I learned with version 97 and tend to stay with that code base. IF the book still works when you open it after saving it as version 2000, you know that it will work in that version (2000) of Excel.:cool:

Any time you are waiting on a response from me about the DB Design book, you can start on a Reports Design book. Only design Sheet style reports for now and do not worry about getting the data on the report. Just put the DB and column Name in the blanks. A real example from the DB Design book,
TachoDB!ODO Start Note the Exclamation mark after the DB Name. The code will find the appropriate date on the DB using the ShiftDate Column (column 2).:thumb

crasherpaul
08-07-2013, 12:43 PM
Hi SamT

I think that I've done all that you asked! could you please take a look and tell me what you think.

Thank You


unfortunately i don't know how to delete previously uploaded files and cant upload any more so I cant upload this amended one. All I can do is give dropbox link.

https://www.dropbox.com/s/gamnv5fnpsdhcs5/TDR%20DB%20v.06.zip

SamT
08-08-2013, 06:09 AM
I'm on it

SamT
08-08-2013, 10:41 AM
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
08-09-2013, 12:44 PM
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
08-09-2013, 02:19 PM
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
08-10-2013, 12:20 AM
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/uploads/system/uploads/attachment_data/file/193402/Staying_Legal_-_HGV_Driver.pdf


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


https://www.dropbox.com/s/p3re6gx74om5m1o/TDR%20Reports%20Design.v.08.xls

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

SamT
08-10-2013, 09:23 AM
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
08-11-2013, 04:21 AM
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
08-11-2013, 06:26 AM
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
08-11-2013, 11:37 AM
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/t2zvl0a5i1q7ccz/HGV-Drivers-Hours-Book-Version-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/aoo95fjhs6i9bmc/TDR%20Reports%20Design%20v1.0.xls

SamT
08-14-2013, 07:38 AM
The workbook looks pretty good. I'll get back to you soon on that.

For the WTD, I'm thinking.

crasherpaul
08-25-2013, 02:52 AM
hey samT ive not heard from you in a while how are things progressing?

SamT
08-25-2013, 12:52 PM
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
08-26-2013, 12:47 AM
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
09-05-2013, 11:41 PM
Hey SamT i have not heard from you in a while, any chance of an update please :)

crasherpaul
09-12-2013, 10:50 PM
Come on SamT how you getting on, please respond

SamT
09-13-2013, 07:19 AM
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
09-15-2013, 02:36 AM
SamT

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

https://www.dropbox.com/s/oec650kzovr64i3/TDR%20DB%20Design.v1.01.xls

Aussiebear
09-16-2013, 04:07 AM
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
09-16-2013, 04:31 AM
I think this project is beyond the scope of this forum.

crasherpaul
09-16-2013, 06:49 AM
I think this project is beyond the scope of this forum.

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

crasherpaul
09-16-2013, 06:52 AM
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.

snb
09-16-2013, 12:41 PM
Yes I noticed a remarkable learing curve in post #33 and #34

SamT
09-16-2013, 01:44 PM
Paul, I am not going to list all the errors in the latest upload.


I see that you have a serious objection to using long and fully descriptive form control names.

At first glance, the forms seem to be a good start.

You have named this workbook as TDR DB Design Version 1.01, which means that it is the finalized database and only the finalized DB, but doesn't contain any DBs. It only has some user forms and a bunch of sheets with grey tabs, (I can't tell what the grey tabbed sheets are supposed to be, are they Reports [yellow tabs] or what?) It also has some green tabs that appear to be VBA List prototypes. The actual VBA_Lists sheet (Red tab) is incomplete.

crasherpaul
09-16-2013, 10:37 PM
ah sorry SamT i see what i did, i did not transfer the userforms over to the last sheet you sent over to me. ill get on to it right away.

SamT
09-17-2013, 06:58 AM
Paul, You aren't doing this for me. I am trying to help you learn, You must think about what you are doing. What I say is merely a guide for you.

crasherpaul
09-28-2013, 07:19 PM
sorry for the delay in getting back to you only i lost my internet. the tabs that i forget to colour where meant to be yellow. have you by any chance managed to do any coding yet please. also i have had my arse chewed off because of this taking so long as well.

SamT
09-29-2013, 03:58 AM
Have you got the Report sheets and Databases done?

crasherpaul
09-29-2013, 07:50 AM
he is the link to the file

https://www.dropbox.com/s/oec650kzovr64i3/TDR%20DB%20Design.v1.01.xls

SamT
09-29-2013, 01:46 PM
There aren't any databases in that book.

crasherpaul
09-29-2013, 10:43 PM
this really is getting complicated. i thought that i did everything that you wanted me to do.

Aussiebear
09-30-2013, 02:31 AM
CrasherPaul, mate you owe Sam a great deal here, as this clearly is well outside the concept of a single issue thread.

SamT
09-30-2013, 05:52 AM
i thought that i did everything that you wanted me to do.

I... Don't want you to do anything. I am only trying to guide you in doing what you want to do.



Refer to: Workbooks("TDR Reports Design v.03.xls").Sheets("Paul, Read Me")

crasherpaul
10-07-2013, 06:17 AM
as far as im aware i have done everything that was needed, im just not sure what colour the first lot of tabs are meant to be. (holidiays - nights out) other than that i think it should be ready for coding

SamT
10-07-2013, 06:45 AM
DataBase Sheets never get database names at the top of the sheet. The database name is the Tab name.

Database tables always start in cell "A1". Row 1 is always the DB Field names.
Except as noted below, Row 1 is the only cells that should be formatted. The entire row should be number formatted as Text. The entire Row's font should be set to a named Font and in Bold and 1 or 2 sizes larger than standard. The entire Row 1 Bottom border should be heavy and solid. Individual fields can be seperated with borders of any size and style. The entire Row should be Horizontal and Verticle aligned = Center. Excel works better with its Databases this way.

Database Fields (Columns) should be Number formatted only as indicated below:
Numbers that must be treated as text:

Text
Money
Dates
Times
Percentages
Fractions

crasherpaul
10-08-2013, 10:02 AM
this seems to be getting really complicated

crasherpaul
10-25-2013, 08:03 AM
Hey SamT are you able to start coding yet please?

SamT
10-26-2013, 07:50 AM
Hey SamT are you able to start coding yet

http://ffextensionguru.com/gofirefox/th_2funny.gif

On what?

Aussiebear
10-27-2013, 12:50 AM
this seems to be getting really complicated
Absolutey, which is why I suggested you owe Sam big time.

crasherpaul
10-27-2013, 06:34 AM
Absolutey, which is why I suggested you owe Sam big time.

Again I know!!!!!!!!! can i ask you to PLEASE stop interfering there is no need for it, with respect. thank you

crasherpaul
10-27-2013, 06:37 AM
http://ffextensionguru.com/gofirefox/th_2funny.gif

On what?

ive spent so much time on this, and ive done everything that you asked and now you are saying that there are no databases. and you laugh at me with "http://ffextensionguru.com/gofirefox/th_2funny.gif". have i disrespected you? No! this has been dragging on for far to long.

this is a bit like fly fishing, you have everything that i need and you tease the fish with the fly.

It's annoying how other people come on here acting as if i don't understand how much you have put into this and quote "you owe Sam big time". i know what you have put into this but its funny how people miss just how much time i have put into this as well.....convenient isn't it.

SamT
10-27-2013, 08:22 AM
Paul,

I think that your project needs more than I can offer. I have a lot going on i my life right now.

Perhaps one of these links can get you to where you want to be.

http://www.amazon.com/Excel-Programm...371/ref=sr_1_1 (http://www.amazon.com/Excel-Programming-Dummies-Computer-Tech/dp/1118490371/ref=sr_1_1)


http://www.getacoder.com/projects/pr...g_c_87390.html (http://www.getacoder.com/projects/programming_c_87390.html)

I wish you the best of luck and truly hope that you get the program to work the way you want.

Regretfully yours,
SamT

Aussiebear
10-27-2013, 03:53 PM
This thread is now closed.