×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Index table created with Make-table Query option

Index table created with Make-table Query option

Index table created with Make-table Query option

(OP)
After making a query in the design view and choosing the “Make-table Query” option from the Query menu, the query creates a table out of its results.  However, that table created by the first query is one that needs to be used in a second query so it must be indexed to be related to the other tables pulled into that second query.  How can I index a table that is created by the Make-table Query option?  

RE: Index table created with Make-table Query option


In Access 97 and 2000, create an index on a table with Data Definition query.

Create Index IdxName On TblName (ColName);

Read more in Access Help.

Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

RE: Index table created with Make-table Query option

(OP)
Thanks for the idea.  I am actually working in Access 2002. the line of code you mentioned does not seem to be accepted.  Maybe I have a syntax error and need quotes or something.  Here is what the help file says for CreateIndex, it is somewhat different than what you mentioned:
CreateIndex Method

Creates a new Index object (Microsoft Jet workspaces only).

Syntax

Set index = tabledef.CreateIndex (name)

The CreateIndex method syntax has these parts.

Part Description
index An object variable that represents the index you want to create.
tabledef An object variable that represents the TableDef object you want to use to create the new Index object.
name Optional. A Variant (String subtype) that uniquely names the new Index object. See the Name property for details on valid Index names.



Remarks

You can use the CreateIndex method to create a new Index object for a TableDef object. If you omit the optional name part when you use CreateIndex, you can use an appropriate assignment statement to set or reset the Name property before you append the new object to a collection. After you append the object, you may or may not be able to set its Name property, depending on the type of object that contains the Indexes collection. See the Name property topic for more details.

If name refers to an object that is already a member of the collection, a run-time error occurs when you use the Append method.

To remove an Index object from a collection, use the Delete method on the collection.


HERE Is the section of code I am working on:
Sub SendSBTInvoice(inv As Integer) 'based on Sub SendBWInvoice KS 4/01

    Dim dbs, db As Database
    Dim qry, qryUpd, qryInvUpd, qryLn As QueryDef
    Dim rst As Recordset
    Dim fHandle As Integer
    Dim strFileName As String
    Dim printfreight As String
    Dim idx As Index
    Dim tblDef, tblR As TableDef
    Dim idxOrderLineID As Index
    Dim fld As Field
    
    DoCmd.Hourglass True
    DoCmd.Echo False, "Open Databases"
    
    Set dbs = CurrentDb
    Set qryLn = dbs.QueryDefs("qryOrderHeadLine")
    
    Set rst = qryLn.OpenRecordset()
    Set tblR = rst.CreateTableDef("tblCmbOrdHeadLine")  'Followed example in printout from tec-tips webpage
                                'but it doesn't seem to accept qryLn in place of dbs so will try another option
    DoCmd.OpenTable "tblCmbOrdHeadLine"

    'Create an index and set its properties NEITER TRY NUMBER ONE NOR TWO SEEMED TO BE WORKING
    
    'TRY NUMBER ONE FROM SOMEONE ELSE'S QUESTUION ON TEC-TIPS
    'Set idx = tblR.CreateIndex("idxOrderLineID")
    'idx.Primary = True
    'idx.Required = True
    'idx.Unique = True
    
    'TRY NUMBER TWO FROM SOMEONE ELSE'S QUESTUION ON TEC-TIPS
    'Create Index "idxOrderLineID" On "tblCmbOrdHeadLine (OrderLineID)"
    
    'Add a field to the index  Not sure if this step is necessary
    Set fld = idx.CreateField("OrderLineID")
    idx.Fields.Append fld
    
    'Add the index to the TableDef
    tblR.Indexes.Append idx
        
    Set qry = dbs.QueryDefs("qrysbtinvoice")
    qry.PARAMETERS![invno] = inv
    Set rst = qry.OpenRecordset()
    
    DoCmd.Echo False, "Send Data To SBT"
    
    If rst.RecordCount > 0 Then
        rst.MoveFirst
        strFileName = lookup("filename", "tblfilelocation", "fileid", "SBTOUT")
        fHandle = stdFileOpen(strFileName, "Append")
        
        If fHandle > 0 Then
            'If rst!EDIFreight = True And rst!sumoftraderfreight > 0 Then
            If rst!BusinessWorksID = "Finger" Then  ' 10/20 Do only for FingerHut
                printfreight = rst!sumoftraderfreight
            Else
                printfreight = rst!sumoffreight
            End If
          
                 
            If rst!EDIFreight = True Then ' pass freight charges
              Write #fHandle, rst!SBTid, CInt(rst!InvoiceNo), "", CStr(rst!UPSShipDate), CDec(rst!sumofsellprice), CDec(rst!sumofhandcharge), CDec(printfreight)
            Else ' Don't pass freight charges
              If rst!BusinessWorksID = "12SPIEGEL" Then ' Pass handleing charge for Speigal 5/99
                 Write #fHandle, rst!SBTid, CInt(rst!InvoiceNo), CStr(rst!PurchaseOrderNum), CStr(rst!UPSShipDate), CDec(rst!sumofsellprice), CDec(rst!sumofhandcharge), ""
              Else
                 Write #fHandle, rst!SBTid, CInt(rst!InvoiceNo), CStr(rst!PurchaseOrderNum), CStr(rst!UPSShipDate), CDec(rst!sumofsellprice), "", ""
              End If
            End If
                        
     
            
            Close fHandle    ' Close file.
            DoCmd.Echo True
        
            DoCmd.Echo False, "Update Orders"
            Set qryUpd = dbs.QueryDefs("qryStatToDone")
            qryUpd.PARAMETERS![invno] = inv
            qryUpd.Execute 'incriments through records to change them
            
            DoCmd.Echo False, "Update Invoice"
            Set qryInvUpd = dbs.QueryDefs("qryInvoiceToDoneSBT")
            qryInvUpd.PARAMETERS![invno] = inv
            qryInvUpd.Execute 'incriments through records to change them
            
            MsgBox "Finished Processing SBT Output File"
        End If  'file opened
        dbs.Close
    Else
        MsgBox "No Reccords To Send"
    End If
    
    DoCmd.Echo True
    DoCmd.Hourglass False

End Sub

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close