# Visual Basic Logic Structure

Hi,

I am working on project where I need to supply file paths pointing to jpegs to fill 5 spots in a label designer.

I have 5 different images, and as mentioned above, 5 locations to fill.  Each jpeg image has a rank from 1 to 5.

The label program is driven by data inserted into a SQL table.  That table has 5 different boolean attribute fields which correspond to the 5 images.    Any given record will need to print with anywhere from 0 to 5 of the images, depending on which attribute fields in the record are TRUE.

The highest ranking image with a corresponding TRUE record in the data needs to print in position 1 on the label.
The next highest ranking image with a corresponding TRUE record in the data needs to print in position 2 on the label.
The next highest ranking image with a corresponding TRUE record in the data needs to print in position 3 on the label, etc.

I need to work out a logic control structure to implement this, and am struggling with it so far.  There are 3125 total possible combinations (5 x 5 x 5 x 5 x 5).  I'm sure there is an elegant way to write this.

Thanks in advance for your help on this.
Asked:
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Billing EngineerCommented:
can you please clarify with data samples from the table?
I presume this can be solved with a very simple SQL
Author Commented:
Hi,

Thanks for the reply.

Here are some sample records, and some sample scenarios.  Hopefully I have explained this well enough.

For item 1000000123, Attribute 1 would print in position 1
For item 1000000125, Attribute 3 would print in position 1
For item 1000000126, Attribute 2 would print in position 1, Attribute 4 would print in position 2
For item 1000000133, Attribute 5 would print in position 1

``````INSERT INTO PRINT_LIST (ITEM_NO, ATTRIBUTE_1, ATTRIBUTE_2, ATTRIBUTE_3, ATTRIBUTE_4, ATTRIBUTE_5)
VALUES
('1000000123', Y,N,N,N,N),
('1000000124', Y,Y,N,N,N),
('1000000125', N,N,Y,N,N),
('1000000126', N,Y,N,Y,N),
('1000000127', Y,N,N,Y,Y),
('1000000128', Y,N,N,Y,N),
('1000000129', Y,Y,N,Y,N),
('1000000130', Y,N,N,N,N),
('1000000131', N,N,Y,Y,Y),
('1000000132', N,N,N,Y,Y),
('1000000133', N,N,N,N,Y)
``````
Billing EngineerCommented:
yes, the explanation is ok;

however, the design is not normalized, it should be 2 tables instead (maybe it is?)

INSERT INTO PRINT_LIST (ITEM_NO) VALUES ('1000000123'), ('1000000124' )

INSERT INTO PRINT_LIST_ATTRIBUTES (ITEM_NO, ATTRIBUTE_ID, ATTRIBUTE_STATUS ) VALUES
('1000000123', 1, 'Y'),
('1000000124', 1, 'Y'),
('1000000124', 2, 'Y')

eventually, you may also want to store the "N" in that table, but that is irrelevant for this query.

``````select a.ITEM_NO
, ATTRIBUTE_ID
, row_number() over ( partition by a.ITEM_NO order by a.ATTRIBUTE_ID) position
from PRINT_LIST_ATTRIBUTES a
where a.ATTRIBUTE_STATUS = 'Y'
order by a.ITEM_NO
, ATTRIBUTE_ID
``````

note: if you wanted you original table "layout", that can be produced using a view like this:
``````select l.item_no
, isnull( max( case when a.attribute_id = 1 then a.attribute_status end) , 'N' ) attribute1
, isnull( max( case when a.attribute_id = 2 then a.attribute_status end) , 'N' ) attribute2
, isnull( max( case when a.attribute_id = 3 then a.attribute_status end) , 'N' ) attribute3
, isnull( max( case when a.attribute_id = 4 then a.attribute_status end) , 'N' ) attribute4
, isnull( max( case when a.attribute_id = 5 then a.attribute_status end) , 'N' ) attribute5
from PRINT_LIST l
left join PRINT_LIST_ATTRIBUTES a on a.item_no = l.item_no
group by l.ITEM_NO
``````

and you could even "insert/update" into this view down to the underlying tables using triggers on that view.

hope this helps to solve your problem in the best possible way

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Hi,

I like the idea of normalizing the data.  Your first example is getting close but I am getting records for Attribute 1 in position 1 and in position 2 for several item numbers.

Can you see why?

``````CREATE TABLE PRINT_LIST_ATTR
(
ITEM_NO VARCHAR(50),
ATTR_ID VARCHAR(3),
ATTR_STATUS VARCHAR(3))

INSERT INTO PRINT_LIST_ATTR (ITEM_NO, ATTR_ID, ATTR_STATUS)
VALUES
('1000000123', '1','Y'),
('1000000124', '1','Y'),
('1000000125', '1','N'),
('1000000126', '1','N'),
('1000000127', '1','Y'),
('1000000128', '1','N'),
('1000000129', '1','N'),
('1000000130', '1','N'),
('1000000131', '1','Y'),
('1000000132', '1','Y'),
('1000000133', '1','Y'),
('1000000123', '2','Y'),
('1000000124', '2','Y'),
('1000000125', '2','Y'),
('1000000126', '2','N'),
('1000000127', '2','Y'),
('1000000128', '2','N'),
('1000000129', '2','N'),
('1000000130', '2','N'),
('1000000131', '2','Y'),
('1000000132', '2','Y'),
('1000000133', '2','Y'),
('1000000123', '3','Y'),
('1000000124', '3','Y'),
('1000000125', '3','N'),
('1000000126', '3','N'),
('1000000127', '3','Y'),
('1000000128', '3','N'),
('1000000129', '3','N'),
('1000000130', '3','N'),
('1000000131', '3','Y'),
('1000000132', '3','Y'),
('1000000133', '3','Y'),
('1000000123', '1','Y'),
('1000000124', '1','Y'),
('1000000125', '4','Y'),
('1000000126', '4','N'),
('1000000127', '4','Y'),
('1000000128', '4','N'),
('1000000129', '4','N'),
('1000000130', '4','N'),
('1000000131', '4','Y'),
('1000000132', '4','Y'),
('1000000133', '4','Y'),
('1000000123', '5','Y'),
('1000000124', '5','Y'),
('1000000125', '5','Y'),
('1000000126', '5','N'),
('1000000127', '5','Y'),
('1000000128', '5','N'),
('1000000129', '5','N'),
('1000000130', '5','N'),
('1000000131', '5','Y'),
('1000000132', '5','Y'),
('1000000133', '5','Y')
``````

``````SELECT A.ITEM_NO
, ATTR_ID
, row_number() over ( partition by a.ITEM_NO order by a.ATTR_ID) position
from PRINT_LIST_ATTR a
where a.ATTR_STATUS = 'Y'
order by a.ITEM_NO
, ATTR_ID
``````

Result Set:

ITEM_NO      ATTR_ID      position
1000000123      1      1
1000000123      1      2
1000000123      2      3
1000000123      3      4
1000000123      5      5
1000000124      1      1
1000000124      1      2
1000000124      2      3
1000000124      3      4
1000000124      5      5
1000000125      2      1
1000000125      4      2
1000000125      5      3
1000000127      1      1
1000000127      2      2
1000000127      3      3
1000000127      4      4
1000000127      5      5
1000000131      1      1
1000000131      2      2
1000000131      3      3
1000000131      4      4
1000000131      5      5
1000000132      1      1
1000000132      2      2
1000000132      3      3
1000000132      4      4
1000000132      5      5
1000000133      1      1
1000000133      2      2
1000000133      3      3
1000000133      4      4
1000000133      5      5
Billing EngineerCommented:
I cannot beliewe you get that outcome with that script.
the "duplicates" can only be returned if there are duplicates in the table data, hence you must have executed (partially) the insert 2 times
please review the data
Author Commented:
Hi,

Your right, sorry.   There was just an error in my sample data insert.

Thanks very much.  Your solution will work great.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.