Update Query Loop Question

[mndiscer]

Hi all,

Here is my problem, I have the following tables
1. TblStore [Store], [Rank]
2. TblItem [UPC], [Rank], [Store](This is null)

TblStore Example
[Store] [Rank]
1000 A
1001 B
1002 A
1003 B
1004 B

TblItem Example
[UPC] [Rank] [Store]
123 A
456 A
789 A
012 B
345 B
678 B
912 B

I need the Tblstore to loop through the same ranked records in the tblItem updating the Store field with the Store numbers from the Tblstore.

Final example

[UPC] [Rank] [Store]
123 A 1000
456 A 1002
789 A 1000
012 B 1001
345 B 1003
678 B 1004
912 B 1001


Any help would be great, I know a little sql but not enough to start this loop statement.

Thanks all!

 

[TheAceMan1]

How are ya mndiscer . . .

Could you give a synopsis on this [blue]Ranking Methodology[/blue]?

In [blue]TblStore[/blue] just how is rank assigned?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[danvlas]

You need an additional relationship. Now it's ambiguous, there is no way to know which UPC should be assigned to which store.

For instance, why is UPC 456 assigned to store 1002 and not to 100, like item 123, which has the same rank A?

All you can do now is to see which UPC's *could* match which stores:

Select Itm.UPC, Sto.Rank, Sto.Store
From tblItem As ITM
Inner Join tblStore As STO On ITM.RANK = STO.Rank
Order By ITM.Rank, Itm.UPC

Of course, now it's giving too many records. Please explain what's wrong with the unnecessary records.



Daniel Vlas
Systems Consultant

 

[mndiscer]

Hey guys, here is my reasoning. The tblstore is based off sales volumne. I have a make table query that performs the calculations and creates a temp table that gets over written weekly.
And to answer Danvlas question, "For instance, why is UPC 456 assigned to store 1002 and not to 100, like item 123, which has the same rank A?"
Because in that example there is only one instance of that UPC, so UPC 456 can only be assigned to one store. Its actually based off our inventory and if we only have one piece, we can only ship it to one store. What I think I need is help with writting a sql based loop where it loops through and updates records until no more instances exist. Heres a better example.

TblStore Example (same as before)
[Store] [Rank]
1000 A
1001 B
1002 A
1003 B
1004 B


TblItem Example
[UPC] [Rank] [Store]
123 A
123 A
123 A
012 A
012 A
345 B
345 B
678 B

After the looping update query runs the table should look like this...
[UPC] [Rank] [Store]
123 A 1000
123 A 1002
123 A 1000
012 A 1002
012 A 1000
345 B 1001
345 B 1003
678 B 1004

I think its a nested loop, where the 1st loop cycles through the stores that are ranked "A" and the 2nd loop cycles through the UPCs with the same "A" rank and updates the records until there is no more "A" ranked UPCS. Let me know if I'm not explaining this well enough or if I'm even in the right forum.

Thanks all

 

[danvlas]

In the fisrt example, I still think that UPC 456 should be assigned to store 1000. Why? Because there is no rule, and I prefer 1000, not 1002.
Just kidding.

In the new example I might understand what you're after, if you explain why 678 is assigned to store 1004 instead of 1001 (which would come first in the loop).

I might guess that you are trying to distribute UPC quantities as evenly as possible between eligible stores.

However, I think your structure is wrong or incomplete.
What is the primary key (or unique identifier) in table tblItem?
How are you going to make a difference between record 1 and record 3?
Keep in mind that records are NOT ordered in the database.


Before starting to loop through records, you might want to be sure you have a good structure in your database.


Daniel Vlas
Systems Consultant

 

[mndiscer]

Yes to the distribute evenly accross the eligible stores and yes you are correct, I didn't list it but I do have a TitleID and RankID column in both tables.

Heres my train of thought, it really doesn't matter what the UPC is only that the rank is A. After I typed the first post I needed to manually assign stores to get the order out. So after doing it once I learned that I needed to randomize the UPC listing that were ranked the same, to get the best mixture of upcs/stores. So I randomized the titles before assigning the TitleID, so all the "A" ranked titles are 1 through X and "B" ranked titles are X through Y.

So I'm back to how to update the Store column from a looping query that cycles through similiar ranked stores until there are no more "A" ranked titles. Then loop through the B ranked stores, updating the B ranked titles, etc...

Thanks