2 fields 1 record change to 2 records in new query

[cdw0308]

I have an existing table that has a field cblcode1 and cblcode2. cblcode1 always has data in it and cblcode2 is null sometimes and has data on other records.

I need to create a query that can output the single record into multiple records when cblcode1 and cblcode2 both have data in it. As well as show all cblcode1 records

ex.
current table
autonum cblcode1 cblcode2
1 452d 452c
2 532a 532b
3 562f <null>

new query

autonum cblcode
1 452d
2 452c
3 532a
4 532b
5 562f

Any help in creating this query would be greatly appreciated.

 

[Golom]

select cblcode1 As cblcode from thetable

union all

select cblcode2 As cblcode from thetable
where cblcode2 IS NOT NULL

 

[Golom]

Forgot to add ...

To get your AutoNumber field you can use the above as input to a maketable query like this

Select cblCode INTO myNewTable

From (

select cblcode1 As cblcode from thetable

union all

select cblcode2 As cblcode from thetable
where cblcode2 IS NOT NULL) As X

and then create an AutoNumber column

ALTER TABLE myNewTable ADD COLUMN AutoNum Counter(1,1)

Unfortunately you can't create an on-the-fly Autonum field in a query.