So, Lets start out with scope.
I work in a plant that gathers thousands of lines of data minute.
my current QVF file has ~9 million lines of data and takes over 20 minutes to load (when I reload the data)
My coworkers spend hours manually filtering excel spreadsheets and working with an obsolete database.
I'm trying to combine a few assembly lines of data so I can cross reference part numbers in an assembly.
Currently I have an engine number "TPCEKxxxxx" (general serial number layout)
Head = "S"
Crank = "U"
Block = "P"
Block Sample Data
| Line | Station | Station Timestamp | Part Number | Model | Pass Fail | Part Status | Part Type |
| 2700 | 20.1 | 1/22/2018 4:27 | 0218P06260 | 336 | Pass | Online | Other |
| 2700 | 20.1 | 1/21/2018 15:46 | 0218P06070 | 336 | Pass | Online | Other |
| 2700 | 20.1 | 1/21/2018 15:36 | 0218P06250 | 336 | Pass | Online | Other |
Assembly Line Data Sample
| Line | Station | Station Timestamp | Part Number | Model | Pass Fail | Part Status | Part Type | Pallet Number | Equipment |
| 2880 | 20 | 1/22/2018 1:47 | TPCEK022810031 | P68229651AE | Pass | Online | Other | 108 | Depth 1 |
| 2880 | 20 | 1/22/2018 1:47 | TPCEK022810031 | P68229651AE | Pass | Online | Other | 108 | Depth 2 |
| 2880 | 20 | 1/22/2018 1:47 | TPCEK022810031 | P68229651AE | Pass | Online | Other | 108 | Force 1 |
| 2880 | 20 | 1/22/2018 1:47 | TPCEK022810031 | P68229651AE | Pass | Online | Other | 108 | Force 2 |
| 2880 | 20 | 1/22/2018 1:47 | 0148P04220 | 232 | Pass | Part Married | Other | 108 | None |
I want to Be able to choose a Block Number or an engine number and see all the other numbers associated with this. Timestamp is difficult due to it being different everywhere.
Currently I am using this code with a "key". I have a similar code for each didfferent sub component.
[Block Number]:
Load
[Station]&'-' & [Station Timestamp] as _key,
[Part Number] as [Block Number]
Resident [Sheet1]
where [Part Status] = 'Part Married' and match([Model],'336' ,'332', '232');
However, the cross referencing doesnt work both ways and doesnt seem to work very good.
Basically my problem is I have multiple part numbers under the "Part Number" column and I would like to match them up and cross reference them. Other columns like "Part Status" have a unique "Part Married"
Utlimately I would like a separate table that references all my other tables like:
Enigne Number Block number Crank Number Head number
TPCEK P U S etc.
