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

[Jim Ferguson]

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.

 

[K Dales]

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]

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]

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]

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.

 

[Jim Ferguson]

K Dales

Just a thought but could I write a marco or sql statement that would
download units produced at a specific time

ie

at 07:00 update and retreive hprod(from Database)
return to A1
at 08:00 update and retreive hprod
return to B1

as hprod is all I need as this is the counter for how much is produced.

I could then set up excel to use this data.

separate data by line number and how much is produced taking away units
produced from previous hour.

If this is possible how would I code it.

Yours scraping the bottom of the barrel now.