Increment in query output

[cheuschober]

So I used the last hour or so looking for a similar query to no avail.

Thought I'd throw this to the wolves as I keep stumping myself somewhere around the DMax().

Basically we're converting an old 97 database with very bad keys and no naming system to a newly modeled 2000 db. In our old system we have a table [Table1] with a 3 field key [Year] [Site] [Class]. Because multiple entries of the same class can happen within the same site and the same year this has caused nothing but headaches. In the new model the key is [Year] [Site] [ClassID] where [ClassID] is an incremented byte that (I'm assuming) our data entry people will fill in by hand. The issue is transferring the 20 000 records or so over.

They (being the powers that be) want [classID] to reset to 1 with each [year] and [site].

So what I have gleaned thus far is that I should be able to use DMax()+1 to increment the field however I can't seem to find the correct argument as to just -what- is being DMax'ed. Since this isn't a form but a query that I'll eventually turn into an update I can't reference the control.

Of course the second part of the question is how I'm supposed to tell it to reset the count at each combination of [year] and [site] but I'm willing to sniff around a little longer on that one--would I be wrong to say the answer is to be found in the For... Each... Next???

Thanks,
~Chad

Edit: It should be noted that [year] and [site] are non-sequential numeric fields but [class] is a 3 character text field

 

[FoFa]

You could use a function that you feed Year and Site to as parameters, and it returns the next seq. number.
the function could user DCOUNT to get the current count of records for the Year & Site, then add 1 to it. If none are returned, it would be 0 + 1 or 1 (so the initial thing is handled).

Just a thought.

 

[cheuschober]

Thanks for the reply FoFa but I admit I'm a little vague as to what you meant.

I might have an idea, however, a good question to ask at this juncture if there is a way to force a DCount to count unique records only or do I need to create a stored query for that?

Regards,
~Chad

 

[cheuschober]

Actually, now that I think about it, I may not need the unique count.

Let me try this on for size and see if it fits. (Thank goodness our [Site] and [Year] are at the very least numeric):

Dim Y As Integer, MaxY As Integer, S As Byte, MaxS as Byte

Y = Min([Year]) 'Establishes the First Year on record
MaxY = Max([Year]) 'Establishes the Last Year on record

For Y To MaxY 
     S = DMin("[Site]","Table1","Y=[Year]") 'Establishes the first valid site for defined year
     MaxS = DMax("[Site]","Table1","Y=[Year]") 'Establishes the last valid site for defined year
     For S To MaxS
          .... '??? Code to to begin assigning abritrary seq. numbers to courses
     Next S
Next Y

Does that look like it would work? Granted the years wouldn't be starting at 1 but according to the helpfile, at least, it shouldn't have to start at one to use the counter...

Regards,
~Chad

Edit: Grr. I just relized this won't work because the sites are not sequential numbers. At least I don't think it will?

 

[FoFa]

Well I was thinking along the lines of:
FUNCTION GiveMeDaCount(YearIn as long, SiteIn as long) AS long
dim DaCount as Long
DaCount = DCOUNT("primaryKey","MyTable","[Site] = " & cstr(SiteIn) & " AND [Year] = " & cstr(YearIn))
GiveMeDaCount = DaCount + 1
End Function

 

[cheuschober]

Ahh... Okay it's starting to come together... and the easy way out of having to manually input all the parameters is through a parameter table that stores a static [Year] [Site] and [Class]. With update queries and a few looped macros I guess I could continually update the three key fields until there are no cases left.

Admittedly, though, it seem horridly inefficient.

Regards,
~Chad

 

[FoFa]

You could reference the function in an APPEND or UPDATE query
Oops, not update, only an Append.
To use it in an UPDATE you woulod have to add more criteria

 

[cheuschober]

Oi. I am struggling here folks.

It seems I'm stuck between using a temporary table with an autonumber field and using a Macro to append each [Site] and [Year] combination with the respective classes, then updating from the temporary table, deleting the data, using a function to reset the autonumber field back at 0 and repeating the process for the 300 or so combinations we have

or

Looping a macro 20 000 times (1 for each record) and appending the keys and the [ClassID] individually to a temporary table, then updating from my temporary table.

What's particularly frustrating after a straight 12 hours of research now, is that I can't seem to use vb to automate it. I would've thought that some kind of 'For ... Next' would have been able to run the loops.

Does anyone have an idea which of these options would be more efficient? Or maybe a third option?

Regards,
~Chad

 

[Bat17]

If I understand the problem then this should do it

Sub AddClass()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intYear As Integer
Dim lngSite As Long
Dim intClassID As Integer

strSQL = "SELECT table1.year, table1.Site, table1.ClassID FROM table1 ORDER BY table1.year, table1.Site"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
Do Until rs.EOF
If intYear = rs!Year And lngSite = rs!Site Then
intClassID = intClassID + 1
Else
intYear = rs!Year
lngSite = rs!Site
intClassID = 1
End If
rs.Edit
rs!classID = intClassID
rs.update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

HTH

Peter

 

[cheuschober]

Someone please shoot me for forgetting about recordsets!

*doof*

Thanks Peter! Yeah... that's the way to go!
~Chad