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

[Minty]

If there was a date or time or batch number or something else associated with the values this would be simple, but as everyone has stated, there is nothing in the data that identifies that "This is one bunch/sample of chocolates"
Your data (as currently presented) can't be identified to the level you want.

[DesoxyN]

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.

You can think of the 3rd column (C) as the result. Instead of using the SK prefix, just name it C1 (combination 1) in your mind. The first 2 columns and the rows from 2nd to 16th are what creates this C1. Then we want to create another group which we name Combination 2 (C2). This group consists of first two columns again and Row 17 to 26. The first 2 columns are the inputs. Depending on the table, the 3rd column is the output.

[DesoxyN]

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,

There are no Access file on this yet but your example looks great for this. I would want "FLRYA 0.75" 6 times and "FLRYB 2.5" 4 times. Makes sense.

[Micron]

In your data file, rows 31 - 35 and 40 - 44 both have 3 of .75 and 2 of .5 for FLRYA yet they have different C values. So I'm still not seeing what the logic is. TBH, I'm not going to watch this one any more but I wish you luck with getting a solution.

FLRYA	0.5
FLRYA	0.75
FLRYA	0.75
FLRYA	0.5
FLRYA	0.75

[DesoxyN]

In your data file, rows 31 - 35 and 40 - 44 both have 3 of .75 and 2 of .5 for FLRYA yet they have different C values. So I'm still not seeing what the logic is. TBH, I'm not going to watch this one any more but I wish you luck with getting a solution.

FLRYA	0.5
FLRYA	0.75
FLRYA	0.75
FLRYA	0.5
FLRYA	0.75

Great observation. Thats the main topic of the post. The database is not perfect, my main objective is to eliminate unnecessary(duplicate) combinations and to prevent more to create. So rows 31-35 and 40-44 both have the same inputs so the latter one is unnecessary, I can use the combination ID of 31-35.

[Gicu]

Please review the attached sample to see if that is what you want. It is a bit slow because of the sorting of the arrays but should give you an idea how to proceed.

Cheers,

[Welshgasman]

Great observation. Thats the main topic of the post. The database is not perfect, my main objective is to eliminate unnecessary(duplicate) combinations and to prevent more to create. So rows 31-35 and 40-44 both have the same inputs so the latter one is unnecessary, I can use the combination ID of 31-35.

So what is the signifcance of the Category field, if you just want one occurrence?
I must admit I am too at a loss as to the logic here.

[DesoxyN]

Please review the attached sample to see if that is what you want. It is a bit slow because of the sorting of the arrays but should give you an idea how to proceed.

Cheers,

Looks interesting but I think the search function is not working? Also I think I couldn't make myself clear about the situation so sorry for that. English is not my first language.

What I actually need a simple program to create a database from 0. The database I sent you folks is the inputs I will use to create a new, fresh database. As I said I will have 2 input parameters here. The main thing is how much inputs will be given.

So the application could bring out a form on startup;

1-) Having a textbox or a combobox for "How much input(rows) should I create?" For example to create "5" of FLRYA 0.5.
2-) Then obviously, a textbox to type "FLRYA or FLRYB or whatever" (LABEL1)
3-) Then a combobox to select values like "0.5" "0.75" "4" etc. (LABEL2)

Also 2 buttons;

1-) For continuing the input process, when pressed should save the previous selections and next selections will be added on the table under the same combination ID.
2-) To end input session, then it should search for the current database if it already has a combination ID for the selected inputs. If there is an ID, it should pop on the screen giving the combination ID for the same inputs. If there are no ID, it should save the new combination in the database giving it a new ID.

I know I sound like an unbearable guy and sorry for making a bad impression. I'm in need of a dire help here. Even if you can't help with this specific app., can you direct me to some tutorials about learning more about Access and Access forms? Thanks and awaiting your replies.

[Gicu]

This is a bit better explanation... Sorry, I'll upload another file shortly, somehow my latest changes didn't get saved....

Cheers,

[Gicu]

Here it is, it should do what you described in the last post; you need to enter the new batch in the Search box in the format shown in red (LABEL1 Space LABEL2 space QUANTITY colon NEXT_LABEL1 space.....).

Cheers,

[DesoxyN]

Here it is, it should do what you described in the last post; you need to enter the new batch in the Search box in the format shown in red (LABEL1 Space LABEL2 space QUANTITY colon NEXT_LABEL1 space.....).

Cheers,

This looks great, but I believe the main part seems missing here, when I search for: "FLRYA 0,75 9:FLYRB 2,5 6". It creates a new combination. However there is already a combination for these labels and initial counts. The first (1-15 rows) already has 9 times FLRYA 0,75 and 6 times FLYRB 2,5's. Seems like the order(sorting) is causing this but the main part of the problem is this. The sorting (the order) shouldn't matter for combination creation. What matters is that the count for every label.

Also there should be combinations containing both FLRYA's and FLRYB's with different sizes for example. Like "FLRYA 0,5 4 - FLRYA 1 2 - FLRYB 1,5 1 - FLRYB 2 5" . In this instance, there are 4 different entries.

[Gicu]

You have the second item's Label 1 misspelled (FLYRB instead of FLRYB)...

[DesoxyN]

Thats my bad... Can you lead me to some tutorials about understanding these modules and forms? I don't want to bother you anymore at least with some simple parts but I'm a beginner so these modules are unknown to me.

[Micron]

That suggests to me that the first 5 characters need to be validated before anything else.

[Gicu]

Hi,

I have made some changes to the file that I think would make it faster. Because you would be using this to add new records I think it would make sense to save with each record the combination qualifier (basically the search entry in format <Label1 Label2 Quantity:Label1 Label2 Quantity:....>. I know some will say that it goes against the normalization rules but it is accurate at the time of the data entry. Doing this would save having to dynamically build that qualifier for every combination every time you do a search using the slow qryCombinationCondensed query. We are now checking for the existence of the search string straight into the table SHUNK_DATA_NEW.

I also added some code to validate the search string to look for new entries in Label1 (notice I don't use spaces in field or object names as it makes it easier to program against them, no need to wrap them in square brackets) - that would have help you spot the misspell earlier today.

As for tutorials I think you should start with reading about database normalization first, then start with some basic SQL and VBA tutorials, I don't have specific ones but Google would give you plenty of options (I have some nice Access resource links on my website but I haven't updated that in quite a while).

Cheers,