Date Forcasting

[Misty]

I have what looks like a simple thing to do, but I've discovered that I sure don't know how to make it happen. The method that "Modest" showed me how to do wouldn't work because of a problem with the data I was using.

Using the data in the attached db, I need to forcast future dates based on a date span and a start date.

I've attached a small db to explain the data I have and what I'm trying to do.

If anyone can show me how to do this, I would greatly appriciate it. I'm down to just a few days before my boss expects to see the results and I'm freaking out.

Thanks in advance for any help that I can get.

M

 

[modest]

Need more information:
- What is FirstDate?
- Why is there a pmname textbox if you want to return all pmnames between a certain date?
- You can't just group by pmname and show all the dates... you'd have to group by pmname and the due date (which wouldn't do anything). If you had a report that occurred every day and you selected a 10 day span that report would come up 10 times... you can't group it by the person (how would you choose what date to use?)

 

[Misty]

The form was just something to put the message on. It's not a used form.

The [firstdate] is the first time the pm was ever due. The software I'm linked to keeps that date. It's really the only thing I can find in the tables to use as a starting point for future dates.

If the start date was 5/1/05, and the end date was 12/31/05, I need the report to show a monthly breakdown of every pm that will be due between those two dates in a way that I can add querys to for more information gathered from other tables.

These are preventive maitenance work orders that are generated by the software I'm linking to. However, the software has no means with which to forcast what pm's, (and how many), will be due each month in the future. Dummy me, when asked, said "Oh, maybe I can get Access to do that". Gawd, I didn't know that a bunch of others had already tried and failed.

If you can figure this out and put your credits in the code or just on a form, I can promise you that you will become a very famous person to these people. They will see you every time this thing is used! A contact email might even generate some business if I can talk them into it.

What they are trying to do is balance the work load for the shop I work for. When this forcast is enabled, I then have to collect past hourly data for each pm in the past and show them a daily, weekly and monthly total for man hours. Then I have to balence the pm's so that the workload is as close to even as possible.

But first, I have to be able to see which pm's are going to be due, and when.

The boss wants to see a prompt for a beginning date and an end date so that we can go between any date span.

Next time, I think I'll be very quiet. At least until I can learn a whole lot more about Access.

Modest, I really, really appriciate all the effort you've given me. You have got to be a really nice person!

Desperate Misty

 

[modest]

Like this maybe?

 

[Misty]

That looks exactly like what I needed. Unbelieveable! I looked at the code behind the OnClick event of the OK button and of course got lost real quick. I'm going to go to Barnes and Noble tonight and get an Access 2003 book that will help me learn VBA from the bottom up. Is there a particular book that you know would be the best for a VBA beginner in Access 2003?

I followed a lot of what you did in the code, but I'm so new in VBA, I don't understand the "why" behind most of it and why the order of the code had to be what you did.

I couldn't find anywhere that refers to the table you named "PmData2". Did you make it just to create a backup of the "PmData" table, or is this new table used somewhere? I've made backups of tables before and I'm curious of that is what you did here.

I am determined to learn how to do this kind of thing in VBA. I'm good at learning from books and I have a couple of hours each night that I can study and work at it. My last book was for Access 97, and it was a basics book that I never got into the VBA in.

How did you learn Access? Are you self taught or did you learn in a classroom?

I wish I could back up and learn in a classroom enviroment, but I'm out in the working world now and trying to make ends meet. This one instance has bothered me so much, you wouldn't believe it. I will learn this!

Modest, thank you so very much for pulling me out of this mess. I'm going to try to get my boss to understand that things involving coding will have to wait until I can learn it. I can't keep asking someone else to solve my problems without putting a sincere effort into learning what I'm missing.

You have saved the day!

Misty

 

[modest]

I couldn't find anywhere that refers to the table you named "PmData2". Did you make it just to create a backup of the "PmData" table

That's exactly what I did. I was playing with recordset clones because I thought that a clone would copy the recordset data to a new recordset variable and act independently of the table. Instead, they still pointed to the same table. This meant that when I deleted a record that didn't meet a certain criteria, it would delete it from the actual table. Now I have it set up to add the records that match to a new table. This is mainly because I don't know how to populate a sub form with data from a recordset. I don't regularly use sub forms, so I've never looked into this. I just based the subform on a table (the easy way).

How did you learn Access? Are you self taught or did you learn in a classroom?

I learned "Access" as I learn anything having to do with computers. I sacrificed my time for fiddling around until I understood enough. I don't believe you need to pay money to learn the intro. level things. There are plenty of websites, tutorials, online books, and forums that will answer almost any question you have.

James Madison University has a good intro. level VBA/Access tutorial. There are many other sites that do as well. If you want to get a good VBA book I suggest "Access 2003 VBA - Programmer's Reference" by Cardoza, Hennig, Seach, and Stein (found at www.wrox.com or Barnes and Noble). Just take a minute and walk through the code line by line.

 

[Misty]

I just finished making the first of the reports that my boss wanted. I couldn't have done it without the help you've given me.

I'm going to pick up the book you suggested tonight. I'll also take a look at the website you suggested. It sounds like just what I need.

You have relieved me of about a ton of stress. I can't thank you enough.

Thank you, thank you,

Misty

 

[dt01pqt]

That's exactly what I did. I was playing with recordset clones because I thought that a clone would copy the recordset data to a new recordset variable and act independently of the table.

You can think of the recordset clone as the copy of the recordset the form uses which is linked to the actual recordset. The word 'clone' is a bit misleading like you can see it is a pobject not a cloning function. I would call it RecordsetLinkedCopy or s'thing.

 

[modest]

Yes, I wish recordsets were a little bit easier to work with. I'd like to see a built in function copy a recordset over: set rs2 = rs1.copy

I'd want this so that I could just delete the necessary records without affecting the source data. I know filters help limit the results, but i'd still like that copy without having to loop through one recordset like:

rs1.movefirst
do until rs1.eof
    with rs2
        .addnew
            for each fld in rs1.fields
                .fields(fld.name) = fld.value
            next
        .update
        .bookmark = .lastmodified
    end with
    rs1.movenext
loop

 

[Misty]

James Madison University Tutorials

Hi Modest, I looked for a VBA/Access Tutorial on the JMU website and couldn't find one.

Do you happen to have a link to it?