How can I add an index field to a table?

[Jon]

I have a table of temporary employee data.
I must produce a 3 page report where each page has a different layout and the
employee printed is different on each page.

report #1 report #2 report #3

empoyee #1 employee #6 employee #16
#2... to #5. #7...#15 #17....#25

The order of employee must be alphabetical.

I have managed to cludge something together with a series of queries using a
combination of 'top' and the Access 'Unmatched Query Wizard'. It is a bit
unweildy and would be difficult for someone else to come along at a later
time and manage.

I think I could do this a lot better if I could add an index field to an
alphabetized table I created with a make table query. Then I could do a
query using, for example, "between 6 and 15" but I can't figure out how to
add the field to the table which would become the index to query.

Is adding a field like this possible?

Thanks, jon

 

[Jeff Boyce]

I'm confused...

I thought you stated that you need an "alphabetical" sort. What will having
a "sequence number" allow you to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jon]

I'm confused...

I thought you stated that you need an "alphabetical" sort. What will having
a "sequence number" allow you to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to print the first 5 alpha names on report #1
I need to print the next 10 alpha names no report #2 and so on. Each report
is different and so I need to select an range of names that is arranged
alphabetical to go on the report.
Report #1 is easy, I use top 5.
After that I have used the unmatched query wizard.

I was hoping to add a field that out number the records 1-25 so that I can
query on that field to produce the results I need.

Sorry to be so confusing,
Jon

 

[Jeff Boyce]

Jon

Are you saying you want to add a new field to the table definition so you
can "set" a sequence number (?index)? If so, won't you have to revisit the
entire table every time a name is added, edited, or removed?

Perhaps it would be sufficient for you to have your report "count" the
number of records it's used and move to the next page when its printed all
it should? This makes it more of a "reports" question than a "query"
question.


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jon]

Perhaps it would be sufficient for you to have your report "count" the
number of records it's used and move to the next page when its printed all
it should? This makes it more of a "reports" question than a "query"
question.

I didn't know that could be done, it sounds perfect.
I'll take my question to the other forum.

Thanks!
Rgds, Jon

 

[raskew via AccessMonster.com]

Interesting problem. The following will create a query that returns a
specified group of records from a sorted (either alpha or numeric) table or
query. Try pasting to a new module, then call it as described.

Public Sub SelMid2(ptblName As String, pItem As String, _
pNum As Integer, pOffset As Integer, _
pUpDown As Boolean, Optional qname As String)
'*************************************************************
' PURPOSE: Creates query to retrieve a specified segment
' (e.g. records 6 - 15) from a sorted table or query.
' CODED BY: raskew
' PARAMETERS:
' ptblName - Name of table/query as string
' pItem - The field to sort on
' pNum - Number of records to return
' pOffset - Starting point minus 1
' pUpDown - Sort order. True = ascending, False = descending
' qName - Optional name of query to be created. If missing,
' Default = qryXOXOX
' EXAMPLE (test from debug window):
' To return records 6-15 from Query3, sorted on FullName, into query
nTest2
' call selmid2("query3", "fullname", 10, 5, True, "nTest2")

Dim db As Database
Dim rs As Recordset
Dim qd As QueryDef
Dim strSQL As String
Dim test As String
Dim tName As String
Dim varHold As Variant

Set db = CurrentDb
'get upper parameter
strSQL = "SELECT TOP " & pOffset & " " & pItem & " FROM " & ptblName & "
ORDER BY " & pItem & IIf(pUpDown, "", " Desc")
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)
rs.MoveLast
varHold = rs(pItem)
Debug.Print varHold
rs.Close

'create SELECT TOP query, predicated on varHold

strSQL = "SELECT TOP " & pNum & " " & pItem & " FROM " & ptblName
strSQL = strSQL & " WHERE " & pItem & IIf(pUpDown, " > ", " < ") & IIf
(VarType(varHold) = 8, "'", "") & varHold & IIf(VarType(varHold) = 8, "'", "")

strSQL = strSQL & " ORDER BY " & pItem & IIf(pUpDown, "", " Desc")
Debug.Print strSQL

'Create query def
On Error Resume Next
tName = IIf(IsMissing(qname), "qryXOXOX", qname)
'Does query tName exist? If true, delete it;
test = db.QueryDefs(tName).Name
If Err <> 3265 Then
docmd.DeleteObject acQuery, tName
End If

'Create/recreate query tName
Set qd = db.CreateQueryDef(tName, strSQL)
db.QueryDefs.Refresh
rs.Close
db.Close
Set db = Nothing

End Sub


HTH - Bob