Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Unique Records (AC97 SR-1)

    Problem: Machine Utilisation Report, for a number of machines in a Production line.

    Table has a 4 part key. DATE;SHIFT;MACHID;SIZE

    Shift has values of Day;Afternoon;Night
    Machine Id has values 1-9
    Size has values 15;30;45;50;60;90

    For three days I have been trying to determine the count of Date;Shift;MachId combinations (ie excluding Size.) There is a 'Between Dates' parameter to the query which works just fine. BUT! It is possible for multiple sizes to go down the line during a shift. I am trying to find the number of shifts so I can multiply by 8 hours to show 'Available Time' , and compare to 'Actual Run Time'. All my efforts so far, count say a run of 30 for 3 hrs and a run of 60 for 5 hours as two shifts instead on one. Therefore my available hours become 16, instead of 8.

    I have tried using the 'Duplicate Records' Query Wizard, and I know I'm close, but alas no cigar.

    I know someone will point me in the right direction. Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unique Records (AC97 SR-1)

    First, create a query based on your table. Add the Date, Shift and MachID fields. Set the parameter for the Date field.

    Make the query into a Totals query. The Totals option for all fields will be Group By. That is OK.

    Save this query. If you open it, you will see the unuque combinations of Date, Shift and MachID.

    Next, create a new query, based on the query you have just saved. In the first field, type Combinations:Count(*)

    Save this query. If you open it, you will see the number of unique combinations.

    Another way to get the count of combinations is to use DCount("*","qryUnique") where qryUnique should be replaced by the name of the first query. This expression can be used in a query, or in the control source of a text box on a form or report: =DCount("*","qryUnique")

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unique Records (AC97 SR-1)

    Thanks Hans I will try it now.


    Geez I luv this forum!!

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unique Records (AC97 SR-1)

    Beudy Mate!
    I modified your post slightly, by including the Machine Id as a field in the second query, and convertiing it to a TOTALS query.

    Gave me exactly what I wanted.

    Thanks again.


    BTW, was impressed with Holland in the World Cup. You guys will improve greatly with more International Competition.

Posting Permissions

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