Find currently running product from a table

rotomarty

New Member
Joined
Jul 23, 2008
Messages
28
I have 6 machines that are producing boxes of product. There are 4 data collection points based on proprietary software that populate Access db's from scanners, downtime collection, in-motion scales, label printers and QC checks. I am trying to create a 'visual factory' demonstration that runs Excel queries against these Access tables and displays the job numbers, count of production, frequency of QC checks, etc. as a live dashboard. I am having two problems: <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
1. I have one of the tables that is sorted newest to oldest date and am trying to pick the last entry for each production machine from a column called ‘Line’ that has the 6 machines listed in order of events. If I can capture the first entry, that will be the current product SKU on that machine. They change SKU numbers anywhere from 1 hour to 3 apart and the machines have downtime which creates a random list as you scan down (i.e. 2,3,2,3,5,1,6,2,4,3,6,2,1,3,4). If I can somehow use a formula to identify the most recent entry, I can pull the other data from the corresponding job number across all tables.<o:p></o:p>
2. The Excel sheet is pulling from an external data query that is pulling from a query in Access that limits the information to 4 days history. I have to limit it because the weights from the in-line scales add up to 1.3MM lines every two weeks (that is a whole other project). Because of that link, the cells are returning the correct numbers but the underlying formula's are not typical. Like I am seeing =Table_QA_2007.accdb[[#This Row],[Batch]] when running SUMIFS criteria so I am getting errors when trying to use that cell data to run another formula against. For example the above formula is the first part of several criteria to match the job number and I think it is the lack of it being a number that is causing the # VALUE error?
<o:p></o:p>
Can these tasks be accomplished through a formula? I am not versed in VBA or macros and this will end up of being part of a SharePoint page displaying the dashboard with a few KPI indicators included. I am not sure how SP will handle VBA.<o:p></o:p>
<o:p></o:p>
There are 4 computers gathering the data with XP, no Office products installed. The Access tables on those machines are 2007 (.accdb). SharePoint is 2007. I am trying to make this work on a machine running Win7 and Office 2007.<o:p></o:p>
<o:p></o:p>
Any help will be awesome! Thanks.<o:p></o:p>
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top