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

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