RE: Need to be able segregate units run in excel sheet using time.

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thank you again for the quick response

So I am not going mad this is indeed a complicated task.

i think i will need to timestamp and see how that works if at all.

Again thanks.


"K Dales" wrote:

> So is your idea to run the query hourly and use some sort of time marker to
> figure out which jobs were completed in that hour so you can calculate your
> statistics on that basis? It may be possible - using the timestamp as I
> suggested, but there is still something I don't see how to do that would
> affect the result. Let's say the entire process starts at 6am, so there is
> no data in the database at that time. You want to collect the data from every
> hour's run. So you run the query at 7 am and you have everything from 6-7 -
> and, you add the time stamp at the time the query was run. So far, so good -
> you don't know the exact time the units were processed, but at least you know
> from the timestamp they were between 6-7. But now at 8:00 you run the query
> again. How do you restrict the results to only the ones done after 7:00?
> Unless you can do this (and from the query and info you gave, I don't see
> how), the best you can do is run everything going back to 6 am and timestamp
> it. If you run this "on top of" the old query it replaces the old time stamp
> with the new one and you lose the ability to track time this way. If you run
> it as a separate query, saving the old one, you will have to compare records
> to find the duplicates and figure out the correct time by taking the earlier
> time if it is a duplicate. This problem will grow for each hour you download
> the data.
>
> I don't see a way around this, unless there is more to the database than I
> can see - or unless you use something like MS Access to analyze the results
> instead of Excel. Access can do update and append queries that can handle
> the type of situation I describe above better than you can in Excel.
>
> Sorry not to be of more help right now, but the problem actually looks
> pretty complex. The best Excel can do is tell you when you asked for the
> data, it can't tell you directly when the data was created or completed. You
> would have to use some fairly complex logic to do that and I don't know if
> Excel is the right tool for that job.
>
> "Jim Ferguson" wrote:
>
> > K Dales
> > Thanks for getting back to me.
> >
> > Units ran are an expression as you rightly pointed out of how much is
> > produced.
> >
> > I need to know how many units were actually produced by a filler during a
> > production run. I have totals and can work out a running efficiency from
> > information coming back from database. As yet I have been uable to get an
> > hourly efficeincy
> >
> > ie from 06:00 to 07:00 and 07:00 to 08:00 filler 1 ran 15000 and 10000 units
> > on each hour this would obvously give the filler a different efficiency
> > between 06:00 to 07:00 than from 07:00 to 08:00.
> > This is what I am trying to get back from the database.
> >
> > Can I use a macro to to determine the amount run within an hour or SQL.
> >
> > SELECT A, B, C, Now() as TimeStamp FROM MYTABLE ...
> >
> > If I use the above would I puy this in the SQL query or Macro
> >
> > MLINE MBDESC
> >
> > At present I can separate the fillers and the size of product running to
> > ensure data is coming back correctly using the above descrition. below is the
> > query used to return data at present
> >
> > SELECT WorkData.hstat, WorkData.hprod, WorkData.MLINE, WorkData.MBDESC,
> > WorkData.MBTYPE, WorkData.MMTYPE, WorkData.MQTY, WorkData.MLANE
> > FROM database.dbo.WorkData WorkData
> > WHERE (WorkData.MLINE Between 1 And 20) AND (WorkData.hstat<>'Y')
> > ORDER BY WorkData.MBTYPE, WorkData.MBDESC, WorkData.MMTYPE
> >
> > hstat has 3 possibles ('Y' or 'C' or '?')
> > Y = complete
> > C = running
> > ? = waiting to run.
> >
> > Hope this explains it better.
> > As i said I am a novice with macros and SQL.
> >
> > "K Dales" wrote:
> >
> > > I am not sure I understand: When you say "units ran within an hour" are you
> > > using the word "run" as in running your query, or do you mean something to do
> > > with the process you are measuring (i.e. the time these "units" were actually
> > > produced)?
> > >
> > > If the former, basically you want to add a time stamp to your query. That
> > > could be done in a few ways, probably the easiest is to add a calculated
> > > field to your query. The form of the function you would use will depend on
> > > your database driver and SQL version, but the following works with
> > > Jet/MSAccess:
> > >
> > > SELECT A, B, C, Now() as TimeStamp FROM MYTABLE ...
> > >
> > > This appends a field with the time the query was run as a column in my
> > > results table.
> > >
> > > If you need information about some time value associated with the data in
> > > your table, though, you can't get Excel to do that for you if it is not there
> > > in the table. Unless there is a data field with the time you are looking
> > > for, or some sort of time stamp on the records that is made accessible to an
> > > outside query engine, there is nothing Excel (or any 3rd party app) can do -
> > > this would be an issue for the database end, not the Excel end.
> > >
> > > "Jim Ferguson" wrote:
> > >
> > > > I have a spread*** that gets data from using an SQL statement this is
> > > > updated with a macro and button so it can be refreshed at any time as this
> > > > info is time critical.
> > > >
> > > > I want to be able to separate the units ran within an hour by filler and
> > > > unit size.
> > > > I know how to do the above but can't find any info on how to download info
> > > > by the hour.
> > > > ie need to know how many units ran from 06:00 to 07:00.
> > > > When looking at the info from the database I can't find any reference to time.
> > > >
> > > > I am not an expert on SQL or Macros so need help.
> > > >
> > > > Can I use a macro to retrieve the data by the hour and if so how.
> > > > Would it be better to extend my SQL statement is so how.
> > > >
> > > >
.

Quantcast