Results 1 to 2 of 2
  1. #1
    GeedieZ is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    1

    Problems with [Group by] in SQL

    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:
    Code:
    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):

    Code:
    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.

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435

    Answer available on AWF

    I have provided a solution on Access World forums for you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. option group in form
    By mawa4492 in forum Forms
    Replies: 1
    Last Post: 08-05-2009, 02:49 PM
  2. Group By
    By SMAlvarez in forum Access
    Replies: 0
    Last Post: 10-28-2008, 04:40 PM
  3. Top 2 by group
    By griz in forum Access
    Replies: 0
    Last Post: 05-25-2008, 07:10 AM
  4. Group Totals in a form
    By mai1081 in forum Forms
    Replies: 1
    Last Post: 05-14-2008, 06:11 PM
  5. New page before strating a group
    By wasim_sono in forum Reports
    Replies: 2
    Last Post: 11-06-2006, 03:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums