Make Table Query help?

[GD]

I have a table that consists of credit memos (Field 1) and their original
invoices (Fields 2-41, if any). The trick is that there could be anywhere
from 0 to 40 fields of original invoices. Is there an expression that would:

1) Return an invoice number if all non-blank fields match each other
exactly, and
2) Return no result (i.e. blank) if any of the them differ from any of the
others?

Example:

Results Wanted CM_No OrigInv1 OrigInv2 OrigInv3 OrigInv4
CM004268 ED629227 ED629227 ED062399 ED062399
EF587543 CM006417 EF587543 EF587543 EF587543 EF587543

Thanks!

 

[Philip Herlihy]

I have a table that consists of credit memos (Field 1) and their original
invoices (Fields 2-41, if any). The trick is that there could be anywhere
from 0 to 40 fields of original invoices. Is there an expression that would:

1) Return an invoice number if all non-blank fields match each other
exactly, and
2) Return no result (i.e. blank) if any of the them differ from any of the
others?

Example:

Results Wanted CM_No OrigInv1 OrigInv2 OrigInv3 OrigInv4
CM004268 ED629227 ED629227 ED062399 ED062399
EF587543 CM006417 EF587543 EF587543 EF587543 EF587543

Thanks!

Can't quite get my head around what it is you want to do, but it seems
to me that your table design has a classic fault - you should instead
have a table for the Credit Memo(s) and a related table (one to many)
for the Original Invoices. It's just about universally true that if you
can "normalise" your data into the various "Normal Forms" everything you
want to do gets easier. Otherwise you're writing tortuous procedural VB
code.

Phil, London

 

[John W. Vinson]

I have a table that consists of credit memos (Field 1) and their original
invoices (Fields 2-41, if any).

Then you have a spreadsheet, not a relational table! Storing a one-to-many
relationship in each memo is simply Bad Design. Can a given invoice pertain to
more than one memo?

The trick is that there could be anywhere
from 0 to 40 fields of original invoices. Is there an expression that would:

1) Return an invoice number if all non-blank fields match each other
exactly, and
2) Return no result (i.e. blank) if any of the them differ from any of the
others?

Example:

Results Wanted CM_No OrigInv1 OrigInv2 OrigInv3 OrigInv4
CM004268 ED629227 ED629227 ED062399 ED062399
EF587543 CM006417 EF587543 EF587543 EF587543 EF587543

You say "match each other". Are you comparing one record to another record, or
comparing invoices within a record, or what? If comparing records to records,
does it matter WHICH of the non-normalized invoice fields match? That is, if
ED062399 is OrigInv3 in one record and OrigInv21 in a different record, is
that a match?

 

[GD]

John - Yes, it is possible for one invoice to be listed for multiple credit
memos.

 

[John W. Vinson]

John - Yes, it is possible for one invoice to be listed for multiple credit
memos.

Please explain what constitutes "a match", with an example.

 

[GD]

There's an example after the original question.

 

[John W. Vinson]

There's an example after the original question.

You posted:
Example:

Results Wanted CM_No OrigInv1 OrigInv2 OrigInv3 OrigInv4
CM004268 ED629227 ED629227 ED062399
ED062399
EF587543 CM006417 EF587543 EF587543
EF587543 EF587543

Given that I have NO idea what's in your table, which record is which, or what
logic you are using to ascertain "a match", I simply have no idea what you
mean. The word wrap doesn't help either. I'd love to help, but I simply do not
understand what you're trying to communicate! Help please?