ID changed when UNION queried

[maxik]

Hi all,
I have a UNION query of multiple tables:

Select * From tblCal_SlsAB
UNION
Select * From tblCal_SlsBC;

Both of them have Unique IDs as PK, such as: AB001, AB002, etc for tblCal_SlsAB and BC001, BC002, etc for tblCal_SlsBC. When I do the UNION query I get: 1, 2, etc for both IDs instead. What's causing the ID to change and is there a workaround it? I need to get the same exact IDs I have in those individual tables. Thanks in advance!

Max

 

[FoFa]

Try UNION ALL instead of UNION alone, see if that gets it.

 

[maxik]

FoFa
I tried it, didn't work. the reason is access is not storing the format of the primary key, which is "AB"000, it stores just the numbers in table and when union queried it pulls only the numbers. I was just wondering if there was a work around it?

 

[pbaldy]

Select "AB" & PrimaryKeyField AS FormattedPK, * From tblCal_SlsAB
UNION
Select "BC" & PrimaryKeyField AS FormattedPK, * From tblCal_SlsBC;

You may want to add a Format() in there to add the padded zeros.

 

[maxik]

Paul
Thanks for your help. I tried it and it worked, except I can't get those 0s in front of the ID. What's the syntax of Format?

 

[maxik]

pbaldy
I figured it out - format([field_name],'000') - and it worked. Thanks a lot for your help!