iSixSigma

Data Collection Using Acess Database

Six Sigma – iSixSigma Forums Old Forums General Data Collection Using Acess Database

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #31003

    Kwang Hub Pak
    Participant

    Have a problem using Access Database to calculate Mean and Range. Does anyone know how to use the build expression in query to calculate mean and range of data collected from 5 different data fields? For example I have 5 points of data collected (hole size), I need to calculate mean and range in access.

    0
    #81458

    Logan Luo
    Participant

    Try these:
    Mean:
    in the Query filed type in: Mean: =avg([your field name])
    Range:
    type in: Rang: =max([your field name]-min([your field name])
    You have to eliminate other fields to have it working right.
    let me know if it works. I am working on an Access database, which can calcualte DPU, DPMO, Yield, Cycle Time and ect. If you are interested in it, I can send you one for testing when it is ready.
    Logan Luo

    0
    #81461

    Kwang Pak
    Participant

    I have a table that have 5 fields. Hole 1, Hole 2…… I’ve create a query using this table so I could create another field in query for calculating the mean and range. When I write the expression 
        Mean:=avg([Hole1],[Hole 2],……..) 
    I get an error message – can you help?

    0
    #81464

    GE BB
    Participant

    Kwang,
    When your in the Query mode and creating a new query, add the Table (wih the data) you want to create the average for.

    In the Field field, type “Average: Avg([data_field_name])” (without the quotes.
    Make sure the “Show” box is checked.
    The first “Average” is just the title displayed at the top of the results. You could use any value you wanted. The “Avg” calls the function. The Table field name I used was “data” in the Table storing the data, but yours would probably be “Hole Data” — don’t use the individual data points, but the field name.
    Does that make sense?
    GE BB

    0
    #81466

    Kwang Pak
    Participant

    If there is anyone out there an Access Database expert or somewhat experience person, please give me a call at 731-265-2277.
    Thanks

    0
    #81471

    Logan Luo
    Participant

    You will not have the average of 5 fields in this way. You have to Union all five fileds (put them in one field) before you can have the average. Some knowledge of SQL language are requried here. You can copy following code and past to SQL. (New a query –>select sql from View–>cut ans past the code–>chage the table name to the real table name)
    SELECT Hole1 as [AllHoles]FROM table nameUNION SELECT Hole2 as [AllHole]FROM table nameUNION SELECT Hole3 as [AllHoles]FROM table nameUNION SELECT Hole4 as [AllHoles]FROM table nameUNION SELECT Hole5 as [AllHoles]FROM table name;

    It seem the table design that cased the problem. You should consider redegin the table structure to following:
    Meansurement ID
    Measurement (record Hole1, or Hole2 or etc.)
    MeasureResults (record the results)
    Let me know if you still have problem. [email protected]
     

    0
Viewing 6 posts - 1 through 6 (of 6 total)

The forum ‘General’ is closed to new topics and replies.