Here is what your table will ultimately look like (NOTE: I've abbreviated the Field names to save space):
- ID SKU Vend Pri VItem SameDay
-
1 123 MDTV 2 4987 Yes
-
2 123 RYUU 1 464 Yes
-
3 124 MDTV 2 5001 Yes
-
4 124 RYUU 1 463 Yes
-
5 254 MDTV 2 3998 Yes
-
6 254 RYUU 1 085 Yes
-
7 542 MDTV 2 5000 Yes
-
8 542 RYUU 1 357 Yes
-
9 324 MDTV 2 4304 Yes
-
10 324 RYUU 1 874 Yes
-
11 786 MDTV 2 4387 Yes
-
12 786 RYUU 1 919 Yes
-
13 475 MDTV 2 7653 Yes
-
14 475 RYUU 1 898 Yes
-
15 521 MDTV 2 4389 Yes
-
16 521 RYUU 1 078 Yes
-
17 354 MDTV 2 4020 Yes
-
18 354 RYUU 1 489 Yes
-
19 254 MDTV 2 4300 Yes
-
20 254 RYUU 1 167 Yes
-
21 128 MDTV 2 4760 Yes
-
22 128 RYUU 1 168 Yes
-
23 009 MDTV 2 7542 Yes
-
24 009 RYUU 1 597 No
-
25 010 MDTV 2 9677 Yes
-
26 010 RYUU 1 598 Yes
-
27 704 MDTV 0 4555 Yes
-
28 704 LOWE 2 23-OH No
-
29 704 RYUU 3 101 Yes
-
30 704 GVHH 1 804521 Yes
-
31 157 MDTV 0 1700 Yes
-
32 157 RYUU 1 416 Yes
-
33 157 GVHH 2 830921 Yes
-
34 858 MDTV 0 4044 Yes
-
35 858 LOWE 0 23-HF Yes
-
36 858 RYUU 1 413 Yes
-
37 858 GVHH 2 207871 Yes
-
38 821 MDTV 0 4817 Yes
-
39 821 LOWE 0 23-HJ Yes
-
40 821 RYUU 1 132 Yes
-
41 821 GVHH 2 507702 Yes
-
42 724 MDTV 0 8978 Yes
-
43 724 LOWE 0 23-IU Yes
-
44 724 RYUU 1 533 Yes
-
45 724 GVHH 2 434501 Yes
-
46 428 MDTV 0 4567 Yes
-
47 428 LOWE 3 23-YU Yes
-
48 428 RYUU 1 471 No
-
49 428 GVHH 2 459601 Yes
-
50 129 MDTV 0 5287 Yes
-
51 129 LOWE 0 23-FF Yes
-
52 129 RYUU 1 503 Yes
-
53 129 GVHH 2 784701 Yes
-
54 219 MDTV 0 5647 Yes
-
55 219 LOWE 0 42-SD Yes
-
56 219 RYUU 1 219 Yes
-
57 481 MDTV 0 4456 Yes
-
58 481 LOWE 0 42-BG Yes
-
59 481 RYUU 1 925 Yes
-
60 484 MDTV 0 6547 Yes
-
61 484 LOWE 0 42-BB Yes
-
62 484 RYUU 1 914 Yes
-
63 411 MDTV 0 6457 Yes
-
64 411 LOWE 0 46-EH Yes
-
65 411 RYUU 1 470 Yes
-
66 442 MDTV 0 4564 Yes
-
67 442 LOWE 0 31-TF Yes
-
68 442 RYUU 1 488 Yes
-
69 754 MDTV 0 4898 Yes
-
70 754 LOWE 0 31-AS Yes
-
71 754 RYUU 1 987 Yes
-
72 321 MDTV 0 4567 Yes
-
73 321 LOWE 0 31-MN Yes
-
74 321 RYUU 1 949 Yes
Please excuse any typos......
In general, you will notice that your table is now much taller than before, but it is much easier to deal with tall tables than wide tables. This actually takes us less space as a table, because you don't have wasted space allocated for fields in records that are blank.
ALSO NOTE: (high emphasis for a reason!) Your SKUs are in TEXT format, because of the leading zeroes in some cases. Also, because the Vendor Items are a mixture of numbers for some vendors and text and numbers for others, you must make sure to set the data type to Text, and that the rules for that field will work for
all vendors.
So, how does this work? Based on the data you provided, it looks like some of the vendors are not preferred at all for some items. So, those vendors who carry that item, but have no preference become a zero. You can change these values whenever.
Now, lets say I want to ship item 858 (your SKU), which is on the table under IDs 34-37 (just for reference). You want to find the prefered vendor that ships the same day:
- SELECT TOP 1 tblVendorItems.Vendor,
-
tblVendorItems.VendorItem
-
FROM tblVendorItems
-
WHERE tblVendorItems.SKU="858" AND
-
tblVendorItems.CanShipSameDay=True AND
-
tblVendorItems.VendorPriority<>0
-
ORDER BY tblVendorItems.VendorPriority;
We are just selecting the first record in this case ("TOP 1"), and since we are sorting by the Priority, the lowest number gets picked first. However, with "0" as the priority for those non-preferred vendors, we then must exclude those rcords.
The Query above will return:
- Vendor VendorITem
-
RYUU 413
Hope this helps you understand the direction we are taking you with this Table. This is a good theory behind the construction, and should be very useable, based on what you have provided us, thus far.
Let me know if this was useful for you!