SQL Query Trouble in Access

**aglenn** · 12-18-2013, 05:22 PM

I have a table with "A", "B", "C" and "D" fields (4 total columns). "A" and "B" are repeated throughout the table as identifiers to "C" and "D".

My goal is to, using a SQL query, select "A", "B", and ("C" - "D" AS "X"), grouping "X" by "A" and "B".

Can someone help me complete this query? I can provide a sample query, but I am not sure it would be helpful at this point...

Thanks in advance!

**June7** · 12-18-2013, 05:50 PM

Open the query builder, select the table and drag A & B fields to the grid, click Totals button on the ribbon, select GROUP BY under those field. Create a new field with expression: C - D, select SUM under that constucted field. The resulting SQL will be like:

SELECT A, B, Sum(C - D) AS X FROM tablename GROUP BY A, B;

**aglenn** · 12-18-2013, 06:35 PM

D	C	B	A
	2,154.25	1002	445010
	2,154.25	1002	445010
	2,154.25	1002	445010
	2,154.25	1002	445010
	2,154.25	1002	445010
	2,154.25	1002	445010
	2,154.25	1002	445010
	168,954.51	1310	445010
	168,954.51	1310	445010
	168,954.51	1310	445010
	168,954.51	1310	445010
	168,954.51	1310	445010
	168,954.51	1310	445010
	168,954.51	1310	445010
	1,044,008.00	1311	460000
13,589.00		1002	502010
13,692.20		1002	502010
2,738.44		1002	502010
13,692.20		1002	502010
14,001.80		1002	502010
	2,738.44	1002	502010
6,846.10		1002	502010
	6,846.10	1002	502010
18,327.25		1002	502010
10,343.55		1002	502010
5,171.78		1002	502010
	5,171.78	1002	502010
14,238.01		1002	502010
14,025.80		1002	502010
11,220.64		1002	502010
	11,220.64	1002	502010
14,025.80		1002	502010
14,025.80		1002	502010
14,025.80		1002	502010
1,720.55		1002	502010
14,025.80		1002	502010
13,712.49		1002	502010
1,371.25		1002	502010
15,665.80		1002	502010
16,525.00		1002	502010
6,610.00		1002	502010
	1,371.25	1002	502010
11,738.72		1310	502010
10,798.12		1310	502010
2,159.62		1310	502010
8,512.51		1310	502010
10,487.38		1310	502010
	2,159.62	1310	502010
4,256.26		1310	502010
	4,256.26	1310	502010
14,682.24		1310	502010
10,347.50		1310	502010
5,173.75		1310	502010
	5,173.75	1310	502010
10,730.40		1310	502010
11,035.72		1310	502010
8,828.58		1310	502010
	8,828.58	1310	502010
12,763.96		1310	502010
12,124.86		1310	502010
11,252.13		1310	502010
1,348.19		1310	502010
23,933.85		1310	502010
9,678.90		1310	502010
967.89		1310	502010
11,059.77		1310	502010
11,727.74		1310	502010
4,691.10		1310	502010
	967.89	1310	502010
	2,426.14	1002	502020
	367.30	1002	502020
609.98		1002	502020
651.44		1002	502020
	132.69	1002	502020
	1,642.33	1002	502020
2,694.49		1002	502020
354.42		1310	502020
429.62		1310	502020
	263.19	1310	502020
2,001.84		1310	502020
	3,126.25	1310	502020
	3,898.79	1310	502020
	235.44	1310	502020
5,027.93		1002	503450
5,066.11		1002	503450
1,013.22		1002	503450
5,066.11		1002	503450
5,180.67		1002	503450
	1,013.22	1002	503450
2,533.06		1002	503450
	2,533.06	1002	503450
6,781.08		1002	503450
3,827.11		1002	503450
1,913.56		1002	503450
	1,913.56	1002	503450
5,268.06		1002	503450
5,189.55		1002	503450
4,151.64		1002	503450
	4,151.64	1002	503450
5,189.55		1002	503450
5,189.55		1002	503450
5,189.55		1002	503450
636.60		1002	503450
5,189.55		1002	503450
5,073.62		1002	503450
507.36		1002	503450
5,796.35		1002	503450
6,114.25		1002	503450
2,445.70		1002	503450
	507.36	1002	503450
4,343.33		1310	503450
3,995.30		1310	503450
799.06		1310	503450
3,149.63		1310	503450
3,880.33		1310	503450
	799.06	1310	503450
1,574.82		1310	503450
	1,574.82	1310	503450
5,432.43		1310	503450
3,828.58		1310	503450
1,914.29		1310	503450
	1,914.29	1310	503450
3,970.25		1310	503450
4,083.22		1310	503450
3,266.58		1310	503450
	3,266.58	1310	503450
4,722.67		1310	503450
4,486.20		1310	503450
4,163.29		1310	503450
498.83		1310	503450
8,855.52		1310	503450
3,581.19		1310	503450
358.12		1310	503450
4,092.11		1310	503450
4,339.26		1310	503450
1,735.70		1310	503450
	358.12	1310	503450
150.00		1002	506020
117,849.00		1310	506020
68,053.79		1310	506020
240,048.75		1310	506020
8,877.73		1310	506020
117,849.00		1310	506020
117,849.00		1310	506020
8,877.73		1310	506020
240,048.75		1310	506020
117,849.00		1310	506020
3,903.00		1310	506020
1,000.00		1310	506020
240,048.75		1310	506020
4,974.73		1310	506020
117,849.00		1310	506020
13,852.46		1310	506020
300.00		1310	506020
240,048.75		1310	506020
65,000.00		1310	506020
66,661.19		1310	506020
68.69		1310	506020
85.87		1310	506020
67,231.00		1310	506020
240,048.75		1310	506020
117,849.00		1310	506020
12,780.73		1310	506020
117,849.00		1310	506020
59,369.99		1310	506020
68,000.00		1310	506020
240,048.75		1310	506020
68,000.00		1310	506020
117,849.00		1310	506020
	117,849.00	1310	506020
240,048.75		1310	506020
28,645.55		1311	506020
30,289.28		1311	506020
31,263.39		1311	506020
540.00		1311	506020
540.00		1311	506020
540.00		1311	506020
540.00		1311	506020
540.00		1311	506020
540.00		1311	506020
540.00		1311	506020
65,000.00		1310	506030
66,661.19		1310	506030
	65,000.00	1310	506030
	66,661.19	1310	506030
46.00		1002	507310
69.97		1002	507350
2,995.00		1002	508090
181.11		1002	508090
64.51		1002	508090
37.63		1002	508090
54.14		1002	508090
981.88		1310	508090
533.71		1310	508090
11.22		1310	508090
132.64		1310	508090
1,495.00		1310	508090
22.28		1002	508110
243.67		1310	508110
	449.60	1002	509480
999.13		1002	509480
999.13		1002	509480
999.13		1002	509480
999.13		1002	509480
999.13		1002	509480
999.14		1002	509480
999.14		1002	509480
999.12		1002	509480
	999.13	1002	509480
	999.13	1002	509480
	999.13	1002	509480
449.60		1002	509480
999.12		1002	509480
999.15		1002	509480
999.14		1002	509480
	999.12	1002	509480
	38,092.83	1310	509480
17,376.62		1310	509480
17,376.62		1310	509480
17,376.62		1310	509480
17,376.62		1310	509480
17,376.61		1310	509480
17,376.63		1310	509480
17,376.59		1310	509480
17,376.61		1310	509480
	17,376.62	1310	509480
	17,376.62	1310	509480
	17,376.62	1310	509480
38,092.83		1310	509480
17,376.61		1310	509480
17,376.61		1310	509480
17,340.94		1310	509480
	17,376.61	1310	509480
	194,605.64	1311	509480
184,805.97		1311	509480
184,805.97		1311	509480
184,805.97		1311	509480
184,805.97		1311	509480
184,806.17		1311	509480
175,145.75		1311	509480
175,080.92		1311	509480
175,080.91		1311	509480
	184,805.97	1311	509480
	184,805.97	1311	509480
	184,805.97	1311	509480
194,605.64		1311	509480
175,080.91		1311	509480
175,080.92		1311	509480
175,080.97		1311	509480
	175,080.91	1311	509480
46.27		1002	520010
42.66		1002	520010
44.98		1002	520010
76.97		1002	520010
1,012.81		1002	520010
68.99		1002	520010
199.95		1002	520010
45.49		1002	520010
145.86		1310	520050
1,016.72		1002	525010
1,016.72		1002	525010
1,016.72		1002	525010
1,016.72		1002	525010
1,016.72		1002	525010
1,016.72		1002	525010
1,016.72		1002	525010
4,183.23		1310	525010
4,183.23		1310	525010
4,183.23		1310	525010
4,183.23		1310	525010
4,183.23		1310	525010
4,183.23		1310	525010
4,183.23		1310	525010
10.91		1310	530010
1,862.98		1002	530030
1,081.03		1002	530030
5,506.15		1310	530030
6,827.95		1310	530030
6,436.78		1310	530030
6,538.54		1310	530030
59,369.99		1310	530030
5,634.60		1310	530030
	59,369.99	1310	530030
1,765.03		1310	530030
1,765.03		1310	530030
1,765.03		1310	530030
1,765.03		1310	530030
1,561.05		1310	530030
1,969.00		1310	530030
1,765.03		1310	530030
1,226.60		1311	530030
1,227.40		1311	530030
1,227.40		1311	530030
1,227.40		1311	530030
1,227.40		1311	530030
1,227.40		1311	530030
1,227.40		1311	530030
117,849.00		1310	535010
	117,849.00	1310	535010
144.25		1002	600045
8.42		1002	600045
128.04		1002	600045
7.88		1002	600045
128.51		1002	600045
65.86		1002	600045
7.72		1002	600045
128.04		1002	600045
7.46		1002	600045
9.07		1002	600045
128.16		1002	600045
129.38		1002	600045
401.41		1310	600045
1,968.00		1310	600045
377.91		1310	600045
1,968.00		1310	600045
383.46		1310	600045
1,968.00		1310	600045
380.22		1310	600045
1,968.00		1310	600045
1,968.00		1310	600045
386.20		1310	600045
379.93		1310	600045
2,000.00		1310	600045
2,000.00		1310	600045
736.25		1310	600055
17,956.75		1310	600140
17,956.75		1310	600140
17,956.75		1310	600140
17,956.75		1310	600140
17,956.75		1310	600140
17,956.75		1310	600140
17,956.75		1310	600140
8,615.51		1310	600250
8,615.51		1310	600250
8,615.51		1310	600250
8,615.51		1310	600250
8,615.51		1310	600250
8,615.51		1310	600250
8,615.51		1310	600250

I tried the solution you offered (after coming to a similar conclusion myself, but the results do not return properly. When selecting and grouping the fields individually, the correct information is returned, but when using the aggregate function (SUM), the returned result is in error. I am not sure why... Here's the SQL query used:

SELECT t.A, (Sum(t.C) - SUM(t.D)) AS X
FROM t
GROUP BY t.A;

This query returned results, but they were incorrect.

I also tried:

SELECT t.A, Sum((t.C) - (t.D)) AS X
FROM t
GROUP BY t.A;

This query returned null values in the X field.

Hmm....

Originally Posted by June7

Open the query builder, select the table and drag A & B fields to the grid, click Totals button on the ribbon, select GROUP BY under those field. Create a new field with expression: C - D, select SUM under that constucted field. The resulting SQL will be like:

SELECT A, B, Sum(C - D) AS X FROM tablename GROUP BY A, B;

**June7** · 12-18-2013, 06:46 PM

Arithmetic with null returns null:

SELECT A, B, Sum(Nz(C,0) - Nz(D,0)) AS X FROM tablename GROUP BY A, B;

That is not a normalized data structure. I had expected data to be in both fields for all records.

Don't need B in the grouping? The first query should be correct regardless of the null fields.

**aglenn** · 12-18-2013, 06:51 PM

Gotcha! Doh!

SQL Query Trouble in Access

Thread Tools

SQL Query Trouble in Access

Tried your solution, but not working?

Similar Threads

Help with Access - Having trouble with joins

Report trouble in Access 2000

Trouble with Query

VB code in Access '07 trouble

Having trouble with some Access tables

Posting Permissions