the cells that some rows have in common

[analarana]

Hi,
I know nothing about access, and I don't even know if access is the right program for what i need.

Could someone please tell me how I can find the cells that some rows have in common? I need to do this lots of times with lots of names, but this is a small example:
excel2.jpg
So for example, the first time I need to know the numbers that Ana (row 1), Laura (row2) and Manuel (row3) have in common, so the computer should give me nº17 as the answer. If I ask for the numbers (or cells) Chris (row 4)and Eddie (row 5) have in common, the computer should say nº2, nº3 and nº4. I'm not looking for the number that appears more times in the grid, but for the one that the rows I select have in common.
Cheers.

[Dal Jeanis]

This can be done in Access, and it's not too difficult. You would store each of those lines as several rows in the same table, like this:

tbl_PN
Pers   Num
Ana     1
Ana     5
Ana    17 
Ana    21
Ana    13
Ana    35
Laura   2
Laura   4
Laura   1
Laura  17
Laura   3
Laura   9
Manuel 37   etc

Then, you would code a query something like this:

SELECT Num, COUNT(*)
FROM tbl_PN
WHERE (Pers = "Ana" OR Pers = "Laura" OR Pers = "Manuel")
GROUP BY Num
HAVING COUNT(*) = 3;

That query gets you all the Numbers that have been chosen by those three people.

Once that is working, you could build a form that allows you to pick the three people, and builds and runs the query for them.

If you wanted all possible combinations of three people, I'd probably design a trickier way, based on number theory, but this one would work for any one combination of three.

[analarana]

Cool, thanks a lot. I’ll have to learn a bit of access to fully understand your explanation.
So should I put all the names on the first column and all the numbers on the 2^nd one?
Also, would this query work if the names have more than 1 number in common (would the computer give more than one number as a result)?
Thanks again.

[Dal Jeanis]

To answer your last question first - that query will get every number that the three people have in common. You could change the "= 3" to "= 2" and you'd get every number that exactly two of the three had in common.

In this table, for this code, each person gets one record per number, so they would each have six records.

What you can do, is leave the worksheet the way you have it, and when you import the worksheet, using Access's import wizard,

1) import it into a temporary table tblTempNums (tname tnum1 tnum2 tnum3 tnum4 tnum5 tnum6).

2) delete all the data out of your real table. Just to be clear, the first time you have to create the empty table tbl_PN with the two fields Pers and Num, and you won't have to delete the data.

DELETE * from tbl_PN;

3) create an append query for each column from the temp table. The code for the first two queries would look something like these (you'll create all six):

INSERT INTO tbl_PN
(Pers, Num)
VALUES
(SELECT tname, tnum1 FROM tmp_TempNums);

INSERT INTO tbl_PN
(Pers, Num)
VALUES
(SELECT tname, tnum2 FROM tmp_TempNums);

4) run the delete query and all those append queries each time you need to import the workbook fresh.