Code Check Request - Query to find reused values

  • Thread starter Thread starter Tokyo Alex
  • Start date Start date
T

Tokyo Alex

Dear all,

I have two tables, POs and Orders. POs represent purchase orders from our
client, and Orders represent internal orders. One PO can be related to many
orders.
Each order has a Sales Rep (data in a different table, ID in Orders as FK).
The PO number is not a unique ID in the client order data (as one PO can
cover many separate client orders).

Graphically, the structure looks something like this (* indicates a unique
ID):

POs Orders Reps
Client Order ID* ORDER ID*
PO Code <---------> PO NUM
SALES REP ID <------- SALES REP ID*

In theory, each PO should relate to one and only one SR. However, it seems
that the client may be reusing old PO numbers with different SRs.

I created the following query to identify whether this is actually happening:

SELECT PONUM, REPNUM
FROM
(SELECT [POs].[PO Code] AS PONUM, [Orders].[SALES REP ID] AS REPNUM
FROM [POs] INNER JOIN [Orders] ON [POs].[PO Code] = [Orders].[PO NUM]
GROUP BY [POs].[PO Code], [Orders].[SALES REP ID]) AS Q
GROUP BY PONUM, REPNUM
HAVING count(PONUM) > 1;

My logic:
The subquery generates unique combinations of PO Number and SR, and the
outer query identifies any duplicates of PO Number. (i.e. PONUM:REPNUM =/=
1:1)

My questions:
1) Does this code actually do what I think it does?
2) Am I reinventing the wheel here?

Any advice or suggestions will be much appreciated.

Thanks,
Alex.
 
Your table structure looks sound. Is the combinantion of the PO NUM and SALES
REP ID fields in the Orders table a unique index? They should be.

If they are reusing stuff, are there date fields in any of the tables where
you could see this happening. I'd think that an OrderDate field with the
default value of Date() or Now() might help.
 
Hi Jerry,

Thanks for the response.

Unfortunately, the PO NUM and SALES REP ID is not a unique index, and I have
no ability to make it one (ODBC Linked table controlled by a different
department). Also, the Orders table holds orders from clients other than the
one I'm dealing with, and I guess there is an outside chance that a
legitimate duplicate could occur if another client has similar PO numbers.

There are various date fields in both the client (PO) and internal (Orders)
table. Using the same query design as below, I've tested these fields also.

In all cases, the query returns no records. Which means either that PO
numbers are not being reused, or my query logic is faulty. I hope it's the
former <g>

Thanks again,
Alex.

Jerry Whittle said:
Your table structure looks sound. Is the combinantion of the PO NUM and SALES
REP ID fields in the Orders table a unique index? They should be.

If they are reusing stuff, are there date fields in any of the tables where
you could see this happening. I'd think that an OrderDate field with the
default value of Date() or Now() might help.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Tokyo Alex said:
Dear all,

I have two tables, POs and Orders. POs represent purchase orders from our
client, and Orders represent internal orders. One PO can be related to many
orders.
Each order has a Sales Rep (data in a different table, ID in Orders as FK).
The PO number is not a unique ID in the client order data (as one PO can
cover many separate client orders).

Graphically, the structure looks something like this (* indicates a unique
ID):

POs Orders Reps
Client Order ID* ORDER ID*
PO Code <---------> PO NUM
SALES REP ID <------- SALES REP ID*

In theory, each PO should relate to one and only one SR. However, it seems
that the client may be reusing old PO numbers with different SRs.

I created the following query to identify whether this is actually happening:

SELECT PONUM, REPNUM
FROM
(SELECT [POs].[PO Code] AS PONUM, [Orders].[SALES REP ID] AS REPNUM
FROM [POs] INNER JOIN [Orders] ON [POs].[PO Code] = [Orders].[PO NUM]
GROUP BY [POs].[PO Code], [Orders].[SALES REP ID]) AS Q
GROUP BY PONUM, REPNUM
HAVING count(PONUM) > 1;

My logic:
The subquery generates unique combinations of PO Number and SR, and the
outer query identifies any duplicates of PO Number. (i.e. PONUM:REPNUM =/=
1:1)

My questions:
1) Does this code actually do what I think it does?
2) Am I reinventing the wheel here?

Any advice or suggestions will be much appreciated.

Thanks,
Alex.
Click to expand...
Click to expand...
 
Back
Top