Offset in Access Query?

[foxhound]

:P Hello, this is my first post and I am new to coding so I need a lot of help :D I have an access query that has the following columns:

EMPNO EMPNAME DBA NUMHRS DATE INCDNT

EMPNO = NUMBER
EMPNAME = NAME
DBA = NUMBER
NUMHRS = NUMBER
DATE = DATE
INCDNT = Y OR N (just used for querying...all in this query are "N")

The columns are sorted in ascending order first by EMPNO then by DATE.

Is there a way to... have access look at the first EMPNO and if DBA is <> 999 then put a 1 in a new column then look at the second record in EMPNO. If this is EMPNO is the same as the previous, and DATE is equal to the date plus one in Record one then put a 1 in the new column field also. If the date is not equal to the previous date plus 1, then put a 2 in the new column field for that row. The number in the new column field would increment by 1 each time the date is not equal to the previous date plus one until a new EMPNO is reached. Then it would loop through this EMPNO and do the same until a new EMPNO is reached and continue until all records in the query have been looked at.

Thank you in advance for any assistance you can offer.

Foxhound

 

[Russell Hauf]

What you want to do can be done, but it will require VBA.

A few questions:

-What is this "new column" that you want? Would you mind adding it to your table?

-What if you have something like below:

<table border="1"> <col width=64 span=2 style='width:48pt'> <tr height=14 style='height:10.5pt'><td height=14 class=xl22 width=64 style='height:10.5pt;width:48pt'>EMPNO</td><td class=xl22 width=64 style='border-left:none;width:48pt'>DATE</td> </tr> <tr height=14 style='height:10.5pt'><td height=14 class=xl22 style='height:10.5pt;border-top:none' x:num>1</td><td class=xl23 style='border-top:none;border-left:none' x:num="37681">01-Mar</td> </tr> <tr height=14 style='height:10.5pt'><td height=14 class=xl22 style='height:10.5pt;border-top:none' x:num>1</td><td class=xl23 style='border-top:none;border-left:none' x:num="37683">03-Mar</td> </tr> <tr height=14 style='height:10.5pt'><td height=14 class=xl22 style='height:10.5pt;border-top:none' x:num>1</td><td class=xl23 style='border-top:none;border-left:none' x:num="37684">04-Mar</td> </tr> <tr height=14 style='height:10.5pt'><td height=14 class=xl22 style='height:10.5pt;border-top:none' x:num>1</td><td class=xl23 style='border-top:none;border-left:none' x:num="37685">05-Mar</td> </tr> <tr height=14 style='height:10.5pt'><td height=14 class=xl22 style='height:10.5pt;border-top:none' x:num>1</td><td class=xl23 style='border-top:none;border-left:none' x:num="37687">07-Mar</td> </tr> <tr height=14 style='height:10.5pt'><td height=14 class=xl22 style='height:10.5pt;border-top:none' x:num>1</td><td class=xl23 style='border-top:none;border-left:none' x:num="37688">08-Mar</td> </tr> <tr height=14 style='height:10.5pt'><td height=14 class=xl22 style='height:10.5pt;border-top:none' x:num>1</td><td class=xl23 style='border-top:none;border-left:none' x:num="37692">12-Mar</td> </tr> </table>

Would you want the following results?

<table border="1" height="184"> <col width=64 span=2 style='width:48pt'> <tr height=14 style='height:10.5pt'><td height=18 class=xl22 width=64 style='width: 48pt'>EMPNO</td><td class=xl22 width=64 style='width: 48pt; border-left-style: none; border-left-width: medium' height="18">DATE</td><td class=xl22 width=64 style='width: 48pt; border-left-style: none; border-left-width: medium' height="18">NewNum</td> </tr> <tr height=14 style='height:10.5pt'><td height=17 class=xl22 style='border-top-style: none; border-top-width: medium' x:num>1</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37681" height="17">01-Mar</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37681" height="17">1</td> </tr> <tr height=14 style='height:10.5pt'><td height=16 class=xl22 style='border-top-style: none; border-top-width: medium' x:num>1</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37683" height="16">03-Mar</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37683" height="16">1</td> </tr> <tr height=14 style='height:10.5pt'><td height=17 class=xl22 style='border-top-style: none; border-top-width: medium' x:num>1</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37684" height="17">04-Mar</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37684" height="17">2</td> </tr> <tr height=14 style='height:10.5pt'><td height=17 class=xl22 style='border-top-style: none; border-top-width: medium' x:num>1</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37685" height="17">05-Mar</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37685" height="17">3</td> </tr> <tr height=14 style='height:10.5pt'><td height=17 class=xl22 style='border-top-style: none; border-top-width: medium' x:num>1</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37687" height="17">07-Mar</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37687" height="17">1</td> </tr> <tr height=14 style='height:10.5pt'><td height=17 class=xl22 style='border-top-style: none; border-top-width: medium' x:num>1</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37688" height="17">08-Mar</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37688" height="17">2</td> </tr> <tr height=14 style='height:10.5pt'><td height=17 class=xl22 style='border-top-style: none; border-top-width: medium' x:num>1</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37692" height="17">12-Mar</td><td class=xl23 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' x:num="37692" height="17">1</td> </tr> </table>

Thank you,

Russell

 

[foxhound]

[b]Hello! [/b]Thanks for your reply.

The new column would be as you have "New Num" or "INCNUM" and it makes no difference if I have it in the table. Here is more along the lines that I am looking for (I am unsure of how to post in columns so I seperated them with "---"):

EMPNO DATE INCNUM
1 ----- 01-Mar ----- 1
1 ----- 03-Mar ----- 2
1 ----- 04-Mar ----- 2
1 ----- 05-Mar ----- 2
1 ----- 07-Mar ----- 3
1 ----- 08-Mar ----- 3
1 ----- 12-Mar ----- 4
2 ----- 01-Mar ----- 1
2 ----- 02-Mar ----- 1
2 ----- 06-Mar ----- 2
2 ----- 04-Apr ----- 3
3 ----- 03-Mar ----- 1

Please let me know if you need additional details

Tommy

 

[Russell Hauf]

Glad I put my example there as I obviously did not read your original post correctly! I'm a bit busy right now but I'll try to get to this later today.

In the mean time, anyone is free to jump in here!

Hang in there, we'll get you what you want.

-Russell

 

[foxhound]

Thanks Russell

...waiting patiently :D

Tommy

 

[Russell Hauf]

Ok, I have found a solution, but it is not super easy. What version of Access are you using?

Thank you,

rh

 

[foxhound]

Great!

I am using Access 2000.

Tommy

 

[Russell Hauf]

Here is the easy way. The other way I did it involved a make-table query and then another query. This should be easier.

First, open your table in Design View and add a new field named "IncNum". You can rename this later, or if you want to name it something else to start with, change the constant that refers to IncNum in the code below. Make this field a number (it should default to Long Integer).

Then change "MrXl030327" in the code below to the name of your table. And if the fields EMPNO and DATE are named differently, you'll need to change each case of those. I would also recommend against naming a field DATE as it is also a function that returns today's date (in the code below, I showed how you can refer to fields in a recordset by using .Fields("FieldName") or !FieldName. But you cannot use !Date because VBA will think you mean something other than a field name).

If the ADODB part gives you trouble, just take out "ADODB." and try that.

<html><pre><font color='#000000'>
<font color='#000080'>Option</font> <font color='#000080'>Compare Database
Option Explicit</font>
<pre><font color='#000000'>
<hr align=left width=500><font color='#000080'>Private</font> Const cstrMyTable <font color='#000080'>As</font> <font color='#000080'>String</font> = "MrXl030327"
<font color='#000080'>Private</font> Const cstrMyIncField <font color='#000080'>As</font> <font color='#000080'>String</font> = "IncNum"<hr align=left width=500>
<font color='#000080'>Sub</font> MrXL030327()

<font color='#000080'>Dim</font> rst <font color='#000080'>As</font> ADODB.Recordset
<font color='#000080'>Dim</font> dtLast <font color='#000080'>As</font> Date
<font color='#000080'>Dim</font> lngEmpLast <font color='#000080'>As</font> <font color='#000080'>Long</font>
<font color='#000080'>Dim</font> lngIncNum <font color='#000080'>As</font> <font color='#000080'>Long</font>

<font color='#000080'>Set</font> rst = New ADODB.Recordset

rst.Open "Select * FROM " & cstrMyTable & " ORDER BY EMPNO, DATE", CurrentProject.Connection, adOpenKeyset, adLockPessimistic

lngIncNum = 1

With rst
.MoveFirst
.Fields(cstrMyIncField).Value = lngIncNum
dtLast = .Fields("DATE")
lngEmpLast = .Fields("EMPNO")
.MoveNext
<font color='#000080'>Do</font> <font color='#000080'>While</font> <font color='#000080'>Not</font> .EOF
Debug.Print .Fields("DATE")
<font color='#000080'>If</font> .Fields("EMPNO") = lngEmpLast <font color='#000080'>Then</font>
<font color='#000080'>If</font> .Fields("DATE") = dtLast + 1 <font color='#000080'>Then</font>
<font color='#008000'>' do nothing - keep the same IncNum</font>
<font color='#000080'>Else</font>
lngIncNum = lngIncNum + 1
<font color='#000080'>End</font> <font color='#000080'>If</font>
<font color='#000080'>Else</font>
lngIncNum = 1
<font color='#000080'>End</font> <font color='#000080'>If</font>
dtLast = .Fields("DATE")
lngEmpLast = !EMPNO

.Fields(cstrMyIncField) = lngIncNum
.MoveNext
<font color='#000080'>Loop</font>
<font color='#000080'>End</font> With

rst.Close
<font color='#000080'>Set</font> rst = Nothing
<font color='#000080'>End</font> <font color='#000080'>Sub</font></font></pre><hr align=left width=500></html>

HTH,

Russell

 

[foxhound]

Worked Perfectly!!!

Russell, you have just made my night, weekend, and month! Thank you soooooo much for putting this code together for me!

Tommy