SQL Syntax

[Guest]

I have an Access "Make Table" query with the following SQL

SELECT [qspt Employment].Employer, [qspt Employment].EMPLNO, [qspt Employment].SURNAME, [qspt Employment].[FIRST NAME], [qspt Employment].[DATE OF EMPLOY EDITED], [qspt Employment].[DATE OF EMPLOY HYF], [qspt Employment].[INCOME CODE], [qspt Employment].[STARTING TARGET], [qspt Employment].[STARTING PAYMENT], [qspt Employment].[INCREMENT TARGET], [qspt Employment].[INCREMENT PAYMENT], [qspt Employment].[LAST UPDTE DATE], [qspt Employment].[END OF EMPL DATE-HYF], [qspt Employment].[EFF DATE EDIT], [qspt Employment].[EFF DATE HYF] INTO [tbl Supercuts Employment Date Query
FROM [qspt Employment]

Would it be possible for me to add some SQL to the above statement to

1: Change the data type for EMPLNO from text to numeric Long Integer
2: Create an Index (duplicates OK) on EMPLNO and INCOME CODE

If so, how would I go about doing it? Thanks!

 

[Tim Ferguson]

1: Change the data type for EMPLNO from text to numeric Long Integer

Yes: use a cast:

SELECT ... CINT(EmplNo) AS EmplNum ...

2: Create an Index (duplicates OK) on EMPLNO and INCOME CODE

No -- non-constraint indexes are part of the physical implementation and
are thus unknown to SQL. You will have to do this using vba code or the
GUI.

B Wishes


Tim F