Problems with [Group by] in SQL

[GeedieZ]

Hey, I've got a tableA, that looks like this:

worker_id (number)
part (text)
a_date (date/time)
time_start (date/time)
time_end (date/time)
bad_qty (number)
qty (number)
bad_percent (number)
total_time (number)
efficiency (number)

Some data:

294	PART A	2009-08-12	07:40:00	14:25:00	1	295	0,34	405	43,7
298	PART B	2009-08-12	14:40:00	15:05:00	0	40	0	25	96
321	PART C	2009-08-12	11:30:00	12:30:00	1	30	3,33	60	30
321	PART D	2009-08-12	08:45:00	09:30:00	0	60	0	45	80
321	PART D	2009-08-12	09:40:00	11:45:00	1	95	1,05	125	45,6
321	PART B	2009-08-12	06:40:00	08:40:00	0	170	0	120	85
321	PART B	2009-08-12	12:40:00	14:25:00	0	140	0	105	80
322	PART E	2009-08-12	06:40:00	12:05:00	0	251	0	325	46,34
322	PART F	2009-08-12	12:15:00	14:25:00	5	96	5,21	130	44,31
324	PART C	2009-08-12	10:25:00	12:20:00	0	100	0	115	52,17
324	PART D	2009-08-12	08:45:00	10:15:00	0	122	0	90	81,33
324	PART B	2009-08-12	06:40:00	08:40:00	0	180	0	120	90
324	PART B	2009-08-12	12:40:00	14:25:00	0	190	0	105	108,57
325	PART D	2009-08-12	08:40:00	12:54:00	2	260	0,77	254	61,42
325	PART D	2009-08-12	13:00:00	14:25:00	1	150	0,67	85	105,88

What I'm trying to achieve is to sum all workers [qty] for each [worker_id] for each part. So, I want to know:
How many qty and bad_qty of each part did a specific worker do on a given date. I've tried to use GROUP BY function, but I get 3122 error. Here's the sql I was using (plus some form-basem filters which I haven't written here):

SELECT tableA.part, tableA.worker_id, tableA.a_date, sum(tableA.qty) as tot_qty, sum(tableA.bad_qty) as tot_ng, tableA.bad_percent , tableA.efficiency, Round(((tableA.total_time)/60),2) AS prod_time
FROM tableA
WHERE (((tableA.a_date)>=[Forms]![part_rep_form]![Tekst0] And (tableA.a_date)<=[Forms]![part_rep_form]![Tekst2]))
GROUP BY tableA.worker_id, tableA.part 
ORDER BY tableA.worker_id, tableA.a_date;

I'd be grateful for any help.

 

[DCrake]

QrySQL = "SELECT part, worker_id, a_date, Sum(tableA.qty) as tot_qty, Sum(bad_qty) As tot_ng, bad_percent , efficiency, Round((total_time/60),2) As prod_time
FROM tableA
WHERE a_date Between #" & [Forms]![part_rep_form]![Tekst0] & "# And #" &[Forms]![part_rep_form]![Tekst2] & "# GROUP BY worker_id, part ORDER BY worker_id, a_date;"

This is how your sql should look. Have not tested it but it is syntactically correct. Notice how the dates are enclosed in #'s and are ampered if you was to do a Debug.Print QrySQL following this line you would see the results.

David

 

[GeedieZ]

The problem is not with the dates, it's with the GROUP BY - Access is giving me an 3122 error:

"You tried to execute a query that does not include the specified expression a_date as part of an aggregate function. (Error 3122)"


Sorry, propably should've mentioned this earlier, but it has occured to me just now, but those fields are from a view and they are all FKN:
worker_id
a_date
part
time_start
time_end