Create Index in Make Table Query

[Brigitte P]

I'm trying to set a unique index in a Make Table Query. Here is the SQL Code
for my Make Table Query which works fine:

SELECT tblPatients.PatID AS RegNo, tblPatients.LName AS CliLName,
tblPatients.FName AS CliFName, IIf(tblPatients!Status="Open","Yes","No") AS
CliActive, tblPatients.Unit AS CliLivingArea, tblUnit.UnitName,
tblPatients.Birthdate AS CliDOB, tblPatients.Race AS CliRace,
tblRacePatients.Race, tblPatients.Sex AS CliSex, 729 AS AgencyCode INTO
CliCoreTable IN 'G:\Databases\BEClinical\ClientCoreData.mdb'
FROM tblRacePatients INNER JOIN (tblUnit INNER JOIN tblPatients ON
tblUnit.Unit = tblPatients.Unit) ON tblRacePatients.Code = tblPatients.Race;

I attempted to add before the ;

CREATE INDEX pat_id-ind ON CliCoreTable IN
'G:\Databases\BEClinical\ClientCoreData.mdb' ON (RegNo)

but get an error for the FROM clause.

The pat_id_ind is the index name and RegNo is the column name. The original
table has PatID as a unique value, thus the unique property is not
important.

I don't know much about SQL and don't know what's wrong. I need to create
this index, because the new table is part of a relationship with another
table and the relationship doesn't work without the index.

As always, thanks for any help.
Brigitte P

 

[Jeff Boyce]

If your new table is part of a relationship, it sounds like it is
well-defined enough to know that it participates in that relationship. If
true, have you considered creating a defined table structure and appending
records into it? That way, you could set the index on the table, as well as
its relationships to other tables, before putting any data in it.

If you need to "start fresh", use a delete query to empty it out before
appending a new set of records.

Or have I missed something?

 

[Guest]

Thank you, never thought of running a Delete and then an Append Query. It
works just fine. I still would like to know why my Create Index doesn't work
so I could learn the appropriate syntax. Nevertheless, my problem is solved.
THANKS.
Brigitte P

 

[John Vinson]

I still would like to know why my Create Index doesn't work
so I could learn the appropriate syntax.

Access (unlike Oracle and SQL/Server) does not support multiple SQL
statements in a single query. In SQL you would typically have a whole
series of DDL queries in one stream, executed as a unit. Unfortunately
Access does not support that syntax, except as a pass-through query to
SQL.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks.
Brigitte P

Access (unlike Oracle and SQL/Server) does not support multiple SQL
statements in a single query. In SQL you would typically have a whole
series of DDL queries in one stream, executed as a unit. Unfortunately
Access does not support that syntax, except as a pass-through query to
SQL.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps