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.
AD1080Asked:
Who is Participating?
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please clarify with data samples from the table?
I presume this can be solved with a very simple SQL
0
AD1080Author 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)

Open in new window

0
Guy Hengel [angelIII / a3]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

Open in new window


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 

Open in new window


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
0

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.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AD1080Author 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')

Open in new window



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

Open in new window


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
0
Guy Hengel [angelIII / a3]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
0
AD1080Author Commented:
Hi,

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

  Thanks very much.  Your solution will work great.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.