Search if same combination is already in the table, add if not found.

[DesoxyN]

Hello a very beginner Access user here,



So I need help building a basic application for the data I already have. Imagine 2 columns with data and depending on the given data and data count, we would like to create a combination ID for these. For example in the image below, first two columns (Label 1-2) has the label data and the 3rd column is the combination ID. So if I would like to use, [A 1 ; A 2 ; A 3 ; B 2] (4 entries) it should create [C1] as combination ID. Also if I would like to use [A 1 ; B 2] (2 entries) it should create [C5] as the combination ID. The real data is more complicated and I simplified it as best as I could.

In the future when I search with new label dats, it should warn me if I already have a dedicated combination ID for the given data. For example, if I feed [B 3 ; A 3] or [A 3 ; B 3] (without considering the feed order) to it once again in the future, it shouldn't create a new combination ID. Instead it should show me "You already have combination ID for this entries. The combination ID is: C2".

What are the steps I should follow when I want to create a basic access application for this? Any recommendations are welcome. Thanks.

[Minty]

I'm afraid without seeing real data, your current explanation doesn't make much sense.
Preventing duplicates is relatively simple, but what determines the combination codes. What is the logic behind the codes?

I think this is one of those situations where simplifying the original data doesn't help with the correct solution.
If you need to alter names etc to protect the innocent, then that's fine, just keep the real field names and the correct structure.

[Micron]

I think it's an exercise in creating new groups from the 2 left columns, and every time this is done, C is incremented by 1. However, it is also desired that when building a group, it should be unique. If it has already been used, then it should raise a message or something. To ensure unique groups, the combinations would have to be stored, probably as a compound index.

[Gicu]

Do you have the combination IDs (column 3) pre-defined and saved somewhere? How do you know if a 2-item combination is to have a stand-alone ID vs. being part of 3 or 4 item group (I guess the question is if your last example can ever be a sub-population of the first example).
Because you want to id the groups regardless of the order you would probably need a custom VBA function that would use Split() to get the individual values, then sort them and finally compare to a sorted query based on your table.

The more info you give us on this the better answers you can expect ....

Cheers,

[orange]

Further to what the others have posted, you might want to review composite unique index.
This approach would prevent duplicates in a multi-field index.
However, more info with examples would help responders. I'm not sure we fully understand the requirement.

[DesoxyN]

Hello, I have attached the whole database. So to explain, the first 2 columns are labels and the 3rd column is the combinations. As you can see in the image, I would like to feed first 2 columns AS A LIST to the application and it should create me a new combination. If the combination for the given list is already created before it should say "You already have a combination ID for these entries". The sorting-ordering shouldn't matter in this.

Like If I feed;

"FLRYB" and "1"
"FLRYA" and "0.5"

and If there is already combination for the;

"FLRYA" AND "0.5"
"FLRYB" AND "1"

It also should warn me about combination is already available for the given entries. The database excel is attached, please feel free to ask for more details. The combination ID's can start from "1" if my data looks complicated.

[June7]

Why do there appear to be duplicates in each CombinationID group? What determines construction of the CombinationID - sequential? Why are there gaps in sequence?

Why save SKO prefix?

You provided data in Excel but this is an Access question?

Advise not to use space in naming convention.

[DesoxyN]

Combination names doesn't matter here, we can create them starting from "C1" "C2" etc..

So the combinations may contain duplicate entries, for the first combination on my image (SK07529670 which I name this C1 from now) contains 15 entries with 2 labels. I can have duplicate entries here. So the first combination (C1) has ;

6 pieces of FLYRB 2.5's.
9 pieces of FLYRA 0.75's.

C1 has 15 pieces of total entries here.

2nd combination (C2) has ;

4 pieces of FLYRB 2.5's.
6 pieces of FLYRA 0.75's.

C2 has total of 10 entries.

My data is in Excel but I want to create this application in Access.

[Minty]

I think what I and the others are asking is what determines it's a duplicate?
It appears to be completely arbitrary, which means how would you program anything to work that out.

We can see you data, but it has no recognisable pattern of creation or logic.

Describe to us in simple terms why the third entry of FLYRB 2.5's and FLYRA .075's isn't a duplicate?

[DesoxyN]

Imagine there are 2 types of chocolates. 1 type is milky and 1 type is bitter. Also the sizes differ in each type from "Big, Normal, Small" You take a handful of them. When you open for hands you see "3 big milky chocolates, 1 small milky chocolate, 1 big bitter chocolate". This is our "combination number 1". Then you take another handful of them, you see 1 big milky chocolate, 2 big bitter chocolates, 4 small bitter chocolates. That is our combination number 2. But after a while when I open my hands and see "1 small milky chocolate, 3 big milky chocolates, 1 big bitter chocolate", I already have this as combination number 1. I keep doing this and number every different combination depending on the entries(chocolates). I don't have to create a combination ID for every possible as its endless.

The data already has some combinations depending on the entries, the main point here is I don't want to create a new combination when "I have the same entries with different order" like in the chocolate example I gave above. I think I can't simplify this better than this but I'll try to answer your questions as best I can.

[Micron]

So everything from row 2 to 16 represents the individual elements of one sample from a population, or something similar? I'm seeing 6 of FLRYB 2.5 and 9 FLYA .75 in the sample? One problem with that is that I can't see why the same elements and number values from rows 17 to 26 belong to a different group.

[Gicu]

So the combination is determined by the first two columns and numbers of each occurrence? For example you would enter FLRYA 0.75:FLRYA 0.75:FLRYA 0.75:FLRYA 0.75:FLRYA 0.75:FLRYA 0.75:FLRYA 0.75:FLRYB 2.5:FLRYB 2.5:FLRYB 2.5:FLRYB 2.5 and you expect to get combination 2 even if the individual components are the same as in combination 1?

Cheers,

[DesoxyN]

So everything from row 2 to 16 represents the individual elements of one sample from a population, or something similar? I'm seeing 6 of FLRYB 2.5 and 9 FLYA .75 in the sample? One problem with that is that I can't see why the same elements and number values from rows 17 to 26 belong to a different group.

Because the second combination has 4 FLRYB 2.5 and 6 FLRYA .75's. The quantity matters.

So the combination is determined by the first two columns and numbers of each occurrence? For example you would enter FLRYA 0.75:FLRYA 0.75:FLRYA 0.75:FLRYA 0.75:FLRYA 0.75:FLRYA 0.75:FLRYA 0.75:FLRYB 2.5:FLRYB 2.5:FLRYB 2.5:FLRYB 2.5 and you expect to get combination 2 even if the individual components are the same as in combination 1?

Cheers,

Well you typed 7 FLRYA 0.75's there so its a new combination. If you mistyped instead of 6 FLRYA 0.75's then yes, I would want to get combination 2 on this example. Individual components are the same but the used quantities are different.

[Micron]

The quantity matters.

The quantity seems to be only defined by the "grouping" identifiers that are already showin in column C. After looking at the attached file, I see that every row has a value in C, so whatever it is that defines the number of values in a grouping is still a mystery to me. As far as I can tell, I could simply have SK07529670 in rows 17 and 18 (expanding the group count by 1 for each combination of label1 and label2) and it tells me nothing about why I should or shouldn't. If your file contains the result, it's not showing the inputs. As far as I can see, either the inputs or the desired result (or both) are what's missing.

[Gicu]

Yes, I meant to have 6... How are you entering the list, separated by a character like I did or vertically as separate records or individual entities and their number (FLRYA 0.75 6:FLRYB 2.5 4)?

Cheers,