who can help please with table query ?

  • Thread starter Thread starter swab
  • Start date Start date
S

swab

I need confront a lot of table like example (positive, negative and mix).
Tables have 15 colums 500 rows.
I must confront the row a -g with row aa-gg ( each value for row of fist 7
colums with each value of second 7 colums).
I need found similar value in each group and count.
In a-g you can found mulple value es 86 but it count only one.
In second aa-gg no multiple value in row.
I must view wich id or date have equal value.

This is my initial access table

ID DATE a b c d e f g aa bb cc dd ee ff gg
1 26/07/2003 -67 -70 -85 -68 -77 -6 -91 12 35 54 58 61 65 60
2 27/07/2003 -67 -71 -86 -87 -86 -85 -42 40 44 62 76 85 90 10
3 28/07/2003 -86 -72 -76 -81 -85 -10 -120 30 44 47 48 53 56 72
4 29/07/2003 -76 -87 -89 -85 -87 -106 -48 17 29 35 45 76 84 51
5 30/07/2003 -78 -84 -80 -59 -82 -57 -56 15 37 38 57 76 87 30
6 31/07/2003 -68 -89 -71 -71 -79 -33 -75 7 32 40 43 47 87 5
7 01/08/2003 -65 -82 -87 -86 -50 -8 -92 6 25 46 59 71 83 61
8 02/08/2003 -71 -69 -77 -78 -78 -68 -35 2 12 26 30 65 74 39
9 03/08/2003 -80 -76 -86 -55 -81 -55 -53 7 19 39 53 48 86 90
10 04/08/2003 -78 -70 -84 -87 -52 -94 -7 1 9 40 59 73 74 70
11 05/08/2003 -82 -59 -71 -76 -89 -86 -21 14 18 34 48 49 74 4
12 06/08/2003 -86 -74 -76 -89 -65 -20 -100 9 13 16 17 34 57 37
13 07/08/2003 -86 -87 -89 -73 -67 -70 -62 50 62 68 70 74 80 22
14 08/08/2003 -78 -84 -88 -86 -84 -32 -118 36 38 39 66 76 83 3
15 09/08/2003 -88 -89 -63 -80 -83 -10 -123 10 11 46 80 83 88 15


This is result i need

ID COUNT DATE a b c d e f g aa bb cc dd ee ff gg
2 1 27/07/2003 -67 -71 -86 -87 -86 -85 -42 40 44 62 76 85 90 10
3 1 28/07/2003 -86 -72 -76 -81 -85 -10 -120 30 44 47 48 53 56 72
4 1 29/07/2003 -76 -87 -89 -85 -87 -106 -48 17 29 35 45 76 84 51
5 1 30/07/2003 -78 -84 -80 -59 -82 -57 -56 15 37 38 57 76 87 30
9 2 03/08/2003 -80 -76 -86 -55 -81 -55 -53 7 19 39 53 48 86 90
13 2 07/08/2003 -86 -87 -89 -73 -67 -70 -62 50 62 68 70 74 80 22
15 4 09/08/2003 -88 -89 -63 -80 -83 -10 -123 10 11 46 80 83 88 15
 
I think you need to redesign the table if you are going to have any chance
of doing this. It might be possible with the current table design but I'd
not want to do it - you've got to search for values in 16 different fields.

If you can change the table so it looks something like this then what you
want will be possible:

ID Code Value
1 a -67
1 b -70
1 c -85
......
15 ff 88
15 gg 15

(Plus you'll need a second table to store the date's for each ID as you
don't want this stored twice. Make a composite primary key of ID and Code to
prevent duplicates).
 
Back
Top