Matching fields in different records

[Guest]

I have created a database that my department uses to record credits and
invoices. The records for each are stored in the same table and I need a way
to search different records to match reason codes.

If a user creates a credit for a given reason, they should create the
subsequent invoice for the same reason. This does not always happen and I
need a query to locate those mismatched reason codes.

Each record has a unique record ID but the matching will have to be based on
the specific invoice number, as this would be the same for both records.
What is the easiest way to search invoice numbers and compare reason codes
for each record? I want the report to list all record numbers that do not
have matching reason codes. Please advise.

 

[John W. Vinson]

I have created a database that my department uses to record credits and
invoices. The records for each are stored in the same table and I need a way
to search different records to match reason codes.

If a user creates a credit for a given reason, they should create the
subsequent invoice for the same reason. This does not always happen and I
need a query to locate those mismatched reason codes.

Each record has a unique record ID but the matching will have to be based on
the specific invoice number, as this would be the same for both records.
What is the easiest way to search invoice numbers and compare reason codes
for each record? I want the report to list all record numbers that do not
have matching reason codes. Please advise.

You don't post the actual structure of your table so it's a bit hard
to answer. You can create a Query joining your invoice table to the
credits table by the invoice number; and put a criterion on the reason
code in the Credits table such as

<> [Invoices].[ReasonCode]

using your actual table and field names.

I must question why you have the reason code redundantly in two
different tables (which is the basic cause of your problem). Does a
reason code refer to an Invoice? or to a Credit? Might it not make
more sense to store the reason code only in *one* table, and use a
query joining the two tables to look it up?

John W. Vinson [MVP]

 

[Guest]

Hi John,

The values for reason codes are stored in a separate table and used for
lookup values. The problem is that users (although they have been instructed
not to) can change the reason in the drop-down for subsequent requests on the
same invoice number.

If a user specifies a reason for a credit, they should use that same reason
for the corresponding invoice request. I wanted to create a query that finds
these discrepancies.

I did create a "find duplicates" query (see pasted SQL below) that at least
allows me to quickly view the reason code to ensure they match. However, I
would like to take this one step further and identify those duplicate
invoices where the reasons don't match. Can this be done?

SELECT tblTransactionRequest.[Invoice#],
tblTransactionRequest.TransactionID, tblTransactionRequest.ReasonCode,
tblTransactionRequest.InvoiceRequest, tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest, tblTransactionRequest.Requestor,
tblTransactionRequest.RequestDate
FROM tblTransactionRequest
WHERE (((tblTransactionRequest.[Invoice#]) In (SELECT [Invoice#] FROM
[tblTransactionRequest] As Tmp GROUP BY [Invoice#] HAVING Count(*)>1 )) AND
((tblTransactionRequest.ReasonCode)<>"Manual Invoice") AND
((tblTransactionRequest.RequestDate) Between [Start date of report] And [End
date of report]))
ORDER BY tblTransactionRequest.[Invoice#], tblTransactionRequest.RequestDate;

Thanks,
Jim

I have created a database that my department uses to record credits and
invoices. The records for each are stored in the same table and I need a way
to search different records to match reason codes.

If a user creates a credit for a given reason, they should create the
subsequent invoice for the same reason. This does not always happen and I
need a query to locate those mismatched reason codes.

Each record has a unique record ID but the matching will have to be based on
the specific invoice number, as this would be the same for both records.
What is the easiest way to search invoice numbers and compare reason codes
for each record? I want the report to list all record numbers that do not
have matching reason codes. Please advise.

You don't post the actual structure of your table so it's a bit hard
to answer. You can create a Query joining your invoice table to the
credits table by the invoice number; and put a criterion on the reason
code in the Credits table such as

<> [Invoices].[ReasonCode]

using your actual table and field names.

I must question why you have the reason code redundantly in two
different tables (which is the basic cause of your problem). Does a
reason code refer to an Invoice? or to a Credit? Might it not make
more sense to store the reason code only in *one* table, and use a
query joining the two tables to look it up?

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John,

The values for reason codes are stored in a separate table and used for
lookup values. The problem is that users (although they have been instructed
not to) can change the reason in the drop-down for subsequent requests on the
same invoice number.

Again:

Is a Reason an attribute of an Invoice? Or of a Credit? Or of both?

If it's an attribute of both entities, then - logically - it should be
permissible to have different reason codes in the two tables.

If the reason codes must be the same, then the reason code SHOULD NOT
EXIST in the invoice table. You can use a Combo Box to *display* the
reason code from the Credit table on the form, but there is no good
reason to store it redundantly in both tables.

If a user specifies a reason for a credit, they should use that same reason
for the corresponding invoice request. I wanted to create a query that finds
these discrepancies.

Maybe I don't understand your table structure (well, you haven't
posted it, so there's no way that I could). Do you have a table of
Invoices? If not, why not? Isn't an Invoice a real-life entity?

I did create a "find duplicates" query (see pasted SQL below) that at least
allows me to quickly view the reason code to ensure they match. However, I
would like to take this one step further and identify those duplicate
invoices where the reasons don't match. Can this be done?

SELECT tblTransactionRequest.[Invoice#],
tblTransactionRequest.TransactionID, tblTransactionRequest.ReasonCode,
tblTransactionRequest.InvoiceRequest, tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest, tblTransactionRequest.Requestor,
tblTransactionRequest.RequestDate
FROM tblTransactionRequest
WHERE (((tblTransactionRequest.[Invoice#]) In (SELECT [Invoice#] FROM
[tblTransactionRequest] As Tmp GROUP BY [Invoice#] HAVING Count(*)>1 )) AND
((tblTransactionRequest.ReasonCode)<>"Manual Invoice") AND
((tblTransactionRequest.RequestDate) Between [Start date of report] And [End
date of report]))
ORDER BY tblTransactionRequest.[Invoice#], tblTransactionRequest.RequestDate;

How about

SELECT A.Invoice#, A.ReasonCode, B.ReasonCode, <other fields as
needed>
FROM TransactionRequest AS A
INNER JOIN TransactionRequest AS B
ON A.[Invoice#] = B.[Invoice#]
WHERE A.TransactionID < B.TransactionID
AND A.ReasonCode <> B.ReasonCode
AND A.ReasonCode<>"Manual Invoice"
AND B.ReasonCode<>"Manual Invoice"
AND A.RequestDate Between [Start date of report]
And [End date of report]
AND B.RequestDate Between [Start date of report]
And [End date of report]
ORDER BY A.[Invoice#],A.RequestDate;


John W. Vinson [MVP]

 

[Guest]

Hi John,

I understand your point but unfortunately, the conditions are a little more
complicated. A user can create a credit only with a reason, an invoice only
with a reason, or a credit and invoice for the same customer. In the case of
related requests, the reasons need to match. Unfortunately, with these three
scenarios, I cannot limit the field to only credits.

The user needs the ability to specify reason codes for either credits or
invoices but should use the same reason when processing both for a customer.
I hope this answers your question. I realize it can be confusing, as it even
confuses the people in my department.

As I said before, the find duplicates query is adequate because it allows me
to quickly view records for the same invoice number and each reason code. If
the reason codes do not match, I can easily see the difference and go to the
appropriate record to correct the discrepancy.

However, I would like to modify my query to only display duplicate invoice
numbers that do not have matching reason codes. If the answer is too
complicated, I can use what I have already created. I am simply trying to
simplify the error-checking process. Thank you so much for your timely
responses.

Sincerely,
Jim

Hi John,

The values for reason codes are stored in a separate table and used for
lookup values. The problem is that users (although they have been instructed
not to) can change the reason in the drop-down for subsequent requests on the
same invoice number.

Again:

Is a Reason an attribute of an Invoice? Or of a Credit? Or of both?

If it's an attribute of both entities, then - logically - it should be
permissible to have different reason codes in the two tables.

If the reason codes must be the same, then the reason code SHOULD NOT
EXIST in the invoice table. You can use a Combo Box to *display* the
reason code from the Credit table on the form, but there is no good
reason to store it redundantly in both tables.

If a user specifies a reason for a credit, they should use that same reason
for the corresponding invoice request. I wanted to create a query that finds
these discrepancies.

Maybe I don't understand your table structure (well, you haven't
posted it, so there's no way that I could). Do you have a table of
Invoices? If not, why not? Isn't an Invoice a real-life entity?

I did create a "find duplicates" query (see pasted SQL below) that at least
allows me to quickly view the reason code to ensure they match. However, I
would like to take this one step further and identify those duplicate
invoices where the reasons don't match. Can this be done?

SELECT tblTransactionRequest.[Invoice#],
tblTransactionRequest.TransactionID, tblTransactionRequest.ReasonCode,
tblTransactionRequest.InvoiceRequest, tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest, tblTransactionRequest.Requestor,
tblTransactionRequest.RequestDate
FROM tblTransactionRequest
WHERE (((tblTransactionRequest.[Invoice#]) In (SELECT [Invoice#] FROM
[tblTransactionRequest] As Tmp GROUP BY [Invoice#] HAVING Count(*)>1 )) AND
((tblTransactionRequest.ReasonCode)<>"Manual Invoice") AND
((tblTransactionRequest.RequestDate) Between [Start date of report] And [End
date of report]))
ORDER BY tblTransactionRequest.[Invoice#], tblTransactionRequest.RequestDate;

How about

SELECT A.Invoice#, A.ReasonCode, B.ReasonCode, <other fields as
needed>
FROM TransactionRequest AS A
INNER JOIN TransactionRequest AS B
ON A.[Invoice#] = B.[Invoice#]
WHERE A.TransactionID < B.TransactionID
AND A.ReasonCode <> B.ReasonCode
AND A.ReasonCode<>"Manual Invoice"
AND B.ReasonCode<>"Manual Invoice"
AND A.RequestDate Between [Start date of report]
And [End date of report]
AND B.RequestDate Between [Start date of report]
And [End date of report]
ORDER BY A.[Invoice#],A.RequestDate;


John W. Vinson [MVP]

 

[John W. Vinson]

However, I would like to modify my query to only display duplicate invoice
numbers that do not have matching reason codes. If the answer is too
complicated, I can use what I have already created. I am simply trying to
simplify the error-checking process. Thank you so much for your timely
responses.

My suggestion should do that. Did you try it?

John W. Vinson [MVP]

 

[Guest]

Hi John,

No, I could not get your SQL to work for me. I am new to SQL and just do
not have the experience needed to make it work. I will use my existing query
until I can find a better way but, I do appreciate your assistance.

Thanks again,
Jim