Matching Combinations

[Freshman]

Hi all,

I have a tbl called RegTbl with the following fields:
ItemCode
Qty

And then 2 tables used to setup some 'combos'
Combo
ComboItems

I need to find a way to identify when all Items of any of the combos are matched so I can delete the individual items and replace them with the matching ComboCode

I'm ok with marking and deleting the items once I can identify then but I'm battling with the query or a method to check for a possible match.

I've attached an extract of the tables with some queries to help.

I would appreciate some help with this

In my example data Combo4 is made up of item 17 and 86 so if that is matched in the RegTbl then it would mean I need to delete 17 and 86 and replace with Combo4.
Cherry on top would be to handle multiple combos

Thanks
Pierre

 

[Uncle Gizmo]

I've had a look at the sample database you provided. I am completely confused and I am not understanding your question, or the sample, or anything! I pass it back to you to come up with a better explanation of your problem and a better sample database... Hopefully with a form demonstrating the issue. I also suggest you name your objects properly.... Prefixing tables with tbl is good because it makes it much easier to see the difference between the table results and query results. And again prefix the queries with qry... These simple things you can do make it so much easier to discuss issues and problems. See the issues and problems without having to scratch your head wondering what is what...

 

[Freshman]

Hi Uncle - sorry let's go again with a real life example
Forget about the sample db for a moment and think about a point of sale system.
The Cashier enter a few items of which some (combined) qualify for a "Combo Special" reduce price.
As the Items are entered I want to check for a possible combination hit with prior set-up Combos.
The Combo would have a number of Items making up the mmm combination.

I fixed the initial sample but also added a second one with a frm.
Form should open in datasheet. As you enter from the drop-down selection, eg: 102, 104 and 107, it will recognize it as a combo and delete and replace with 'C1'.
Sure you'll get the idea if you test it.
The RetailPOS sample is however flawed since you can enter 102 X 3 to get the same effect.
That was just my initial attempt.

Might be better to setup a query that counts each code within the combo item list compared to the tblReg.
Like I said - I want some ideas on how to do it. Not necessary to 'fix' my sample.
It could be a total different approach as well

Thanks again

 

[Freshman]

This version works better but is cluttered with too many queries and also if you captured eg 102 twice and then 104 and 107, it does the replacement but drops the extra 102 item.
The result should be one combo (C1 in the example) but still keep the extra 102 item.

I'm probably over complicating the process hence asking for maybe another way to do it.

Thanks

 

[Uncle Gizmo]

I tested it as per your directions. I have one question. If you enter 102, 104 and 107 with quantities; when it changes to "C1" then all the quantities disappear. It just displays the price 55... This doesn't seem right to me...

 

[Freshman]

@Uncle - yes you are right - taking it one thing at a time. The qty must still be fixed but let me add to the mix: If you load Item 102 X 3 and say 104 X 3 and 107 X 2 it should result in 2 combos with a remaining 102 and 104.
So this is quite a complex task...
Thinking of running through the recordset and trying to do some sort of 'count' in VBA but I'm open to ideas.

At the moment I'm handling Combos in the reverse way. The Cashier loads the Combo from the ProductList as a separate item and the system then splits the Combo up into it's item parts and decrease each stock item forming the combo.
It was much easier doing it like that and it works great.
But the Client wants it the other way around and to be honest I like a challenge...

 

[Uncle Gizmo]

A challenge is the best way to learn. I'm sure if you persevere you will get there.

However I am inclined to think that you should go back to the beginning and start again as what do you have got so far is not something I would recognise as a common way to do something.

I suggest you start a new thread and explain your problem in terms of data with example tables.

The process of writing an explanation will also help you understand the issue better and you may even see a better way yourself.



Sent from my SM-G925F using Tapatalk

 

[Freshman]

Thanks Uncle G - I agree - will give it some thought and re-post.
In the meantime I'll roll out the app to the Client as is, with the other way of loading Combos as explained.
Will then work on this one.

 

[sneuberg]

I think this is an interesting problem and I could see it's application. For example if a person orders a double cheese burger, fries, and a large drink and that happens to be a combo at a lower price it would be good if the system would recognize that rather than rely on the memory and mental skills of the cashier.

I been thinking about the problem and I'm beginning to doubt if queries will help solve this problem. They might just make it more confusing. Perhaps the better approach would be to come up with an algorithm and then see if queries would help in it's solution. Anyway before I give that any thought I would like the to know the following.

It appears that you are designing the combos in this to allow different quantities of items, e.g., 1 cheeseburger, 2 fries, and 1 large drink could be Combo1 and 2 cheeseburgers, 1 fry, and 1 large drink could be Combo2. Is this true? If isn't not then the combos might be view as a set and maybe something from set theory would help.

Does the algorithm need to find the optimum combination (lowest price) of combos or is if sufficient to make sure the remain items don't make up a combo? I'll give you an example.

ChickenCombo: 1 Chicken Sandwich, 1 Fries, 1 Large Drink (Savings $1)
HamburgerCombo: 1 Hamburger, 1 Fries, 1 Large Drink (Savings $2)

The order

1 Chicken Sandwich
1 Fries
1 Large Drink
1 Hamburger

Both combos are possible but if the algorithm matches combos in the order the items were entered it would go with the cheeseburger combo and fail to give the customer the better savings of the hamburger combo. But then the Hamburger was probably a separate order within this order (would be in its own bag) and normally wouldn't be consider a candidate for a combo. There's another question. What makes up an order? What goes in a bag or a single cash register transaction?

 

[Freshman]

@sneuberg - you are right - the Client wants to rule out mistakes by the Cashier.

It appears that you are designing the combos in this to allow different quantities of items, e.g., 1 cheeseburger, 2 fries, and 1 large drink could be Combo1 and 2 cheeseburgers, 1 fry, and 1 large drink could be Combo2. Is this true?

Yes that is correct yes

The method do have flaws like you pointed out but it is also not just relaying on queries. I still have to complete the code that looks at the register table containing the current sale (list of items) and compare that to the query results.

I still think my other method of setting up the combos beforehand and letting the Cashier choose Combo1 or Combo2 is a better way but it leaves a lot up to the Cashier then.
Application also plays a roll. In a FastFood outlet the second method would be perfect as the Client will order a specific combo.
But in a retail store where the Customer buys a trolly full of goods, it will not work. Then only the first method would work as the system would have to pick out any possible combos inside a large order of multiple items.

I'm done with the second method but still have long to go on the first method...
Will start working on the code tomorrow.

Will also give your algorithm idea some thought but would need more details.

My sample is but a very small ripped out version of the actual app.

Thanks again

 

[jdraw]

In the retail store (I'm thinking grocery store/market), they sometimes have specials like 3 for $7.99, but individually the items are say $3.50. And quite often the items are not checked in consecutively. So, some how (the algorithm) recognizes that you do have 3 of the special item and credits you $2.51
($10.50 - $7.99).

So it seems, their algorithm recognizes the item as a "special of the week" and then starts accumulating the quantity; then for each 3 charges 7.99, and, unless there is a limit, will charge $3.50
where qty/3 <>0.

Don't know if it helps, but it seemed relevant.

You would have to know the items on special and the quantities and special charge vs regular charge for each.

Good luck. Interesting problem/opportunity.

 

[Freshman]

@jdraw - thanks for the input

I also got this query option from a sql guru buddy:

SELECT DISTINCT qryComboMatch.combocode, qryComboMatch.SingleCode, qryComboMatch.cnt
FROM qryComboMatch
WHERE (((Exists (select
1
from
qryComboMatch2
where
qryComboMatch2.combocode = qryComboMatch.combocode
and qryComboMatch2.cnt is null
))=False))
ORDER BY qryComboMatch.combocode, qryComboMatch.cnt;

The result gives me a breakdown of the items and how many of each which should help with counting the number of combos the items matches
Hope to get time to work on this tomorrow

 

[Freshman]

Hi all,

I've made some progress with regards to the combo query.

Please find sample attached (this is a ripped out version of the actual app which has many more buttons and stuff ).

Try this:
1. Combo4 is made up of Item 17 and Item 86
2. So if you enter 17 and then 86, both will be replaced by Combo4
3. If you enter eg: 5 on the keypad and then 17 and 5 again and 86, 5 X Combo4s will be added.
4. If you then add another 17 and 86 the Combo4 jumps to 6 instead of 5
So the above works very well

The only flaw is if you enter eg: 3 X 17 and 4 X 86 the Combo4 will be 3 which is correct but the additional 86 will be dropped.

All I need to do is, instead of deleting the items making up the relevant Combo like I'm doing now, rather decrease the qty of each to correspond with the number of Combos.
It might result in a zero qty for some items which I can then clean by deleting all items with a zero qty at the end of the routine.

Not sure how to do an SQL update query based on the existing qty for each item.

Please let me know if you need more info other than the notes I provided in the code (of the Register form)

Thanks
Pierre

 

[Freshman]

I forgot to add that the system is linked to a touch screen and a barcode scanner but to allow for testing without, you will have to use the mouse to click on the keypad and then manually enter '17' and hit enter in the combo box.
Single items can be added without having to put a '1' on the keypad

 

[sneuberg]

It's wasn't clear to me whether you needed to find the combination of combos with the lowest price or not but if you need to do then you still have work to do. Currently the order in which the items are entered can change the total. For example:

COO-EE ( EACH )
FRESH CHIPS
1/4 BUNNY

totals to R 19

While

COO-EE ( EACH )
1/4 BUNNY
FRESH CHIPS

totals to R 18

Sorry but I haven't been inspired with any great ideas on how to do this yet. There may not be a good algorithm to do this. It kind of stinks of the Knapsack problem. If that's the case you may not be able to provide an optimum solution unless the amount of data is small. Let's say that the only way (I hope it's not) to find the optimum solution is to try all permutations of the order of the items that are combo candidates. In the example above that's just 3 factorial or 3 x 2 x 1 or 6. With 10 items that grows to 3,628,800 which is probably still doable but at 20 it's a 18 digit number.

Do you know about how many combos they will be and how many items will be in them?

One more thing. I tried all of the combos and it found them except for combo1.

 

[Freshman]

Mmm back to the drawing board it seems. I really did not expect it to be such a challenge...
Thanks for your input

 

[jdraw]

Freshman,

I think you should describe the issue/problem/requirement in plain English. Just as Uncle Gizmo said.

I suggest you start a new thread and explain your problem in terms of data with example tables.

The process of writing an explanation will also help you understand the issue better and you may even see a better way yourself.

Until we (and you) clearly understand the requirement, any advice and suggestions are mostly guess work.


jdraw

 

[sneuberg]

Freshman,

I think you should describe the issue/problem/requirement in plain English. Just as Uncle Gizmo said.


Until we (and you) clearly understand the requirement, any advice and suggestions are mostly guess work.


jdraw

@jdraw You seemed to understand the problem in post 11. What happened? I think if you read all of the posts up to this point the problem is clear enough. albeit I wouldn't hurt if it were summarized.

 

[jdraw]

sneuberg,

In post 11 I gave a scenario that seemed relevant to me as I read the post. I don't see it as a combinatorics problem ie knapsack; but perhaps your understanding is closer to the heart of the issue.

As I see it, there have been guesses by responders, but no clear requirement from the OP. And, as is often the case, if the OP can't describe the problem clearly, then no one can build/design solution.

If you really feel the problem is clear enough, then perhaps you could put your thoughts into plain English with an example or 2.

Attached is the basic logic I was considering.

Good luck.

 

[Freshman]

Hi all,
Thanks a lot for sticking with this one in spite of what seems like a lacking spec from my side.

Let me try again by repeating the real life example and then also explaining the sample db
(By now the sample needs some cleaning up but I'm trying various things at the moment)

The Cashier enter a few items of which some (combined) qualify as a "Combo"
As the Items are entered I want to check for a possible combination hit with prior set-up Combos.
The Combo would have a number of Items making up the mmm combination.
I will make sure at setup that there are no duplication combinations of Combo/Items

The tblCombos hold the ComboCodes
The tblComboItems list the items amount amount each ComboCode consists of
One to many relation

So Combo1 is made up of
1 X Item 10
1 X Item 86
1 X Item 60

Combo4 is made up of:
2 X Items 17
1 X Item 86

Opening the frmRegister, the Cashier can enter items (by scanning them in)
This populates the tblReg table

A sub named 'CheckMatch' runs after the Combobox is updated

I've included plenty notes on what each line does.

So far it works well:
1. If I enter 17 and 86 it gives me Combo4
2. If I enter 2 X 17 and 2 X 86 via the Keypad it gives me 2 X Combo4
3. If I enter 3 X 17 and 2 X 86 it gives me 2 X Combo4 and a remaining Item 17
4. If I enter only 1 X 17 and any amount of 86 it will NOT give me Combo4 since it needs at least 2 X 17 to match
5. I tested Combo1 in the same way and it seems to work

Problem I'm still facing
If I enter say 10 and 86 and 17 and then 60 I expect a Combo1 to appear but it doesn't simply cause it then result in Combo1 fully matched but Combo4 semi-matched.
This is shown in qryComboMatchFin and qryComboMatchVerify
So I need a solution for only bringing up a 100% fully matched Combo
In theory it might sound like you could have 2 fully matched Combos but maybe not in practice since the checking is done after every item is entered.
So far I could not mimic 2 fully matched combos (except if I enter data via the backend)
I only getting the fully and semi matched issue

If you guys do find a case of 2 fully matched combos (without chaning the ComboSetup to create a duplication in the backend tables) then we might have to look a a solution of checking all items only at the end and not as they are entered.

I sure hope this shed more light

Thanks again
Seem attached db