SQL Table view autonumber

[aron.ridgway]

Hi There, i have created a Union sql Table View, that i have connected to in access. Its a list from 5 different companines of Supplier accounts. I need to create an autonumber for this table? Not sure if this may be more SQL based than access, but any pointers would be greatly appreciated!!

SELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'RWL' AS Company
FROM Roth.dbo.PLSupplierAccount
UNION ALL
SELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'FAH' AS Company
FROM FAH.dbo.PLSupplierAccount
UNION ALL
SELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'RFG' AS Company
FROM RFG.dbo.PLSupplierAccount
UNION ALL
SELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'RFP' AS Company
FROM RFP.dbo.PLSupplierAccount
UNION ALL
SELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'RFW' AS Company
FROM ALOPS.dbo.PLSupplierAccount

 

[plog]

That's not a table, its a query. You cannot create autonumber fields for queries. Company and AccountID of this query should give you a unique identifier for the records of this query.

 

[aron.ridgway]

The only problem is the companies use 5 different Sage accounts and some of the suppliers have been set up via a Sage auto generated number and some have been Manually written using text? e.g 0000000112233 or RDFEF. So i wanted to create a column so i could have a standardised format? thanks Aron

 

[plog]

I have no idea what you are talking about. I know nothing of Sage, nor what your data looks like. And then you threw question marks into statements and completely lost me.

I assume AccountID is unique to each account in its own table, correct? That plus the Company field you created would make each record in the query you created unique.