Update query - sequential numbering

[Mackster66]

MSAccess 2003
Windows XP Pro
Access front end, SQL back end

I have a table "EVMASTER" that contains evidence information. There is a
field "OCA" for the case number for each record. There can be mulitiple
records with the same case number. Each record is assigned an sequential
item number "ITEM" based on "OCA" during entry. Each new "OCA" starts the
count back at 1 for that case number. Other important fields in this table
are "ID" which is an autonumber that is also used for barcoding and
"DESCRIPT" which is a text description of the evidence. New entries are
completed on a form that contains a large amount of VBA.

Problems started occurring when the database was opened to multiple users on
a network. There is a problem with the code where multiple users entering a
large amount of evidence for one case number will cause duplicate item
numbers within that case number. It's not a big deal...it's just confusing
for the attorneys. I'm working on a fix for that, but I also need to go back
and fix the duplicate item numbers.

Can this be done with an update query based on a select query that finds the
duplicates? The select query I'm using to find duplicates is as follows...

SELECT EVMASTER.OCA, EVMASTER.ITEM, EVMASTER.ID, EVMASTER.DESCRIPT
FROM EVMASTER
WHERE (((EVMASTER.ITEM) In (SELECT [ITEM] FROM [EVMASTER] As Tmp GROUP BY
[oca],[item] HAVING Count(*)>1 And [OCA] = [EVMASTER].[OCA])))
ORDER BY EVMASTER.OCA, EVMASTER.ITEM;

Any ideas would be appreciated.

 

[vanderghast]

If you want to get the records, without dup, you can make a total query:

SELECT a, b, c, d, e
FROM somewhere
GROUP BY a, b, c, d, e

and if the fields 'd' and 'e' are not really important, for uniqueness
(example: who is the clerc who enter the information, or at which date and
time it has been, or the primary key of the record) :


SELECT a, b, c, LAST(d), LAST(e)
FROM somewhere
GROUP BY a, b, c

where you note the d and e have been removed from the GROUP BY clause. Using
LAST indicates to use the value for d and e from the same record (the last
one seen when building the group, accordingly to the execution plan the
query execution optimizer will come with).

If you want to note sequentially the dup, so listing all of them, not just
ANY ONE of them as in the previous case, then you can use a COUNT:

SELECT a.*, (SELECT COUNT(*)
FROM evmaster AS b
WHERE a.OCA=b.OCA
AND a.ITEM=b.ITEM
AND a.ID >= b.ID) AS sequentialNumberForDup
FROM evmaster AS a


where here, I assume there is a DUP if two records have the same OCA and
ITEM value. I used their ID value (different for any records) to sequence
them.




Vanderghast, Access MVP

 

[Mackster66]

But how can I turn it into an update query so that Access will pull all of
the OCAs containing duplicate ITEM numbers and renumber the ITEM sequentially
within each OCA?

If you want to get the records, without dup, you can make a total query:

SELECT a, b, c, d, e
FROM somewhere
GROUP BY a, b, c, d, e

and if the fields 'd' and 'e' are not really important, for uniqueness
(example: who is the clerc who enter the information, or at which date and
time it has been, or the primary key of the record) :


SELECT a, b, c, LAST(d), LAST(e)
FROM somewhere
GROUP BY a, b, c

where you note the d and e have been removed from the GROUP BY clause. Using
LAST indicates to use the value for d and e from the same record (the last
one seen when building the group, accordingly to the execution plan the
query execution optimizer will come with).

If you want to note sequentially the dup, so listing all of them, not just
ANY ONE of them as in the previous case, then you can use a COUNT:

SELECT a.*, (SELECT COUNT(*)
FROM evmaster AS b
WHERE a.OCA=b.OCA
AND a.ITEM=b.ITEM
AND a.ID >= b.ID) AS sequentialNumberForDup
FROM evmaster AS a


where here, I assume there is a DUP if two records have the same OCA and
ITEM value. I used their ID value (different for any records) to sequence
them.




Vanderghast, Access MVP

MSAccess 2003
Windows XP Pro
Access front end, SQL back end

I have a table "EVMASTER" that contains evidence information. There is a
field "OCA" for the case number for each record. There can be mulitiple
records with the same case number. Each record is assigned an sequential
item number "ITEM" based on "OCA" during entry. Each new "OCA" starts the
count back at 1 for that case number. Other important fields in this
table
are "ID" which is an autonumber that is also used for barcoding and
"DESCRIPT" which is a text description of the evidence. New entries are
completed on a form that contains a large amount of VBA.

Problems started occurring when the database was opened to multiple users
on
a network. There is a problem with the code where multiple users entering
a
large amount of evidence for one case number will cause duplicate item
numbers within that case number. It's not a big deal...it's just
confusing
for the attorneys. I'm working on a fix for that, but I also need to go
back
and fix the duplicate item numbers.

Can this be done with an update query based on a select query that finds
the
duplicates? The select query I'm using to find duplicates is as
follows...

SELECT EVMASTER.OCA, EVMASTER.ITEM, EVMASTER.ID, EVMASTER.DESCRIPT
FROM EVMASTER
WHERE (((EVMASTER.ITEM) In (SELECT [ITEM] FROM [EVMASTER] As Tmp GROUP BY
[oca],[item] HAVING Count(*)>1 And [OCA] = [EVMASTER].[OCA])))
ORDER BY EVMASTER.OCA, EVMASTER.ITEM;

Any ideas would be appreciated.

 

[vanderghast]

Change the select query into a make table query through the toolbar?

Vanderghast, Access MVP

But how can I turn it into an update query so that Access will pull all of
the OCAs containing duplicate ITEM numbers and renumber the ITEM
sequentially
within each OCA?

If you want to get the records, without dup, you can make a total query:

SELECT a, b, c, d, e
FROM somewhere
GROUP BY a, b, c, d, e

and if the fields 'd' and 'e' are not really important, for uniqueness
(example: who is the clerc who enter the information, or at which date
and
time it has been, or the primary key of the record) :


SELECT a, b, c, LAST(d), LAST(e)
FROM somewhere
GROUP BY a, b, c

where you note the d and e have been removed from the GROUP BY clause.
Using
LAST indicates to use the value for d and e from the same record (the
last
one seen when building the group, accordingly to the execution plan the
query execution optimizer will come with).

If you want to note sequentially the dup, so listing all of them, not
just
ANY ONE of them as in the previous case, then you can use a COUNT:

SELECT a.*, (SELECT COUNT(*)
FROM evmaster AS b
WHERE a.OCA=b.OCA
AND a.ITEM=b.ITEM
AND a.ID >= b.ID) AS sequentialNumberForDup
FROM evmaster AS a


where here, I assume there is a DUP if two records have the same OCA and
ITEM value. I used their ID value (different for any records) to sequence
them.




Vanderghast, Access MVP

MSAccess 2003
Windows XP Pro
Access front end, SQL back end

I have a table "EVMASTER" that contains evidence information. There is
a
field "OCA" for the case number for each record. There can be
mulitiple
records with the same case number. Each record is assigned an
sequential
item number "ITEM" based on "OCA" during entry. Each new "OCA" starts
the
count back at 1 for that case number. Other important fields in this
table
are "ID" which is an autonumber that is also used for barcoding and
"DESCRIPT" which is a text description of the evidence. New entries
are
completed on a form that contains a large amount of VBA.

Problems started occurring when the database was opened to multiple
users
on
a network. There is a problem with the code where multiple users
entering
a
large amount of evidence for one case number will cause duplicate item
numbers within that case number. It's not a big deal...it's just
confusing
for the attorneys. I'm working on a fix for that, but I also need to
go
back
and fix the duplicate item numbers.

Can this be done with an update query based on a select query that
finds
the
duplicates? The select query I'm using to find duplicates is as
follows...

SELECT EVMASTER.OCA, EVMASTER.ITEM, EVMASTER.ID, EVMASTER.DESCRIPT
FROM EVMASTER
WHERE (((EVMASTER.ITEM) In (SELECT [ITEM] FROM [EVMASTER] As Tmp GROUP
BY
[oca],[item] HAVING Count(*)>1 And [OCA] = [EVMASTER].[OCA])))
ORDER BY EVMASTER.OCA, EVMASTER.ITEM;

Any ideas would be appreciated.