I have ~1000 shop orders I track in an access table (dating back from
May 2004 to today) each with a date in, and a date out. (along with
other data) They all take different amounts of time to complete. Some
do not currently have a 'date out' as I am working on them. We have
been trying to implement some lean initiatives and want to track how
well we are doing. Things seemed to flow much better following some
changes. However, recently I have found that we are once again
overwhelmed with work in process. If I want to know how many orders I
have in progress each monday morning (a snapshot)...how do i take a
summation of work in progress that was in the pipeline that week? Once
I complete the collection of datapoints, I'd like to post a sharp chart
in my factory displaying the past data and a trend line with goals.

Once I figure out the data for the past 2 years I would like to make
the process automated off the data I enter. If the dBase can function
on its own forever with minimal maintance that would be idea.

My biggest obstacle is that I am a manufacturing guy, not an
information whiz. I went to ask some of the other guys who use access
in my office and got the 'I think you might need to write up a VB
function with a loop' and 'You can probably do that in SQL'...well like
I said, I am not a data whiz, or programmer, so I just nodded my head
and uttered a short, 'oh...ok'. Not having a clue what to do.

Does anyone have any tricks or places I can look for more help?

Thanks in advance,
Bob

rdh009 wrote:
> I have ~1000 shop orders I track in an access table (dating back from
> May 2004 to today) each with a date in, and a date out. (along with
> other data) They all take different amounts of time to complete. Some
> do not currently have a 'date out' as I am working on them. We have
> been trying to implement some lean initiatives and want to track how
> well we are doing. Things seemed to flow much better following some
> changes. However, recently I have found that we are once again
> overwhelmed with work in process. If I want to know how many orders I
> have in progress each monday morning (a snapshot)...how do i take a
> summation of work in progress that was in the pipeline that week? Once
> I complete the collection of datapoints, I'd like to post a sharp chart
> in my factory displaying the past data and a trend line with goals.
>
> Once I figure out the data for the past 2 years I would like to make
> the process automated off the data I enter. If the dBase can function
> on its own forever with minimal maintance that would be idea.
>
> My biggest obstacle is that I am a manufacturing guy, not an
> information whiz. I went to ask some of the other guys who use access
> in my office and got the 'I think you might need to write up a VB
> function with a loop' and 'You can probably do that in SQL'...well like
> I said, I am not a data whiz, or programmer, so I just nodded my head
> and uttered a short, 'oh...ok'. Not having a clue what to do.
>
> Does anyone have any tricks or places I can look for more help?
>
> Thanks in advance,
> Bob

rdh009: I have 1000 orders, each with a date in, and a date out. they
all take different amounts of time to complete. I want to know how
many orders i have in progress each monday morning...how do i take a
summation?
rdh009: the first order came in May '04
Drthunderjls02: ok, give me a min to think
Drthunderjls02: I would make a query displaying all the jobs in
progress
Drthunderjls02: then do a record count
Drthunderjls02: so any projects with a null date out
Drthunderjls02: where do you need to display this information
rdh009: table, chart, and report
Drthunderjls02: ic, do you see what I mean though?
rdh009: sure, but I'm not sure how to collect data points for each week
for the past 2 years (completed work)
Drthunderjls02: oh so you need to graph jobs in progress for the past 2
years
Drthunderjls02: ic the problem
Drthunderjls02: well you need a variable to query with and then total
those
Drthunderjls02: that is a little more difficult
Drthunderjls02: I'm trying to think how you can do this in SQL
Drthunderjls02: you may need to write a VB function with a loop
rdh009: alright
rdh009: i think i might be in over my head, but im going to try and
think about how id do it by hand and try to post on a google group
forum
Drthunderjls02: ok, I'll give it a think too and get back to you
Drthunderjls02: I'll assume that this database will have to function on
it's own forever right?
Drthunderjls02: with minimal maintance
rdh009: yeah
Drthunderjls02: ok
Drthunderjls02: so will this report be on it's own
Drthunderjls02: just a chart of jobs in progress
rdh009: yes, including a history
Drthunderjls02: ok, I think you'll need to write a function that makes
a table
Drthunderjls02: it will need to find every Monday (or whatever day of
the week)
Drthunderjls02: and then use a while statement to calculate the
JobsInProgress
Drthunderjls02: then increment to the next monday
Drthunderjls02: and with each iteration of the while add a new row to
the table.
Drthunderjls02: then each time the report is generated destroy the
table and recreate it with the data that's there
Drthunderjls02: there is a decent amount of coding to do it though