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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 spreadsheet 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.
> > > >
> > > >
.

Relevant Pages

  • RE: (Excel 2007) Query Data in Database?
    ... are both headers of the Sheet in Excel, ... Is the ADDRESS field to much for the Microsoft Jet database engine? ... When you do a query you should be selecting the columns You want to ... the sort has to be one of these columns. ...
    (microsoft.public.excel.programming)
  • Re: Excel Access interface
    ... You could create a 'stored procedure' in the database. ... > You can create an External Data table in Excel that uses a parameter query. ... > prompt you to input values for each parameter that you entered. ...
    (microsoft.public.excel.programming)
  • To use parameter with like.
    ... I am attempting to establish a parameter in Excel containing a wildcard ... Wildcard Character Placement in MS Query ... I tested my posted LIKE clause using MS Query against an MS Access database. ...
    (microsoft.public.excel.programming)
  • Re: Split database into smaller db based on values
    ... Why have the Excel file "query the database"? ... "Jeff Boyce" wrote: ...
    (microsoft.public.access.modulesdaovba)
  • Re: How can I change the source of my extern source database in an
    ... Excel Blog - Daily Dose of Excel ... >>> that if he uses the query's he has to select an external database, ... >>>> click the edit query icon. ...
    (microsoft.public.excel.programming)