how could i do that with access?

[stefanocps]

Hello i am trying to develop a calculation, it seems to me complicated to do it techinically speaking not so much as a concept. I'll try to explain what i need, let's see it from a starting table

On the X axis i have several input I1,I2,I3....ecc
On the Y axis i have several (around 200) output O1,O2 O3 ecc...
I normally supply several I that combine together to get 1, more or none O
Each O to be activated needs to meet an X from all the I and at least one A,B,C. This letters X A B C i used as a way to locate the conditions, could use any other mean

SO it may happen that O13 (example) to be activated need to have 2X at least 1A and at least 1B
while O3 needs 2X and at least 1A

The combination of all the I must meet contain all these


O1 O2 O3.....O13
I1 A X
I2 B X A
I3 X X X
I4 C A B

In this case O13 will ber activated while O3 not

X are all required, if an Ox required 4X there must be 4X
A,B,C and other letters just need to be there once

Once i get theO, this will be combined in other table to get the final results, but that is a second and easier step

Hope is it clear enough

I don't know how to start to work at this yet, but i wonder if Fabrik could do that

Thanks a lot

 

[Minty]

This sounds like an excel spreadsheet not a database table, and what is Fabrik?

 

[stefanocps]

This sounds like an excel spreadsheet not a database table, and what is Fabrik?

sorry about fabrik, that is an joomla app for database, should not nbe mentioned here, was a mistake

So you think i could do that with excel? I have around 150 Input string and normally i can supply to the calculation around 20
how wil i supply to elaboration xx input string?

 

[Minty]

I'm afraid your description has left me with no idea of what you are trying to achieve. I think we would need to see two things your starting data, and your desired results, in a format that makes sense. If it's easier to represent that in a excel sheet then attach it here. If you have some access tables then again zip that and attach it here.

You example data would need to cover all possible input and outputs.

 

[June7]

I understand your chart even though not lining up properly. Is that your raw data? Do you have an Access database? As suggested, provide sample data. Follow instructions at bottom of my post.

 

[stefanocps]

i know it is difficult to explain i'll try to add some more

I have a number of event that can be activated according to the content of the input i supply
I write down an examplke with 10 output

001000 1X A
002000 1X
003000 8X
004000 1X A
005000 1X A
006000 3X A
007000 3X A B
008000 3X A B
009000 3X A B
010000 1X

event 001000 need one X and one A to be activated
event 003000 needs eight X to be ativated
and so on
please note that while the X are a must, the other letters are AT LEAST, for example 00100 must have one X and at least one A. That because it can happen that when supply input sring more A (or B or C) would be there

i have also several input strings that looks like this

The string I1 have an X in corrispondance to output 001000, and 002000 and 007000 and a A to output 00400 and 008000 and 010000
and so on
These INPUT string are fixed, i just choose which one to supply to the program

in this example considering the firsta table and the second table the event that will be activated are
001000, 002000,004000,005000,006000 and 00900

This is a little example, i have almost 200 hundred possbile events, and around 150 input string to choose form. Normally i would supply around 20 input string
Hope it is clearer now

 

[stefanocps]

I understand your chart even though not lining up properly. Is that your raw data? Do you have an Access database? As suggested, provide sample data. Follow instructions at bottom of my post.

hi
i have an excel table which explain the thing
I tell you more about the datas, they are for grafology studies
on the row are the input possibility , and each line describe a feature of a calligraphy
on the column are responses
so each row can give an X, or an A,or B or C.. Can give a NO or just nothing to any of these responses

As i said the responses to be activated must contain all the input that contais all the X and at least one of the other letters if contained in the row

In the table i post onlyu consider the CAPITAL letters, not the others

p.s. note that the data i wrote in my previous posts are different form the real one you find in the table..i just used as an example

 

[The_Doc_Man]

Can you be more specific about the phrase "event to be activated"? Because the word "Event" has special meaning in Access and might be confusing the issue here. What specifically did you meant about events?

 

[stefanocps]

Can you be more specific about the phrase "event to be activated"? Because the word "Event" has special meaning in Access and might be confusing the issue here. What specifically did you meant about events?

what i mean is that the operator will input some data, in the image above are the Lines I001 I002 ecc.... Those should contain string of caracters, one for each corrispondent column
When the conditions that "activate" a column are found then that column is an output to consider. I have called it Event, but we can say output, The final aim of inserting data is to have as output those column(event) that match the criteria needed

 

[June7]

Spreadsheet and post sample don't look exactly alike. Why are numero S1 values repeated in worksheet but not in post sample? What is significance of yellow and grey colors? Data in worksheet cells are more varied.

Operator inputs data where - cells of this worksheet? Where are the criteria of the first table input to? What do you mean by "activate" - make visible or hide? Output what to where? You say 'output those column', however, databases output records (rows). If you want to conditionally include a field (column) with record output, that is difficult.

Not clear to me what process you are trying to automate but beginning to suspect would involve a lot of VBA.

How do you currently perform this evaluation?

Provide a sample of what 'output' should look like.

 

[stefanocps]

sorry in my post i have create something not base on the excel table. i made it quick just to be able to show immediately what i mean
So data in the table are differnt form the esemple i write , but logic is the same

 

[June7]

Okay, but the logic and process are not clear to me. Did not address my questions.

 

[stefanocps]

i know, it is not easy to explain, also i had to understand it as it is not my project and i know how complcate is
I will try later to explain in some other way
if you have question that my address me please go on

 

[The_Doc_Man]

Here is the issue: You are essentially inputting things based on an Excel data layout, which is not native to Access. So my question now becomes: How and what do you choose about your input. From what I can SEE, it appears that you choose a column and a row and a letter to put in the cell that is the intersection of the column and row. Then, when you have "accumulated" enough entries to meet your "event" conditions, you want something special to happen. Did I get at least close?

The closest Access would likely come would be to enter the row number, column number, and letter using combo boxes that let you drop down a list to select whatever selection there is. If the row and column numbers are "amorphous" or if there are a really HUGE number of them, then you might be able just directly type them in. That would give you a list of boxes that contain something.

Then after each input, you might need to run a query that counts your A, B, X, or whatever other strings you have used. I gather that your interest is when you reach a certain count of letters or a certain specific grouping of letters.

The query need only look at the items where their row number matches the row number you just entered, or where the column number matches the column number you just entered. Based on your description, though, it is totally unclear as to what happens next and what happens after that.

By that, I mean ... OK, you just found a condition leading to your desired output event. It is not clear what will be output. But THEN, what do you do with the data already entered? If you make one more entry into the same row or column (whatever is your trigger), did you erase the prior contents to prevent "double dipping?" Or do you just try to prevent that specific combination from triggering another event?

I can tell you that we can only help you so much with the description you gave us. I'm going to guess that this process is still not 100% clear in your mind. You said "it is not easy to explain, also i had to understand it as it is not my project."

 

[Cronk]

Maybe there is no underlying algorithm defining the result.

If column headings and the two row headings are put into separate tables and the results are put into a join table, a look up as Doc is suggesting will give the appropriate "intersection" value in the spreadsheet.

 

[stefanocps]

what i will input are those string, the row ones
as in the previous image, for example i input the
I001 string
I001 string it is an one row array of character

suppose that we have 10 column the string will contain 10 characters (i consider character also the blank space)
so I001 will looks like XX AB XABA
Charactetrs might be NO,X,A,B,C...

Now i add another input string

As you can see, the columns start to be populated
The aim is to see what column meet the condition to be considered, good, or activated,,say as you like. The columns that meet critera will be taken to a further process

Criteria for column to be activated are
001000 1X A
002000 1X
003000 8X
and so on
That means that the column 001000 to be considered activated must have one X and at lest one A, when checking the input row
In this case the column 001000 has got only one X, and it is not activated as it would require at least an A also
But the column 002000 is activated as it contains one X and one A, and to be activated it requires only one X

So this is the game. Consider that i normally will input around 15..20 strings (there should be around 150 input strings prepared)
And the column are around 200

So you see the table is quite static, data are fixed, but the differnt combinations of input string might activate some columns or others


i say iy again..the data i used in this example are different from the ones in the excel table..tht is the original

 

[The_Doc_Man]

Basically, then each row contains random (in the sense of "unpredictable order") strings of A, B, X, spaces, and other characters. Further, the number of spaces appear to be significant.

The conditions for "activating a column" vary from one column to the next in what appears to be a random (in the sense of "unpredictable") rule based on what has accumulated in the various columns - but with different rules for each column.

I suppose it is possible for Access to have a table of "column" rules, but the problem here is you are dealing with sparse and unpredictable data in a way that negates the main power of Access, which is stronger when going by row than when going by column. Not that it couldn't do either, but the inherent pseudo-random nature of these "triggers" and of the inputs means that subtlety is not going to be easy to identify or apply.

The subtle way to do this kind of thing is to identify patterns and then intercept them. But what you have described seems to not have an obvious pattern. Without that pattern, I don't know how much help I can offer. The comments from my colleagues here on the forum suggest they are just about as baffled as I am.

 

[Gasman]

Wouldn't the columns become records with an A, X, space etc. Then it would be a count of those against the criteria

eg: Does 003000 have at least 8 records with X as a value.?

 

[The_Doc_Man]

@Gasman - was that question to me? If so, yes that would be true. But the larger picture is that 04000 would have a totally different rule. You would have as many triggering rules as you would have columns and would need some kind of complex parser-type option to decipher the rules for each column, since they are not constant.

 

[Gasman]

@Gasman - was that question to me? If so, yes that would be true. But the larger picture is that 04000 would have a totally different rule. You would have as many triggering rules as you would have columns and would need some kind of complex parser-type option to decipher the rules for each column, since they are not constant.

To anyone Doc, I was thinking out aloud to see if I was on the correct track, that is all.