Query problem

[north-bc]

Hi all,

Not sure how to write this query and I was hoping someone would be able to help me.

I have an import file that contains a primary key and 16 other fields. The fields are name PID1, PID2, PID3, ... up to PID16. What I need to do is create a file that will have an autonumber as PK, the PK from the import file & the PID. I think this would be easier for future querying. The interesting thing is that not all records have 16 PID's. Most have 1, some have 2, etc. My question is, how do I create a query that will pull all the PID's into one file that has a single PID field.

Thanks in advance,
Todd

 

[gromit]

Hi Todd -

I tried to alias all the fields with the same name, but Access won't go for it.

The only other thing that I can think of is to do a separate query for each column (PID) and pull the non-blank fields out. E.g. extract all the PID1 records in Query1, all the PID2 records in Query2, etc. Then you could do a union of all the queries in a final version to convert to one file. (Just make sure that all your Query1, Query2... etc use the same field name "PID").

Sorry for not having anything brainier,

- g