Custom Counter

A

AccessIM

I have a table that tracks employee attendance records. In the table, I am
calculating an accumulative total column for points. This took me quite some
time to figure out but I finally did it and it works perfectly but now I am
seeing that using an autonumber field for this purpose is not the best
practice.

Currently, I have created two queries that recreate and renumber the
attendance records every week when the information is uploaded. The first is
a delete query that clears the table and the second is an append query that
re-enters the records in the correct order of EmployeeID and IncidentDate I
need and assigns an consecutive autonumber to each record. Here is the code
for the Append query:

INSERT INTO tblAttendanceDetail (EmployeeID, SSN, INCIDENTDATE, TYPE,
POINTVALUE)
SELECT qryTotalDetail.EmployeeID, qryTotalDetail.SSN,
qryTotalDetail.INCIDENTDATE, qryTotalDetail.TYPE, qryTotalDetail.POINTVALUE
FROM qryTotalDetail
ORDER BY qryTotalDetail.EmployeeID, qryTotalDetail.INCIDENTDATE DESC;

One other point, qryTotalDetail is a union query which pulls information
from four different tables.

Can anyone shed some light on creating a custom counter to a query to
possibly eliminate some of these steps? I have spent the morning reading and
trying different things but with no success. I am a beginner with writing
code so be gentle please. Thank you so much!
 
K

KARL DEWEY

an append query that re-enters the records in the correct order of
EmployeeID and IncidentDate
Why do you care the order they are in the table?

Try this select query ---
SELECT qryTotalDetail.EmployeeID, qryTotalDetail.SSN,
qryTotalDetail.INCIDENTDATE, qryTotalDetail.TYPE, qryTotalDetail.POINTVALUE,
(SELECT Count(*) FROM qryTotalDetail AS [XX] WHERE qryTotalDetail.EmployeeID
= [XX].EmployeeID AND qryTotalDetail.INCIDENTDATE <= [XX].INCIDENTDATE)+1 AS
Sequence_Number
FROM qryTotalDetail
ORDER BY qryTotalDetail.EmployeeID, qryTotalDetail.INCIDENTDATE DESC;
 
A

AccessIM

Thank you, Karl. I guess I thought the order of the table mattered to get
the results I wanted.

Here is how I would like the results to look and I have accomplished this
with the extra manipulation and autonumber:

EmployeeID SSN INCIDENTDATE TYPE POINTS
ACCUMTOTAL
10 111223333 7/4/09 NEG PTS -0.25
0.50
10 111223333 6/24/09 LATE IN 0.50
0.75
10 111223333 6/24/09 UNTIMELY 0.50
0.25
10 111223333 6/20/09 NEG PTS -0.25
-0.25
296 333445555 6/26/09 ABSENT 2.00
2.00

Creating it the way I did creates an autonumber field that is consecutive
from one record to the next.

When I used the code you suggested, I get this result:

EmployeeID SSN INCIDENTDATE TYPE POINTS
Seq_Num
10 111223333 7/4/09 NEG PTS -0.25
1
10 111223333 7/1/09 SHORTMEAL 0.00
2
10 111223333 6/27/09 FORGOTPUNCH 0.00
3
10 111223333 6/24/09 LATE IN
0.50 5
10 111223333 6/24/09 UNTIMELY 0.50
5
10 111223333 6/20/09 NEG PTS -0.25
6
296 333445555 6/29/09 ABSENT (S.I.) 0.00
1
296 333445555 6/26/09 ABSENT 2.00
2

The first thing I noticed was that my desired results does not include the
incidents that don't generate points for the employee. I could guess that
cleaning that up would be easy with a <>0 for criteria but I am afraid I am
not following the sequence number column. Would I use this in place of the
autonumber field when I do the accumulative total? Do you know why they
wouldn't be consecutive?

KARL DEWEY said:
EmployeeID and IncidentDate
Why do you care the order they are in the table?

Try this select query ---
SELECT qryTotalDetail.EmployeeID, qryTotalDetail.SSN,
qryTotalDetail.INCIDENTDATE, qryTotalDetail.TYPE, qryTotalDetail.POINTVALUE,
(SELECT Count(*) FROM qryTotalDetail AS [XX] WHERE qryTotalDetail.EmployeeID
= [XX].EmployeeID AND qryTotalDetail.INCIDENTDATE <= [XX].INCIDENTDATE)+1 AS
Sequence_Number
FROM qryTotalDetail
ORDER BY qryTotalDetail.EmployeeID, qryTotalDetail.INCIDENTDATE DESC;

--
Build a little, test a little.


AccessIM said:
I have a table that tracks employee attendance records. In the table, I am
calculating an accumulative total column for points. This took me quite some
time to figure out but I finally did it and it works perfectly but now I am
seeing that using an autonumber field for this purpose is not the best
practice.

Currently, I have created two queries that recreate and renumber the
attendance records every week when the information is uploaded. The first is
a delete query that clears the table and the second is an append query that
re-enters the records in the correct order of EmployeeID and IncidentDate I
need and assigns an consecutive autonumber to each record. Here is the code
for the Append query:

INSERT INTO tblAttendanceDetail (EmployeeID, SSN, INCIDENTDATE, TYPE,
POINTVALUE)
SELECT qryTotalDetail.EmployeeID, qryTotalDetail.SSN,
qryTotalDetail.INCIDENTDATE, qryTotalDetail.TYPE, qryTotalDetail.POINTVALUE
FROM qryTotalDetail
ORDER BY qryTotalDetail.EmployeeID, qryTotalDetail.INCIDENTDATE DESC;

One other point, qryTotalDetail is a union query which pulls information
from four different tables.

Can anyone shed some light on creating a custom counter to a query to
possibly eliminate some of these steps? I have spent the morning reading and
trying different things but with no success. I am a beginner with writing
code so be gentle please. Thank you so much!
Click to expand...
Click to expand...
 
K

KARL DEWEY

The first thing I noticed was that my desired results does not include the
incidents that don't generate points for the employee.
There is nothing in what I posted that would eliminate records that have
zero as POINTVALUE or that POINTVALUE is null.

The reason the sequence number field that is consecutive from one record to
the next is that the employee had two incidents on the same date, therefore a
tie.

A tie can be eliminated like this --
....POINTVALUE, (SELECT Count(*) FROM qryTotalDetail AS [XX] WHERE
qryTotalDetail.EmployeeID = [XX].EmployeeID AND qryTotalDetail.INCIDENTDATE &
qryTotalDetail.TYPE <= [XX].INCIDENTDATE & [XX].TYPE)+1 AS Sequence_Number


--
Build a little, test a little.


AccessIM said:
Thank you, Karl. I guess I thought the order of the table mattered to get
the results I wanted.

Here is how I would like the results to look and I have accomplished this
with the extra manipulation and autonumber:

EmployeeID SSN INCIDENTDATE TYPE POINTS
ACCUMTOTAL
10 111223333 7/4/09 NEG PTS -0.25
0.50
10 111223333 6/24/09 LATE IN 0.50
0.75
10 111223333 6/24/09 UNTIMELY 0.50
0.25
10 111223333 6/20/09 NEG PTS -0.25
-0.25
296 333445555 6/26/09 ABSENT 2.00
2.00

Creating it the way I did creates an autonumber field that is consecutive
from one record to the next.

When I used the code you suggested, I get this result:

EmployeeID SSN INCIDENTDATE TYPE POINTS
Seq_Num
10 111223333 7/4/09 NEG PTS -0.25
1
10 111223333 7/1/09 SHORTMEAL 0.00
2
10 111223333 6/27/09 FORGOTPUNCH 0.00
3
10 111223333 6/24/09 LATE IN
0.50 5
10 111223333 6/24/09 UNTIMELY 0.50
5
10 111223333 6/20/09 NEG PTS -0.25
6
296 333445555 6/29/09 ABSENT (S.I.) 0.00
1
296 333445555 6/26/09 ABSENT 2.00
2

The first thing I noticed was that my desired results does not include the
incidents that don't generate points for the employee. I could guess that
cleaning that up would be easy with a <>0 for criteria but I am afraid I am
not following the sequence number column. Would I use this in place of the
autonumber field when I do the accumulative total? Do you know why they
wouldn't be consecutive?

KARL DEWEY said:
an append query that re-enters the records in the correct order of
Click to expand...
EmployeeID and IncidentDate
Why do you care the order they are in the table?

Try this select query ---
SELECT qryTotalDetail.EmployeeID, qryTotalDetail.SSN,
qryTotalDetail.INCIDENTDATE, qryTotalDetail.TYPE, qryTotalDetail.POINTVALUE,
(SELECT Count(*) FROM qryTotalDetail AS [XX] WHERE qryTotalDetail.EmployeeID
= [XX].EmployeeID AND qryTotalDetail.INCIDENTDATE <= [XX].INCIDENTDATE)+1 AS
Sequence_Number
FROM qryTotalDetail
ORDER BY qryTotalDetail.EmployeeID, qryTotalDetail.INCIDENTDATE DESC;

--
Build a little, test a little.


AccessIM said:
I have a table that tracks employee attendance records. In the table, I am
calculating an accumulative total column for points. This took me quite some
time to figure out but I finally did it and it works perfectly but now I am
seeing that using an autonumber field for this purpose is not the best
practice.

Currently, I have created two queries that recreate and renumber the
attendance records every week when the information is uploaded. The first is
a delete query that clears the table and the second is an append query that
re-enters the records in the correct order of EmployeeID and IncidentDate I
need and assigns an consecutive autonumber to each record. Here is the code
for the Append query:

INSERT INTO tblAttendanceDetail (EmployeeID, SSN, INCIDENTDATE, TYPE,
POINTVALUE)
SELECT qryTotalDetail.EmployeeID, qryTotalDetail.SSN,
qryTotalDetail.INCIDENTDATE, qryTotalDetail.TYPE, qryTotalDetail.POINTVALUE
FROM qryTotalDetail
ORDER BY qryTotalDetail.EmployeeID, qryTotalDetail.INCIDENTDATE DESC;

One other point, qryTotalDetail is a union query which pulls information
from four different tables.

Can anyone shed some light on creating a custom counter to a query to
possibly eliminate some of these steps? I have spent the morning reading and
trying different things but with no success. I am a beginner with writing
code so be gentle please. Thank you so much!
Click to expand...
Click to expand...
Click to expand...
 
A

AccessIM

Oh my... I am going to do a little more testing from home tonight but I think
that is it! I cannot thank you enough for that knowledge, Karl. I have been
struggling with that for days and you were able to help me within hours of my
first post. I will also be able to carry this over to another database I
created in which I used the same procedure of deleting and recreating a table
over and over.

I appreciate all your help and assistance. You and your brain have been a
blessing to me! Thank you again!

KARL DEWEY said:
incidents that don't generate points for the employee.
There is nothing in what I posted that would eliminate records that have
zero as POINTVALUE or that POINTVALUE is null.

The reason the sequence number field that is consecutive from one record to
the next is that the employee had two incidents on the same date, therefore a
tie.

A tie can be eliminated like this --
...POINTVALUE, (SELECT Count(*) FROM qryTotalDetail AS [XX] WHERE
qryTotalDetail.EmployeeID = [XX].EmployeeID AND qryTotalDetail.INCIDENTDATE &
qryTotalDetail.TYPE <= [XX].INCIDENTDATE & [XX].TYPE)+1 AS Sequence_Number


--
Build a little, test a little.


AccessIM said:
Thank you, Karl. I guess I thought the order of the table mattered to get
the results I wanted.

Here is how I would like the results to look and I have accomplished this
with the extra manipulation and autonumber:

EmployeeID SSN INCIDENTDATE TYPE POINTS
ACCUMTOTAL
10 111223333 7/4/09 NEG PTS -0.25
0.50
10 111223333 6/24/09 LATE IN 0.50
0.75
10 111223333 6/24/09 UNTIMELY 0.50
0.25
10 111223333 6/20/09 NEG PTS -0.25
-0.25
296 333445555 6/26/09 ABSENT 2.00
2.00

Creating it the way I did creates an autonumber field that is consecutive
from one record to the next.

When I used the code you suggested, I get this result:

EmployeeID SSN INCIDENTDATE TYPE POINTS
Seq_Num
10 111223333 7/4/09 NEG PTS -0.25
1
10 111223333 7/1/09 SHORTMEAL 0.00
2
10 111223333 6/27/09 FORGOTPUNCH 0.00
3
10 111223333 6/24/09 LATE IN
0.50 5
10 111223333 6/24/09 UNTIMELY 0.50
5
10 111223333 6/20/09 NEG PTS -0.25
6
296 333445555 6/29/09 ABSENT (S.I.) 0.00
1
296 333445555 6/26/09 ABSENT 2.00
2

The first thing I noticed was that my desired results does not include the
incidents that don't generate points for the employee. I could guess that
cleaning that up would be easy with a <>0 for criteria but I am afraid I am
not following the sequence number column. Would I use this in place of the
autonumber field when I do the accumulative total? Do you know why they
wouldn't be consecutive?

KARL DEWEY said:
an append query that re-enters the records in the correct order of
EmployeeID and IncidentDate
Why do you care the order they are in the table?

Try this select query ---
SELECT qryTotalDetail.EmployeeID, qryTotalDetail.SSN,
qryTotalDetail.INCIDENTDATE, qryTotalDetail.TYPE, qryTotalDetail.POINTVALUE,
(SELECT Count(*) FROM qryTotalDetail AS [XX] WHERE qryTotalDetail.EmployeeID
= [XX].EmployeeID AND qryTotalDetail.INCIDENTDATE <= [XX].INCIDENTDATE)+1 AS
Sequence_Number
FROM qryTotalDetail
ORDER BY qryTotalDetail.EmployeeID, qryTotalDetail.INCIDENTDATE DESC;

--
Build a little, test a little.


:

I have a table that tracks employee attendance records. In the table, I am
calculating an accumulative total column for points. This took me quite some
time to figure out but I finally did it and it works perfectly but now I am
seeing that using an autonumber field for this purpose is not the best
practice.

Currently, I have created two queries that recreate and renumber the
attendance records every week when the information is uploaded. The first is
a delete query that clears the table and the second is an append query that
re-enters the records in the correct order of EmployeeID and IncidentDate I
need and assigns an consecutive autonumber to each record. Here is the code
for the Append query:

INSERT INTO tblAttendanceDetail (EmployeeID, SSN, INCIDENTDATE, TYPE,
POINTVALUE)
SELECT qryTotalDetail.EmployeeID, qryTotalDetail.SSN,
qryTotalDetail.INCIDENTDATE, qryTotalDetail.TYPE, qryTotalDetail.POINTVALUE
FROM qryTotalDetail
ORDER BY qryTotalDetail.EmployeeID, qryTotalDetail.INCIDENTDATE DESC;

One other point, qryTotalDetail is a union query which pulls information
from four different tables.

Can anyone shed some light on creating a custom counter to a query to
possibly eliminate some of these steps? I have spent the morning reading and
trying different things but with no success. I am a beginner with writing
code so be gentle please. Thank you so much!
Click to expand...
Click to expand...
Click to expand...
 
A

AccessIM

Hi Karl - It's me again.

I have been importing past data into the database week by week to simulate
how it will work when the database is live. Everything came in great for 6
weeks in a row with the sequence number adjusting for every new record that
came in for an employee. Then on the 7th week, the sequence number is out of
order on one employee. Here is what the results look like:

EmployeeID SSN IncidentDate Seq# Type Points
AccumTotal
6 111223333 8/13/09 1 LATE 0.50
1.00
6 111223333 8/5/09 0 LATE 0.50
1.50
6 111223333 7/30/09 2 LATE 0.50
0.50
6 111223333 4/6/98 3 START 0.00
0.00

Do you know why this would happen?

KARL DEWEY said:
It is just a 'typical' 'Ranking in a group' query.

--
Build a little, test a little.


AccessIM said:
Oh my... I am going to do a little more testing from home tonight but I think
that is it! I cannot thank you enough for that knowledge, Karl. I have been
struggling with that for days and you were able to help me within hours of my
first post. I will also be able to carry this over to another database I
created in which I used the same procedure of deleting and recreating a table
over and over.

I appreciate all your help and assistance. You and your brain have been a
blessing to me! Thank you again!

KARL DEWEY said:
The first thing I noticed was that my desired results does not include the
incidents that don't generate points for the employee.
There is nothing in what I posted that would eliminate records that have
zero as POINTVALUE or that POINTVALUE is null.

The reason the sequence number field that is consecutive from one record to
the next is that the employee had two incidents on the same date, therefore a
tie.

A tie can be eliminated like this --
...POINTVALUE, (SELECT Count(*) FROM qryTotalDetail AS [XX] WHERE
qryTotalDetail.EmployeeID = [XX].EmployeeID AND qryTotalDetail.INCIDENTDATE &
qryTotalDetail.TYPE <= [XX].INCIDENTDATE & [XX].TYPE)+1 AS Sequence_Number


--
Build a little, test a little.


:

Thank you, Karl. I guess I thought the order of the table mattered to get
the results I wanted.

Here is how I would like the results to look and I have accomplished this
with the extra manipulation and autonumber:

EmployeeID SSN INCIDENTDATE TYPE POINTS
ACCUMTOTAL
10 111223333 7/4/09 NEG PTS -0.25
0.50
10 111223333 6/24/09 LATE IN 0.50
0.75
10 111223333 6/24/09 UNTIMELY 0.50
0.25
10 111223333 6/20/09 NEG PTS -0.25
-0.25
296 333445555 6/26/09 ABSENT 2.00
2.00

Creating it the way I did creates an autonumber field that is consecutive
from one record to the next.

When I used the code you suggested, I get this result:

EmployeeID SSN INCIDENTDATE TYPE POINTS
Seq_Num
10 111223333 7/4/09 NEG PTS -0.25
1
10 111223333 7/1/09 SHORTMEAL 0.00
2
10 111223333 6/27/09 FORGOTPUNCH 0.00
3
10 111223333 6/24/09 LATE IN
0.50 5
10 111223333 6/24/09 UNTIMELY 0.50
5
10 111223333 6/20/09 NEG PTS -0.25
6
296 333445555 6/29/09 ABSENT (S.I.) 0.00
1
296 333445555 6/26/09 ABSENT 2.00
2

The first thing I noticed was that my desired results does not include the
incidents that don't generate points for the employee. I could guess that
cleaning that up would be easy with a <>0 for criteria but I am afraid I am
not following the sequence number column. Would I use this in place of the
autonumber field when I do the accumulative total? Do you know why they
wouldn't be consecutive?

:

an append query that re-enters the records in the correct order of
EmployeeID and IncidentDate
Why do you care the order they are in the table?

Try this select query ---
SELECT qryTotalDetail.EmployeeID, qryTotalDetail.SSN,
qryTotalDetail.INCIDENTDATE, qryTotalDetail.TYPE, qryTotalDetail.POINTVALUE,
(SELECT Count(*) FROM qryTotalDetail AS [XX] WHERE qryTotalDetail.EmployeeID
= [XX].EmployeeID AND qryTotalDetail.INCIDENTDATE <= [XX].INCIDENTDATE)+1 AS
Sequence_Number
FROM qryTotalDetail
ORDER BY qryTotalDetail.EmployeeID, qryTotalDetail.INCIDENTDATE DESC;

--
Build a little, test a little.


:

I have a table that tracks employee attendance records. In the table, I am
calculating an accumulative total column for points. This took me quite some
time to figure out but I finally did it and it works perfectly but now I am
seeing that using an autonumber field for this purpose is not the best
practice.

Currently, I have created two queries that recreate and renumber the
attendance records every week when the information is uploaded. The first is
a delete query that clears the table and the second is an append query that
re-enters the records in the correct order of EmployeeID and IncidentDate I
need and assigns an consecutive autonumber to each record. Here is the code
for the Append query:

INSERT INTO tblAttendanceDetail (EmployeeID, SSN, INCIDENTDATE, TYPE,
POINTVALUE)
SELECT qryTotalDetail.EmployeeID, qryTotalDetail.SSN,
qryTotalDetail.INCIDENTDATE, qryTotalDetail.TYPE, qryTotalDetail.POINTVALUE
FROM qryTotalDetail
ORDER BY qryTotalDetail.EmployeeID, qryTotalDetail.INCIDENTDATE DESC;

One other point, qryTotalDetail is a union query which pulls information
from four different tables.

Can anyone shed some light on creating a custom counter to a query to
possibly eliminate some of these steps? I have spent the morning reading and
trying different things but with no success. I am a beginner with writing
code so be gentle please. Thank you so much!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Custom Counter Question (Repost) 4
Adding data to MS Access from csv file 0
Append Crosstab Query to Existing Table 0
DSum Problem 7
DSum Problems 2
query field names changing to expr#: 8
AutoNumber Field Not Populating with Append Query 2
Time Frame Query 2

Top