add primary key to an Access Make Table Union Query? 1

[sjck]

I am a novice when it comes to Access SQL – I have the following Make table query. The query as written provides the results I need (not sure exactly how) but I now need to add a primary key to table (autonumber) How would I do this and what would the code look like? Any help is appreciated. Thank you very much..


SELECT o.[Claim Uid] AS [Claim Uid],
o.[Emp Zip] AS [Emp Zip],
o.Zip AS Zip,
o.[Place Of Srvc Cd] AS [Place Of Srvc Cd],
o.[Srvc Prv Ctg Cd] AS [Srvc Prv Ctg Cd],
o.[Srvc Prv Bus Cd] AS [Srvc Prv Bus Cd],
o.Product AS Product,
o.[Srvc Cd] AS [Srvc Cd],
o.[Calc Cvrd Chg Amt] AS [Calc Cvrd Chg Amt],
o.[Calc Cvrd Amt] AS [Calc Cvrd Amt],
o.[Ptnt Birth Dt] AS [Ptnt Birth Dt],
o.[Srvc From Dt] AS [Srvc From Dt],
o.[Age in Years] AS [Age in Years],
o.[Diag1 Cd] AS [Diag1 Cd],
o.[Diag2 Cd] AS [Diag2 Cd],
o.[Diag3 Cd] AS [Diag3 Cd],
o.[calc diag1] AS [calc diag1],
o.[ICD9 Chapter] AS [ICD9 Chapter],
o.[Cpt4 Cd] AS [Cpt4 Cd],
o.[Calc Srvc Qty] AS [Calc Srvc Qty],
o.[Payee Cd] AS [Payee Cd],
o.[Srvc Prv Billed Tax Id] AS [Srvc Prv Billed Tax Id],
o.[Srvc Prv Line1 Nm] AS [Srvc Prv Line1 Nm],
o.POS AS POS,
o.[Emp Id] AS [Emp Id],
o.[Emp St Cd] AS [Emp St Cd],
o.[Invnty Nbr] AS [Invnty Nbr],
o.[Policy Nbr] AS [Policy Nbr],
o.[Ptnt Nbr] AS [Ptnt Nbr],
o.[Ptnt First Nm] AS [Ptnt First Nm],
o.[Ptnt Rel Cd] AS [Ptnt Rel Cd],
o.[Dep Nbr] AS [Dep Nbr] INTO [ALL CLAIMS]

FROM (SELECT o.[Claim Uid],
o.[Emp Zip],
o.Zip,
o.[Place Of Srvc Cd],
o.[Srvc Prv Ctg Cd],
o.[Srvc Prv Bus Cd],
o.Product,
o.[Srvc Cd],
o.[Calc Cvrd Chg Amt],
o.[Calc Cvrd Amt],
o.[Ptnt Birth Dt],
o.[Srvc From Dt],
o.[Age in Years],
o.[Diag1 Cd],
o.[Diag2 Cd],
o.[Diag3 Cd],
o.[calc diag1],
o.[ICD9 Chapter],
o.[Cpt4 Cd],
o.[Calc Srvc Qty],
o.[Payee Cd],
o.[Srvc Prv Billed Tax Id],
o.[Srvc Prv Line1 Nm],
o.POS,
o.[Emp Id],
o.[Emp St Cd],
o.[Invnty Nbr],
o.[Policy Nbr],
o.[Ptnt Nbr],
o.[Ptnt First Nm],
o.[Ptnt Rel Cd],
o.[Dep Nbr]
FROM [Source: Claims in 939 and 950 zips] o

UNION SELECT T.[Claim Uid],
t.[Emp Zip],
t.Zip,
t.[Place Of Srvc Cd],
t.[Srvc Prv Ctg Cd],
t.[Srvc Prv Bus Cd],
t.Product,
t.[Srvc Cd],
t.[Calc Cvrd Chg Amt],
t.[Calc Cvrd Amt],
t.[Ptnt Birth Dt],
t.[Srvc From Dt],
t.[Age in Years],
t.[Diag1 Cd],
t.[Diag2 Cd],
t.[Diag3 Cd],
t.[calc diag1],
t.[ICD9 Chapter],
t.[Cpt4 Cd],
t.[Calc Srvc Qty],
t.[Payee Cd],
t.[Srvc Prv Billed Tax Id],
t.[Srvc Prv Line1 Nm],
t.POS,
t.[Emp Id],
t.[Emp St Cd],
t.[Invnty Nbr],
t.[Policy Nbr],
t.[Ptnt Nbr],
t.[Ptnt First Nm],
t.[Ptnt Rel Cd],
t.[Dep Nbr]
FROM [Source: Costal provider claims] t) AS [%$##@_Alias];

 

[PHV]

SQL code:
ALTER TABLE [ALL CLAIMS] ADD COLUMN myID COUNTER(1,1);

VBA code:
CurrentDB.Execute "ALTER TABLE [ALL CLAIMS] ADD COLUMN myID COUNTER(1,1)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, missed the Primary key stuff:
SQL code:
ALTER TABLE [ALL CLAIMS] ADD COLUMN myID COUNTER(1,1) CONSTRAINT PrimaryKey PRIMARY KEY;;

VBA code:
CurrentDB.Execute "ALTER TABLE [ALL CLAIMS] ADD COLUMN myID COUNTER(1,1) CONSTRAINT PrimaryKey PRIMARY KEY"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sjck]

Where do I add this code? Sorry, I am a novice to Access and have not figured out VB vs SQL code and where to add it...

 

[sjck]

oops -- can I add it to the orginal query or do I have to do something else

 

[PHV]

How is your MakeTable query executed ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sjck]

I created a Make-table query under the query section in Access by choosing the Make-Table query under the query type icon. Then I went to the SQL view and added the Union Select portion of the statement.

To execute the Make-Table Query, I double click on the query under the query section of Access.

I hope this is what you are asking.

Thank you for your assistance...

 

[PHV]

As you seem to do all the stuff manually why not doing what you want in the Table design view ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sjck]

I tried to add a primary key to the design table view, but I receive the following error "File Sharing Lock Count Exceeded. Increase MaxLockPerFile Registry Entry.

If there is a place to put the query to automatically execute, I would be willing to do it, I just don't know where I would put this information, as I have only done it manually..

Thanks again for your help.

 

[PHV]

Create a new query without any table selected and go in the SQL view pane to write the ALTER TABLE instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886