looping query

[67flyer]

Hi, not sure looping is the correct word so i will try to explain what i am trying to do.

i have a tbl and need to mark the records in groups of 12. the first group would be in group 1. The next group of 12 would be 2. and so on. the highest number is unknown as the data will grow.

is this posible with a qry? or do i need a for next loop and run a qry each time? if i do that how do I incriment the to the next level.

Thanks.

 

[Banana]

I'm thinking you can't do that with a query. However, you can use a open recordset.

I don't have Access so I can't test the code and can't remember what syntax you need to move a specific number of records but at least you'll have the general idea.

Dim db As DAO.Database
Dim rst as DAO.Recordset
Dim GroupBookMark As Variant
Dim i As Integer

Set db = Currentdb
Set rst = Currentdb.OpenRecordset("YourTableNameHere")

With rst
    .MoveFirst

   Do Until .EOF
        .MoveNext 12 'Need to check the syntax here
        If Not .EOF Then
           GroupBookmark(i) = .Bookmark
           i=i + 1
        Else
           .MoveLast
           GroupBookMark(i) = .BookMark
         End IF
    Loop
End With

HTH.

 

[Karma]

It might be possible with SQL, what is the query at the moment - need the full sql here not a description - and what is the criteria to group the records into 12's?

 

[67flyer]

- banana, Will try that. However I don't program with any active x or doa componets. I've read that those componets must be installed and setup on each machine that would use the db. If wrong I probably could do some things easier in the db's i write.

- karma, don't have the qry yet. the records are grouped by department. so if a department had 30 people there would be 2 groups of 12 and 1 group of 6. group #'s being 1, 2, and 3.

I can give them a group # at the time i add them to the department (doing that now.) However if i remove some one from a previous group say group 1, now that group has only 11 and i need to fill that 12th slot. So i was looking to make a qry that would go through the department and regroup the people. making group 1 have 12, 2 have 12 and now 3 would have 5. hope that is a little clearer.

maybe i don't need the qry. on the subform that list people, if one is deleted then loop through the subform records using something close to what banana said. (I already check for the dmax of the group # on adding a person. )

Thanks for the help guys.

 

[Banana]

67flyer, normally, it's not a problem as long you're consistent with what you use. "DLL hell" commonly occurs whenever you try to use two different library version. It is more likely to be true if you have other custom in-house applications. If you do have such applications, you will want to check with your IT guys on what version they use and make sure you use the same version in your database. Check your VBA's Tool -> References to see what is set as default.

I assumed that you were using Access as both backend and frontend, which if is the case, DAO is the best choice. Otherwise ADO may be more suited to your need.

As to your needs, the harder I think, I can't think up of a query. Karma may be able to correct me (and I'd sure like to know if there was a way to do that! ), and would think open recordset is probably the best solution. You also could dynamically assign the group# to each record by adding another loop.

Here's an example:

Dim db As DAO.Database
Dim rst as DAO.Recordset
Dim i As Integer
Dim Counter As Integer

Set db = Currentdb
Set rst = Currentdb.OpenRecordset("YourTableNameHere")
i=1

With rst
    .MoveFirst

   Do Until .EOF
        For Counter= 1 to 12
           If Not .EOF Then
                !GroupNumber = i
                .MoveNext
           Else
               Exit Loop
           End If
        Next Counter
        i=i + 1
    Loop
End With

 

[67flyer]

After you mentioned it in your first post it got me thinking and now that you posted the second code, i was getting ready to try some thing like that on the subform that adds them to the group.

Thanks,

 

[67flyer]

tried using the code but can't dim the database. Not using DAO. If i use DAO and then the db gets put on another pc will it keep the DAO settings? what if they are not installed on the other pc?

 

[Banana]

I don't know what version of Access you have.

You need to check the references in your VBA: Tools -> References. In my code, I'm assuming DAO Library 3.6.

 

[67flyer]

its not checked right now the only things check are:
visual basic for applications
ms access 11.o object library
ole automation
ms activex data objects 2.1 library
ms windows common controls 6.0 sp6

If i check the dao library does it go with the database when moved to another system or do you have to check the dao on every system that you put the database on.

if you have to check the dao on every system, is there code that can do it. the end users i give the databases to would get lost trying to check the dao.

thats why i have never used them. and code using the default references that are installed with access. If there is a way to include the references with the databases, i would like to know how to do that. It would make coding easier and cleaner.

thanks,

 

[Banana]

11... That means you have Access 2000...

It looks like Access 2000 has ADO set as default.

I've never had the opportunity to find out if database inherit the references and whether it can be set programically. Maybe someone else can answer that.

However, if you are so concerned, you can just use ADO instead. All that means is some different syntax. I know that elbweb has posted a good example of how ADO works... Found it

 

[67flyer]

okay, thanks! will have to check on access 2000 system also to see if ADO is check by default. I normaly program on a access 2000 system.

Thank you very much!

 

[pbaldy]

This is quick and dirty but it's not hard to do it all with SQL. First create a field that increments 1 for each record (not hard with a DCount() on a unique field). Then:

GroupNum: Int([IncrementField]/12.01)+1

That will assign 1 to the first 12, 2 to the second 12, etc.

 

[Banana]

Cool. Thanks for SQL lesson.

 

[67flyer]

pdaldy, i put the groupnum in a qry and it updated every record to 1 they were 0. there was 26 records.

Probably not coding it correctly. the incrementfield is the field in the table?

 

[pbaldy]

No, I said to create an increment field. Here's a sample, and the "key" field I incremented on is a date field (in this table, there can only be one record per day):

SELECT Dor_date, DCount("dor_date","TableName","dor_date <=#" & [dor_date] & "#") AS Increment, Int([Increment]/12.01)+1 AS GroupNum
FROM TableName;

In my query, the increment field goes from 1 up to whatever, and the groupnum field groups as noted earlier.

 

[67flyer]

Sorry I don't use SQL code alot. This is what I did:

SELECT accountletter, DCount("accountletter","tbluser","[accountletter]=forms!frmsubaccounts!accountletter") AS Increment, Int([Increment]/12.01)+1 AS rptlevel
FROM tbluser;

the keys in this tbl are accountletter and lname. the rptlevel is the field that needs to have 12 (1), (2) and so on. I get an error saying that it can't find the item in the where clause of the dcount command.

 

[pbaldy]

See here for the proper syntax of the DCount:

http://www.mvps.org/access/general/gen0018.htm

Also, I don't think you want to refer to a form field, you want to refer to the current row in the query, like I did. Otherwise, every record will return the same thing.

 

[67flyer]

changed it to:
SELECT accountletter, DCount("RPTLEVEL","tbluser","RPTLEVEL=" & [RPTLEVEL]) AS Increment, Int([Increment]/12.01)+1 AS groupnum
FROM tbluser;

no errors but it list all the accountletters, (just need the one that I am on.)
the increment #'s are 30, 9, 7, 3 and the groupnum are 1 and 3's. if i change the select accountletter to select rptlevel then i get an error saying i am looping.

I don't think i have the right fields on the increment portion.
I have the following fields in the table:
accountletter (k)
lnameloc (k)
fname
famchart
manager
rptlevel

for any given accountletter there can only be 12 people per level. so when accountletter P, rptlevel 1 has 12 then the next 12 will be accountletter P, rptlevel 2 and so on.

to update I would replace the SELECT with UPDATE?

Thanks for the help paul, when it comes to SQL i stumble.

 

[pbaldy]

Is the goal here to populate that rptlevel field in the table? Can you post a sample db?

 

[67flyer]

Yes, rptlevel needs to be updated.

I give them a rptlevel at the time i add them to the department (doing that now.) Now if i remove someone from the department, if that person was in rptlevel 1 and there are 3 rptlevels, rptlevel 1 has only 11 people and i need to fill that 12th slot. I Need the code to update (requery/renumber/redistribute) all the rptlevels for the department, making rptlevel 1 have 12, rptlevel 2 have 12 and now 3 would have 5.

It is a big db I will try to make a sample db.