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
fficeffice" /><o></o>
<o></o>
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></o>
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></o>
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></o>
<o></o>
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></o>
<o></o>
Any help will be awesome! Thanks.<o></o>
fficeffice" /><o></o><o
></o>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
></o>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
></o>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
></o><o
></o>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
></o><o
></o>Any help will be awesome! Thanks.<o
></o>
