Up to 10 vendors on a trasaction - create a field that indicates .

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create a formula that can test each vendor name field and return a
1 if they are all the same or a 2 if they are different.

I tried a nested IIF statement but there are too many conditions to test.

Any suggestions?
 
My interpretation is that the original poster has a table with 10 fields
named something like Vendor1, Vendor2 ... Vendor10. In which case, of
course, the answer is that the database needs to be redesigned. Of course, I
could be wrong.
 
Hi Chris2,

I'm soory if my description was not specific enough. I have a transactions
database with over 110,000 transactions. Within each transaction, the
payment can come from one or a combination of ten vendors. In some cases, we
can get ten different payments from different field offices of the same
vendor. I am trying to isolate these transactions by having a formula in a
new field that indicates if all payments are coming from one company (many
different field offices or from several different companies.

As I stated before, I tried a nested IIF statement but there are too many
variables.

Any suggestions assuming I explained myself better this time?

Thanks,
 
I want to create a formula that can test each vendor name field and return a
1 if they are all the same or a 2 if they are different.

I tried a nested IIF statement but there are too many conditions to test.

Any suggestions?
Click to expand...

What's the structure of your table? Do you have fields Vendor1,
Vendor2, Vendor3, ...? IF so your table structure IS SIMPLY WRONG;
you're "committing spreadsheet upon a database", a venial sin.

If you had a many to many relationship between Transactions and
Vendors, using a VendorTransaction table with fields for TransactionID
and VendorID as a joint two-field primary key, you would not need to
test for duplicates since you wouldn't be permitted to enter them in
the first place.

With your current design you'll need to write some VBA code to check
the ten fields for duplicates (more code than I want to write
offhand...) and redesign your entire database the first time you get a
transaction with eleven vendors.

John W. Vinson[MVP]
 
Kerney said:
I want to create a formula that can test each vendor name field and return a
1 if they are all the same or a 2 if they are different.

I tried a nested IIF statement but there are too many conditions to test.

Any suggestions?
Click to expand...

Ok . . . the below implements my interpretation of your request.

I know you said "return a 1 if they are all the same", but what kind
of vendor table has every vendor be the same name? And, "or a 2 if
they are different", but I think most vendors are going to have
different names, otherwise why have a table with more than one row?

The below assumes you wanted a 1 for each vendor name that appears
only once in the table, and a 2 for each vendor name that appears more
than once in the table.


Sample Table

CREATE TABLE Vendors
(VendorID AUTOINCREMENT
,VendorName Text(50)
)

Sample Data:
1, Office Box
2, Home Storage
3, Towing Company
4, Grocery Company
5, me.Com
6, Office Box
7, Lower's
8, Penny Pinchers
9, Fat Cat's
10, Fat Cat's
11, HyperChem AG
12, Fat Cat's
13, Zippy Delivery



SELECT V1.VendorName
,IIF(COUNT(V1.VendorName) = 1, 1, IIF(COUNT(V1.VendorName) >
1, 2))
FROM Vendors as V1
GROUP BY V1.VendorName

The results:

Fat Cat's 2
Grocery Company 1
Home Storage 1
HyperChem AG 1
Lower's 1
me.Com 1
Office Box 2
Penny Pinchers 1
Towing Company 1
Zippy Delivery 1


If my above interpretation was correct, you may find this useful:

SELECT V1.VendorName
,COUNT(V1.VendorName)
FROM Vendors as V1
GROUP BY V1.VendorName
HAVING COUNT(V1.VendorName) > 1

As this will show only the duplicate VendorNames.
 
Kerney said:
I want to create a formula that can test each vendor name field and return a
1 if they are all the same or a 2 if they are different.

I tried a nested IIF statement but there are too many conditions to test.

Any suggestions?
Click to expand...


And, just in case my interpretation was flat wrong, then try:

SELECT IIF(COUNT(V1.VendorName) = 10, 1, 2)
FROM Vendors as V1
 
Chris2 said:
and
return a to


And, just in case my interpretation was flat wrong, then try:

SELECT IIF(COUNT(V1.VendorName) = 10, 1, 2)
FROM Vendors as V1
Click to expand...


That example only works for 10 rows. For a variable number of rows,
it appears to be a bit tougher, but I *think* I've solved it.

Note: I did about ten minutes testing, on a couple of different
version of the Vendors table I posted previously. Please investigate
a bit further before using.


There are two queries.


Vendors_AreAllDups

SELECT V1.VendorName
,COUNT(V1.VendorName) AS VendorDups
FROM Vendors as V1
GROUP BY V1.VendorName
HAVING COUNT(V1.VendorName) = (SELECT COUNT(V2.VendorName)
FROM Vendors AS V2)


And:

Vendors_1or2

SELECT IIF(COUNT(*) = 0, 2, 1)
FROM Vendors_AreAllDups AS Q1;
 
Brendan Reynolds said:
My interpretation is that the original poster has a table with 10 fields
named something like Vendor1, Vendor2 ... Vendor10. In which case, of
course, the answer is that the database needs to be redesigned. Of course, I
could be wrong.
Click to expand...


Let's hope not. :)


Sincerely,

Chris O.
 
Kerney said:
Hi Chris2,

I'm soory if my description was not specific enough. I have a transactions
database with over 110,000 transactions. Within each transaction, the
payment can come from one or a combination of ten vendors. In some cases, we
can get ten different payments from different field offices of the same
vendor. I am trying to isolate these transactions by having a formula in a
new field that indicates if all payments are coming from one company (many
different field offices or from several different companies.

As I stated before, I tried a nested IIF statement but there are too many
variables.

Any suggestions assuming I explained myself better this time?

Thanks,

:
Click to expand...

I repost immediately below. See if this combination of queries can
help you (they'll have to be modified to fit your tables, of course).


Note: I did about ten minutes testing, on a couple of different
version of the Vendors table I posted previously. Please investigate
a bit further before using.


There are two queries.


Vendors_AreAllDups

SELECT V1.VendorName
,COUNT(V1.VendorName) AS VendorDups
FROM Vendors as V1
GROUP BY V1.VendorName
HAVING COUNT(V1.VendorName) = (SELECT COUNT(V2.VendorName)
FROM Vendors AS V2)


And:

Vendors_1or2

SELECT IIF(COUNT(*) = 0, 2, 1)
FROM Vendors_AreAllDups AS Q1;
 
John Vinson said:
What's the structure of your table? Do you have fields Vendor1,
Vendor2, Vendor3, ...? IF so your table structure IS SIMPLY WRONG;
you're "committing spreadsheet upon a database", a venial sin.

If you had a many to many relationship between Transactions and
Vendors, using a VendorTransaction table with fields for TransactionID
and VendorID as a joint two-field primary key, you would not need to
test for duplicates since you wouldn't be permitted to enter them in
the first place.

With your current design you'll need to write some VBA code to check
the ten fields for duplicates (more code than I want to write
offhand...) and redesign your entire database the first time you get a
transaction with eleven vendors.

John W. Vinson[MVP]
Click to expand...


Kerney,

Mr. Vinson's requests for table structures and other infomation would
go a long way to help answering your questions.


Sincerely,

Chris O.
 
Kerney said:
Hi Chris2,

I'm soory if my description was not specific enough. I have a transactions
database with over 110,000 transactions. Within each transaction, the
payment can come from one or a combination of ten vendors. In some cases, we
can get ten different payments from different field offices of the same
vendor. I am trying to isolate these transactions by having a formula in a
new field that indicates if all payments are coming from one company (many
different field offices or from several different companies.

As I stated before, I tried a nested IIF statement but there are too many
variables.

Any suggestions assuming I explained myself better this time?

Thanks,
Click to expand...

Kerney,

I can't tell from this request whether you have the properly
structured table I was thinking you do, or not.

Two other posters have already stated you may have repeating columns
for the same data based on the description of your original post.

The above text doesn't say one way or another.

If you have multiple columns for VendorName, then all bets are off.

I can't think of an offhand way to do it in those circumstances, and
you have now encountered the "reason" relational databases aren't
designed that way (it makes some queries difficult to impossible).

It would require, I think, at a minimum, a giant set of conditions.

I'm not sure if this will exceed any internal limits of the function,
either.

IIF(
(VendorName1 = VendorName2 AND
VendorName1 = VendorName3 AND
VendorName1 = VendorName4 AND
VendorName1 = VendorName5 AND
VendorName1 = VendorName6 AND
VendorName1 = VendorName7 AND
VendorName1 = VendorName8 AND
VendorName1 = VendorName9 AND
VendorName1 = VendorName10) AND
(VendorName2 = VendorName3 AND
VendorName2 = VendorName4 AND
VendorName2 = VendorName5 AND
VendorName2 = VendorName6 AND
VendorName2 = VendorName7 AND
VendorName2 = VendorName8 AND
VendorName2 = VendorName9 AND
VendorName2 = VendorName10) AND
..
..
..
(VendorName8 = VendorName9 AND
VendorName8 = VendorName10) AND
(VendorName9 = VendorName10), 1, 2)

If the internal limits of the function aren't exceeded, that *might*
work.


Or, you could write a VBA function:

Public Function VendorNameDups(TransactionID AS Long) AS Byte

<code to grab the VendorNames out of a particular row
and find out if they're all the same, probably by
loading them into an array and running them through
a recursive loop of some sort>

VendorNameDups = FinalDecision 'Value will be 1 or 2

End Function

This function could make the decision in place of a while IIF
statement stuck in the middle of a QueryDef.

In the end, the VBA function will probably be more useful.


Sincerely,

Chris O.
 
If the internal limits of the function aren't exceeded
Click to expand...

They will be... in spades. A single expression cannot exceed 256 bytes
(or some rather low limit, don't recall the exact number).

Custom VBA code would be the only solution, and it would be just as
ugly as this nested IIF.

John W. Vinson[MVP]
 
Hi,


Jet does not have a native DISTINCTCOUNT.

I would try


SELECT TransactionID
FROM myTable
GROUP BY TransactionID, SalesmanID
HAVING COUNT(*) > 1


to get the cases where a transaction implies a duplicated salesman.



SELECT DISTINCT a.TransactionID, iif( b.TransactionID Is null, 1, 2)
FROM myTable As a LEFT JOIN
( SELECT TransactionID
FROM myTable
GROUP BY TransactionID, SalesmanID
HAVING COUNT(*) > 1) As b
ON a.TransactionID = b.TransactionID




If your data is not normalized, you can try

SELECT TransactionID,
InStr( "," & v2 & "," & v3 & "," & ... & "," & v10 & "," , "," & v1 &
"," )
OR InStr( "," & v1 & "," & v3 & "," & ... & "," & v10 & "," , "," & v2
& "," )
...
OR InStr( "," & v1 & "," & v2 & "," & ... & "," & v9 & "," , "," & v10
& "," )


FROM myTable



where vendorID fields are v1, v2, ... v10. Basically, the first argument of
InStr is the concatenation of all the vendors, except one, and we look if
that missing is .... is found or not. If not found, InStr returns 0, which
is also false. Otherwise, it returns a not zero value, or True. That query
would probably be VERY slow. Don't blame Microsoft.



Hoping it may help,
Vanderghast, Access MVP
 
Chris2 said:
It would require, I think, at a minimum, a giant set of conditions.

I'm not sure if this will exceed any internal limits of the function,
either.

IIF(
(VendorName1 = VendorName2 AND
VendorName1 = VendorName3 AND
VendorName1 = VendorName4 AND
VendorName1 = VendorName5 AND
VendorName1 = VendorName6 AND
VendorName1 = VendorName7 AND
VendorName1 = VendorName8 AND
VendorName1 = VendorName9 AND
VendorName1 = VendorName10) AND
(VendorName2 = VendorName3 AND
VendorName2 = VendorName4 AND
VendorName2 = VendorName5 AND
VendorName2 = VendorName6 AND
VendorName2 = VendorName7 AND
VendorName2 = VendorName8 AND
VendorName2 = VendorName9 AND
VendorName2 = VendorName10) AND
.
.
.
(VendorName8 = VendorName9 AND
VendorName8 = VendorName10) AND
(VendorName9 = VendorName10), 1, 2)

If the internal limits of the function aren't exceeded, that *might*
work.
Click to expand...

I'm new here, and I normally sit back and watch for a while before
commenting, but are you sure it would need this much? If he had 1 table,
Table1, with 11 columns (id, vendor1, vendor2,...vendor10) would this not
work?

SELECT id, iif(vendor1 = vendor2 AND
vendor1 = vendor3 AND
vendor1 = vendor4 AND
vendor1 = vendor5 AND
vendor1 = vendor6 AND
vendor1 = vendor7 AND
vendor1 = vendor8 AND
vendor1 = vendor9 AND
vendor1 = vendor10, 1, 2)
FROM Table1 ;

I created this table quickly and put in 3 rows. id field was autonumber,
and then for each row I populated some data. In my case, the first row had
all fields set to A, the 2nd had all set to B except the 10th column which
was X, then the third all columns were set to C.

I ran the query and got what I expected were the right results...
1 1
2 2
3 1

I must be missing something out of the original requirements...

Matt
 
conditions.

I'm new here, and I normally sit back and watch for a while before
commenting,
Click to expand...

Please don't worry about that. :) I'm Glad to see you're willing to
comment at all.

but are you sure it would need this much? If he had 1 table,
Table1, with 11 columns (id, vendor1, vendor2,...vendor10) would this not
work?

SELECT id, iif(vendor1 = vendor2 AND
vendor1 = vendor3 AND
vendor1 = vendor4 AND
vendor1 = vendor5 AND
vendor1 = vendor6 AND
vendor1 = vendor7 AND
vendor1 = vendor8 AND
vendor1 = vendor9 AND
vendor1 = vendor10, 1, 2)
FROM Table1 ;
Click to expand...

What happens if vendor2 <> vendor3? Which comparison above catches
that?

I created this table quickly and put in 3 rows. id field was autonumber,
and then for each row I populated some data.

In my case, the first row had all fields set to A,
Click to expand...

Which the above expression will evaluate true.
the 2nd had all set to B except the 10th column which was X,
Click to expand...

Which the above expression will evaulate false, because it tests for
vendor1 = vendor10.
then the third all columns were set to C.
Click to expand...

Same as the first case.


Try adding a 4th Row. Set Vendor1, 2, 4, 5, 5, 6, 7, 8, 9, and 10
to "A", and Vendor3 to "B", and see what happens.
 
Chris2 said:
What happens if vendor2 <> vendor3? Which comparison above catches
that?
Click to expand...

I think I read the question incorrectly...in fact, I'm still reading it
incorrectly.

OP: "I want to create a formula that can test each vendor name field and
return a
1 if they are all the same or a 2 if they are different."

I think I focused on the "if they are ALL the same" -- so, by my way of
thinking, if Vendor1=Vendor2 and Vendor1 = Vendor10 then Vendor2 MUST also =
Vendor10...see what I mean? True and True and False and True =
False...right? Only True and True and True and True = True.

But undoubtedly I'm reading it too simply, otherwise everyone would have
immediately posteed with that solution.

I guess it boils down to I read it as he wanted one result per row, if all
10 fields were equal to each other, then 1, else 2. Every one else has read
it as he needs 10 values per record, some may be false, some may be
true...no, I'm still wrong. I'm just not seeing it, but I'm probably making
a fool of myself.

Matt
 
YYZ said:
I think I read the question incorrectly...in fact, I'm still reading it
incorrectly.

OP: "I want to create a formula that can test each vendor name field and
return a
1 if they are all the same or a 2 if they are different."

I think I focused on the "if they are ALL the same" -- so, by my way of
thinking, if Vendor1=Vendor2 and Vendor1 = Vendor10 then Vendor2 MUST also =
Vendor10...see what I mean?
Click to expand...

Well, after thinking about it for a minute, you are correct.

If Vendor 1 has been tested equal to Vendor2 to Vendor10 as True,
then the whole expression is True. If it wasn't equal to any one of
them, then we really don't care if Vendor2 and Vendor3 (etc.) don't
match, becasue *one* of them *won't* have matched Vendor1.

Thanks for straightening me out on that. :)

A nine-way comparison is a bit much, but will probably work.

On the other hand, for the record/album collection thread I was
posting to, where the Albums table had columns Track1 to Track24, a
twenty-three-way comparison would have been too much, and the VBA/DAO
loop I set up to scan through each column with a LIKE expression,
looking for a track's name with a search patter was the only really
viable way to go.

Either solution is less viable than properly normalized tables and a
simple SELECT query.


Sincerely,

Chris O.
 
thinking, if Vendor1=Vendor2 and Vendor1 = Vendor10 then Vendor2
Well, after thinking about it for a minute, you are correct.
Click to expand...

At least I'm not nuts. said:
Either solution is less viable than properly normalized tables and a
simple SELECT query.
Click to expand...

That bears repeating many many many times.

Matt
 
Back
Top