So are you saying that for example the JIPCABLENO column is either blank or only ever has DMPX-XXA in it - at least at the drawing level?
The problem is your data is not normalised so you are going to need a pretty complex query
Intitially try
Code:
SELECT DISTINCT FileName, JIP1CABLENO
FROM myTable
WHERE JIP1CABLENO IS NOT NULL
if this gives the correct result, save it as qryJIP1CABLENO
write and save another query for the next column and so on, until all columns are done
Next, write a different query - simply
Code:
SELECT DISTINCT FileName
FROM myTable
and save it as qrymaster
finally a query to put it all together
open the query designer and drag qrymaster into the grid, then drag qryJIP1CABLENO onto the grid and make a join between filename in the qrymaster and filename in qryJIP1CABLENO. Right click on the join and select option 2 (include all records from qrymaster and only those from qryJIP1CABLENO where they are equal - or words to that effect). Then drag filename in qryMaster and JIP1CABLENO from qryJIP1CABLENO to the grid at the bottom.
Now click on the SQL button to view the sql generated - it should look something like
Code:
SELECT qryMaster.Filename, qryJIP1CABLENO.JIP1CABLENO
FROM qryMaster LEFT JOIN qryJIP1CABLENO ON qryMaster.FileName=qryJIP1CABLENO.FileName
All set? run the query - does this give the result you expect for these two columns? I would expect to see all the filenames and DMPX-XXA in the JIP1CABLENO column or blank where this filename does not use the JIP1CABLENO column.
If all OK, return to the query design and drag qryJIP1DCVSCABINET onto the grid, make a join between qryMaster and this query as before, right click, select option 2 and then drag JIP1DCVSCABINET to the grid below (no need to do the filename, you've already got that).
Now repeat for each of the other queries
This should produce what you want
Windows 8 
Access 2003
Reply With Quote
Originally Posted by Ajax
