Simple Query problem ... please help

[zkosky]

My table (called tbl_Job_Asset_Emp) is as below.

4155_HW6_MAS_Data.xls
	A	B	C	D	E
1	JobNumber	AssetType	EmpDeptNo
2	1001	1	10
3	1001	1	12
4	1002	2	32
5	1002	2	34
6	1003	3	40
7	1003	3	45
8	1004	4	52
9	1004	4	54
10	1005	5	80
11	1005	7	80
Job_Asset_Emp

 

[zkosky]

Re: Simple Query problem ... please help -- APPEND1

My Second Table (called tblTotal_Pay_per_Dept) is as follows:<SCRIPT language=JavaScript

src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT>

4155_HW6_MAS_Data.xls
	F	G	H	I
6	Emp#	Dept	TotPay
7	101	12	58016
8	102	10	58016
9	103	32	16974
10	104	32	19674.2
11	105	34	26076
12	106	45	32594.1
13	107	54	46412.8
14	108	34	15925.2
15	109	32	8647.5
16	110	32	26096.7
17	111	40	37827
18	112	32	15062.6
19	113	32	17935.2
20	114	10	18625.5
21	115	45	34893.2
22	116	80	49764
23	117	32	17166.6
24	118	34	17962
25	119	52	20083.2
26	120	54	37572.1
27	121	10	9718.75
28	122	12	14247.5
29	123	80	44548
30	124	40	30572.6
31	125	32	9418.2
32	126	45	25864
33	127	12	36981
34	128	10	7159.5
35	129	34	16039
36	130	54	34067
37	131	52	24017.75
38	132	45	31118.65
39	133	54	47656
40	134	34	15682.8
41	135	80	33566.4
42	136	10	18918
43	137	32	19116
44	138	34	27516.15
45	139	45	30601.8
46	140	32	18509.4
47	141	10	31356
48	142	40	20637
49	143	45	8117.2
50	144	80	7014
51	145	54	12672.8
52	146	52	2304.8
53	147	32	1688.4
54	148	34	1058.4
55	149	52	897.6
56	150	32	767
Job_Asset_Emp

When I run the following query
SELECT tbl_Job_Asset_EmpDept.JobNumber, tbl_Job_Asset_EmpDept.EmpDeptNo,

tblTotal_Pay_per_Dept.DeptNo, tblTotal_Pay_per_Dept.TotPay
FROM tblTotal_Pay_per_Dept, tbl_Job_Asset_EmpDept
WHERE (((tbl_Job_Asset_EmpDept.JobNumber)=1005) AND ((tbl_Job_Asset_EmpDept.EmpDeptNo)=

[tblTotal_Pay_per_Dept]![DeptNo]))
ORDER BY tbl_Job_Asset_EmpDept.JobNumber;

The purpose of my query is to sum the TotPay for each department. It does so accordingly except

it doubles the TotPay for department 80. I will post the results so that you can see it.

Thanks for any help you can give. I've searched the web and have been working on this problem for

the past 6 hours and haven't had much luck.

Thanks again!!

 

[Andrew Fergus]

Hi, you might want to re-post the html version of your spreadsheet - it might be too big. Can you include details of the fields included in the tblTotal_Pay_per_Dept?
Andrew