HLOOKUP in Access 2013

[AndyDevine1]

Hi there

To simplify, I have three fields in my table:

Column A "Item" contains
A2 - Item 1
A3 - Item 2


Column B contains the heading "Item 1" and then values in B2 (eg 100) and B3 (eg 50)
Column C contains the heading "Item 2" and then values in C2 (eg 11) and C3 (eg 12)

I want to report on the totals for each column (B, C etc)

So I want Item 1 total = 100 + 150 = 150
And item 2 total = 11 + 12 = 25

Can I do this via a horizontal look up or similar?

Thanks for reading

Andy

 

[sheetspread]

I changed the table slightly:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Item	Group 1	Group 2	Group 3	Group 4	Group 5	Group 6	Group 7	Group 8	Group 9	Group 10	Group 11	Group 12	Group 13	Group 14	Group 15	Group 16	Group 17	Group 18	Group 19	Group 20
2	1	42	47	74	13	84	19	79	82	31	79	97	3	55	3	52	43	54	97	33	6
3	2	26	11	41	78	99	8	42	73	42	45	52	69	53	35	68	15	26	82	46	52
4	3	52	54	10	77	52	11	94	11	23	5	62	86	63	3	9	19	93	87	2	26
5	4	33	26	42	84	61	79	17	54	60	24	43	25	20	87	32	42	4	38	6	75
6	5	48	68	96	73	63	74	34	22	58	85	84	9	6	61	30	23	17	81	28	20
7	6	66	89	27	49	57	35	62	88	41	43	26	46	91	66	90	27	6	19	9	62
8	7	97	68	46	16	99	53	100	46	17	2	79	86	92	86	42	66	64	59	81	27
9	8	81	66	79	39	37	52	51	99	66	49	49	36	56	24	37	97	48	1	76	84
10	9	2	66	39	65	91	57	7	94	91	21	19	4	39	17	89	42	37	12	91	50
11	10	53	24	40	90	72	7	1	76	8	47	24	20	70	72	90	75	5	23	45	10
12	11	41	71	59	65	27	83	11	31	63	52	30	54	34	46	82	39	95	66	81	15
13	12	22	60	53	4	27	99	4	53	86	16	35	70	49	29	17	26	57	43	93	4
14	13	100	8	38	52	62	24	45	21	30	49	40	3	16	21	31	78	40	86	44	51
15	14	60	10	79	16	53	76	95	37	52	76	92	28	62	11	66	6	98	62	7	87
16	15	82	4	32	54	78	69	66	53	80	15	100	60	26	99	72	2	41	82	71	99
17	16	21	23	70	47	65	95	16	16	63	46	41	35	91	64	35	5	24	8	96	19
18	17	5	6	37	30	28	67	50	55	69	35	59	79	16	1	38	9	58	10	8	30
19	18	23	87	34	75	86	54	4	29	35	30	70	45	91	34	10	66	61	94	19	64
20	19	53	52	70	28	1	13	43	29	83	15	63	53	35	52	15	60	54	15	37	35
21	20	97	54	76	2	25	45	51	43	94	90	96	11	99	64	11	10	4	96	37	76
Data

With Data as the table name, paste this into sql view of a blank query:

SELECT Data.Item, 1 AS [Group], Data.[Group 1] AS [Number]
FROM Data;
union
SELECT Data.Item, 2 AS [Group], Data.[Group 2] AS [Number]
FROM Data;
union
SELECT Data.Item, 3 AS [Group], Data.[Group 3] AS [Number]
FROM Data;
union
SELECT Data.Item, 4 AS [Group], Data.[Group 4] AS [Number]
FROM Data;
union
SELECT Data.Item, 5 AS [Group], Data.[Group 5] AS [Number]
FROM Data;
union
SELECT Data.Item, 6 AS [Group], Data.[Group 6] AS [Number]
FROM Data;
union
SELECT Data.Item, 7 AS [Group], Data.[Group 7] AS [Number]
FROM Data;
union
SELECT Data.Item, 8 AS [Group], Data.[Group 8] AS [Number]
FROM Data;
union
SELECT Data.Item, 9 AS [Group], Data.[Group 9] AS [Number]
FROM Data;
union
SELECT Data.Item, 10 AS [Group], Data.[Group 10] AS [Number]
FROM Data;
union
SELECT Data.Item, 11 AS [Group], Data.[Group 11] AS [Number]
FROM Data;
union
SELECT Data.Item, 12 AS [Group], Data.[Group 12] AS [Number]
FROM Data;
union
SELECT Data.Item, 13 AS [Group], Data.[Group 13] AS [Number]
FROM Data;
union
SELECT Data.Item, 14 AS [Group], Data.[Group 14] AS [Number]
FROM Data;
union
SELECT Data.Item, 15 AS [Group], Data.[Group 15] AS [Number]
FROM Data;
union
SELECT Data.Item, 16 AS [Group], Data.[Group 16] AS [Number]
FROM Data;
union
SELECT Data.Item, 17 AS [Group], Data.[Group 17] AS [Number]
FROM Data;
union
SELECT Data.Item, 18 AS [Group], Data.[Group 18] AS [Number]
FROM Data;
union
SELECT Data.Item, 19 AS [Group], Data.[Group 19] AS [Number]
FROM Data;
UNION SELECT Data.Item, 20 AS [Group], Data.[Group 20] AS [Number]
FROM Data;

then paste this into another blank query:

SELECT DISTINCTROW [Data Query2].Group, Sum([Data Query2].Number) AS [Sum Of Number]
FROM [Data Query2]
GROUP BY [Data Query2].Group;

Databases efficiently store and present 1 to 1 data, not the 2-dimensional table you started with. This is a common problem, which later versions of Access might handle better, perhaps with the table analyzer to "normalize".

 

[AndyDevine1]

I changed the table slightly:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Item	Group 1	Group 2	Group 3	Group 4	Group 5	Group 6	Group 7	Group 8	Group 9	Group 10	Group 11	Group 12	Group 13	Group 14	Group 15	Group 16	Group 17	Group 18	Group 19	Group 20
2	1	42	47	74	13	84	19	79	82	31	79	97	3	55	3	52	43	54	97	33	6
3	2	26	11	41	78	99	8	42	73	42	45	52	69	53	35	68	15	26	82	46	52
4	3	52	54	10	77	52	11	94	11	23	5	62	86	63	3	9	19	93	87	2	26
5	4	33	26	42	84	61	79	17	54	60	24	43	25	20	87	32	42	4	38	6	75
6	5	48	68	96	73	63	74	34	22	58	85	84	9	6	61	30	23	17	81	28	20
7	6	66	89	27	49	57	35	62	88	41	43	26	46	91	66	90	27	6	19	9	62
8	7	97	68	46	16	99	53	100	46	17	2	79	86	92	86	42	66	64	59	81	27
9	8	81	66	79	39	37	52	51	99	66	49	49	36	56	24	37	97	48	1	76	84
10	9	2	66	39	65	91	57	7	94	91	21	19	4	39	17	89	42	37	12	91	50
11	10	53	24	40	90	72	7	1	76	8	47	24	20	70	72	90	75	5	23	45	10
12	11	41	71	59	65	27	83	11	31	63	52	30	54	34	46	82	39	95	66	81	15
13	12	22	60	53	4	27	99	4	53	86	16	35	70	49	29	17	26	57	43	93	4
14	13	100	8	38	52	62	24	45	21	30	49	40	3	16	21	31	78	40	86	44	51
15	14	60	10	79	16	53	76	95	37	52	76	92	28	62	11	66	6	98	62	7	87
16	15	82	4	32	54	78	69	66	53	80	15	100	60	26	99	72	2	41	82	71	99
17	16	21	23	70	47	65	95	16	16	63	46	41	35	91	64	35	5	24	8	96	19
18	17	5	6	37	30	28	67	50	55	69	35	59	79	16	1	38	9	58	10	8	30
19	18	23	87	34	75	86	54	4	29	35	30	70	45	91	34	10	66	61	94	19	64
20	19	53	52	70	28	1	13	43	29	83	15	63	53	35	52	15	60	54	15	37	35
21	20	97	54	76	2	25	45	51	43	94	90	96	11	99	64	11	10	4	96	37	76
Data

With Data as the table name, paste this into sql view of a blank query:

SELECT Data.Item, 1 AS [Group], Data.[Group 1] AS [Number]
FROM Data;
union
SELECT Data.Item, 2 AS [Group], Data.[Group 2] AS [Number]
FROM Data;
union
SELECT Data.Item, 3 AS [Group], Data.[Group 3] AS [Number]
FROM Data;
union
SELECT Data.Item, 4 AS [Group], Data.[Group 4] AS [Number]
FROM Data;
union
SELECT Data.Item, 5 AS [Group], Data.[Group 5] AS [Number]
FROM Data;
union
SELECT Data.Item, 6 AS [Group], Data.[Group 6] AS [Number]
FROM Data;
union
SELECT Data.Item, 7 AS [Group], Data.[Group 7] AS [Number]
FROM Data;
union
SELECT Data.Item, 8 AS [Group], Data.[Group 8] AS [Number]
FROM Data;
union
SELECT Data.Item, 9 AS [Group], Data.[Group 9] AS [Number]
FROM Data;
union
SELECT Data.Item, 10 AS [Group], Data.[Group 10] AS [Number]
FROM Data;
union
SELECT Data.Item, 11 AS [Group], Data.[Group 11] AS [Number]
FROM Data;
union
SELECT Data.Item, 12 AS [Group], Data.[Group 12] AS [Number]
FROM Data;
union
SELECT Data.Item, 13 AS [Group], Data.[Group 13] AS [Number]
FROM Data;
union
SELECT Data.Item, 14 AS [Group], Data.[Group 14] AS [Number]
FROM Data;
union
SELECT Data.Item, 15 AS [Group], Data.[Group 15] AS [Number]
FROM Data;
union
SELECT Data.Item, 16 AS [Group], Data.[Group 16] AS [Number]
FROM Data;
union
SELECT Data.Item, 17 AS [Group], Data.[Group 17] AS [Number]
FROM Data;
union
SELECT Data.Item, 18 AS [Group], Data.[Group 18] AS [Number]
FROM Data;
union
SELECT Data.Item, 19 AS [Group], Data.[Group 19] AS [Number]
FROM Data;
UNION SELECT Data.Item, 20 AS [Group], Data.[Group 20] AS [Number]
FROM Data;

then paste this into another blank query:

SELECT DISTINCTROW [Data Query2].Group, Sum([Data Query2].Number) AS [Sum Of Number]
FROM [Data Query2]
GROUP BY [Data Query2].Group;

Databases efficiently store and present 1 to 1 data, not the 2-dimensional table you started with. This is a common problem, which later versions of Access might handle better, perhaps with the table analyzer to "normalize".

Thank you so much for this, really appreciate it. Will try this Monday, cheers

 

[AndyDevine1]

Hi, thanks again, this works brilliantly...

Would you be able to help me some more - imagine the same scenario with heading 1, heading 2, heading 3 running left to right in columns but instead saying heading 1 - holidays, heading 1 - events, heading 1 - other, then same for all other headings...heading 2- holidays, heading 2 - events, heading 2 - other etc, etc....

When the query is run, the list then becomes

Column A - headings (heading 1, heading 2, heading 3 etc), Column B - type (Holidays, events or other) and Column C the value for each?

Many thanks

Andy

 

[AndyDevine1]

Would you be able to help me some more?
The scenario is bit more involved…instead of columns with headings running left to right - heading 1, heading 2, heading 3 etc the structure is:
Headings D onwards: “heading 1 – holidays”, “heading 1 – events”, “heading 1 – other”, then same for all other headings...”heading 2- holidays”, “heading 2 – events”, “heading 2 – other” etc, etc....

When the query is run, the list then becomes
Column A - headings (heading 1, heading 2, heading 3 etc)
Column B – Product Type (Holidays, events or other)
Column C - The total number of each…eg total number of holidays for heading 1, total number of events for heading 1, total number of ‘other’ for heading 1 and so on…

Many thanks

Andy

 

[sheetspread]

Can you show me a before/after example?