Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    We have two tables: Product and Process.

    Product table:
    [Product Id]
    101
    102
    103
    ... etc.

    Process table:
    [ProcessId], [ProductId], [ProcessType], [Personnel]
    2001, 101, A, Smith
    2002, 101, B, Black
    2003, 102, A, Jones *
    2004, 103, A, White
    2005, 103, B, Smith
    ... etc.

    * Most records in the Product table have a pair of corresponding records in the Process table, but some have only a single corresponding record (i.e., ProcessType = A only), like this one. Is it possible to create a query to count such products, by personnel?
    e.g.: Jones 1

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Create a query based on the Process table.
    Add the Personnel and ProductID fields.
    Select View | Totals.
    Set the Total option for the ProductID field to Count.
    Switch to datasheet view to see the result.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. We only need to count the number of products that have a single process (i.e., they have an A but not a B ). We have now created a pair of queries - QueryA listing type A processes, QueryB listing type B processes - with a third (total) query left-joining the first two queries, and an "Is Null" criterion on [ProductId] in QueryB, to count the null matches.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You hadn't told us whether it's possible that there are more than 2 records for a personnel member and product. If not, you could creaste a totals query based on the Process table with Personnel (Group By), ProductID (Group By) and ProcessType (Count), and enter 1 in the Criteria line for the ProcessType column.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •