Combining Tables with Custom AutoNumber Formatting

Here is my dilema... I have about 13 Tables that I need to combine/append to another table once a month for reporting. I have tried to do a multiple table append query, but keep getting the message "Duplicate Output Destination Provider Number". All of the tables that need to be combined have the same table design. The only difference is that Autonumber field which is dependent on the type of provider getting the number. Example: If John is a Doctor he would get a number like "A12345" (Autoformat "A"00000), but Jane is a Physical Therapist and would get a number like "PT1234" (Autoformat "PT"0000) (These numbers are generated in sequential order). Does anyone know how I can combine these fields and have them hold the formatting assigned in the table? I am using Access 2000

Does Access 2000 allow the UNION operator? As an example, I would create a query that pulled that data from Table1. In that query, I would use the CONVERT function to change the autoformat field to VarChar. I'd duplicate that query for each table, and UNION the result sets:

SELECT CONVERT(VarChar(6), Field1), Field2 FROM Table1
UNION
SELECT CONVERT(VarChar(6), Field1), Field2 FROM Table2

I know access has a UNION Query, but I am not too informed on the SQL coding for those types of queries. The thing I am worried about and I am not sure if I would get the same result with your code; is that when I do an extract from any one single table to say an Excel spreadsheet. the format is lost example: Table Field "Provider Number" Format "PT"1234 When I export it to anything other than an access report. it shows up as 1234 instead of PT1234.

But if you do an Access query, is the correct formatting returned? If so, you can build a query to convert the value to Text on each table, then do your UNION with those queries.