Results 1 to 13 of 13

Thread: query help

  1. #1
    Join Date
    Oct 2007
    Posts
    34

    Unanswered: query help

    Can you tell me how to make this work.
    I have 3 fields; date, pass/fail, qty checked.
    date is a date field
    pass/fail is a text field where a person would enter either p or f
    qty is number field

    how do I make a query show how many passed and how many failed by date and in relation to how many were checked on that date

    thanks,
    Don.....

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Create a query, then group by date and use the Count function to count how many a P and how many are F when the qty checked is True

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't think you can do this in a single query, or at least if you can I don't know how to do it... not that that means anything

    What I'd do is create a query that COUNTs the number of tests that failed, and the quantity that failed grouped by day. eg:-
    Code:
    SELECT inspdate, Sum(QTY) AS NoFailed, Count(passfail) AS NoFailedTests
    FROM mytable
    WHERE passfail="F"
    GROUP BY inspdate;
    note I used inspdate rather than date to avoid using a reserved word AND passfail to avoid using a reserved symbol '/' both of which could cause problems

    do the same for passed tests
    Code:
    SELECT inspdate, SUM(QTY) AS NoPassed, count(passfail) AS NoPassedTests
    FROM mytable
    WHERE passfail="P"
    GROUP BY inspdate;
    I saved these queries as qNoFailed and qNoPassed respectivley

    then created a third query including your table and the two new queries
    add the iunspection date from your table and the No???? & No????Tests from both queries
    add two additional columns
    QtyTested:NoFailed+NoPassed
    NoTests:NoFailedTests+NoPassedTests

    define a join between your table and the inspection date in both queries. then define the join/relationship( click on the inspection date in your table and drag to the inspection date in the no failed query, repeat for the no passed query. The you need to refine the relationship (right click on the relationship line)to return all rows from the table and only those rows that matched in the no???? queriy, repeat for both realtionships. this allows for there to be no failed or no passed rows but report rows where the other rows were found. so if your fail rate one day is 0 then you will get a row returned for that day for passed items

    then switched the query designer view to SQL and altered
    Code:
    SELECT mytable.InspDate
    to
    Code:
    SELECT DISTINCT (mytable.InspDate) AS InspectionDate
    so that you only get one row per inspection date in your table

    that will then give you your summarised data

    the date
    the total number of failures for that date
    the total number of failured tests for that date, if any
    the total number of Passes for that date, if any
    the total number of Passes tests for that date, if any
    the total number of tests taken
    the total number of items tested

    you could add other columns if you wished, but equally you could do the final calculations in a form or report, which would be my preference

    so thats three queries to extract data from one table. or if you are a fan of Tolkein two queries, and one to bind them

    as said before someone whose SQL is better than mine may well have a single query answer, I don't
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2012
    Posts
    24
    Thanks for you help it's much appreciated.
    Poppa,
    I've tried what you said and I haven't been able to get the info I was looking for. That may not be because your advice was wrong but very possibly because I wasn't clear on what I was looking for.

    Healdem,
    I've read your suggestions but honestly I wouldn't know where to start. I'm very much a "hacker" at all of this. I'm willing to learn and do but I need lots of instruction if I'm to do anything as you've suggested.

    Maybe if I try to explain again what I'm trying to do it will help everyone, including me.

    I'd like to be able to sort my data by date.
    I'd like to sum all the parts checked on each date.
    I'd like to see how many have passed and how many have failed on each date, by, the total qty checked on those dates.

    So I might have something that looks like this:
    Date Checked Pass/Fail
    11/24/13 100 80P 20F
    11/25/13 100 60P 40F

    Does this make sense to you guys?

    Thanks again for all your help and I apologize for my questions and lack of underestanding.

    Don...........

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try to follow the step suggested in post #3. and come back if its not working for you
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2012
    Posts
    24
    Honestly, I don't know how to do what post 3 is saying.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm very much a "hacker" at all of this. I'm willing to learn and do but I need lots of instruction if I'm to do anything as you've suggested.
    well you arent' really trying to learn that much are you

    OK to make it simpler
    1) modify your table:-
    ..rename date to inspdate
    ..rename pass/fail to passfail

    2) open the query designer
    switch to SQL View (under the file menu option the view button look for 'sql view')
    cut and paste the following into the Query
    Code:
    SELECT inspdate, SUM(QTY) AS NoPassed, count(passfail) AS NoPassedTests
    FROM mytable
    WHERE passfail="P"
    GROUP BY inspdate;
    ***Note you will need to replace 'mytable' with whatever your table is called
    save that query as qNoPassed


    3) repat the process, this time cut and paste
    Code:
    SELECT inspdate, Sum(QTY) AS NoFailed, Count(passfail) AS NoFailedTests
    FROM mytable
    WHERE mytable.passfail="F"
    GROUP BY inspdate;
    ***Note you will need to replace 'mytable' with whatever your table is called
    save that query as qNoFailed

    4) create another query, copy and paste the following into that query
    Code:
    SELECT DISTINCT (mytable.InspDate) AS InspectionDate, qNoFailed.NoFailed, qNoFailed.NoFailedTests, qNoPassed.NoPassed, qNoPassed.NoPassedTests, [NoFailedTests]+[NoPassedTests] AS NoTests, [NoFailed]+[NoPassed] AS QtyTested
    FROM (mytable LEFT JOIN qNoFailed ON mytable.InspDate = qNoFailed.inspdate)
    LEFT JOIN qNoPassed ON mytable.InspDate = qNoPassed.inspdate;
    ***Note you will need to replace 'mytable' with whatever your table is called
    save the query as, say qTestDatebyDate

    5)run the query
    Last edited by healdem; 11-25-13 at 07:41.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Oct 2007
    Posts
    34
    I wish I could do things like you do but my job doesn't give me that opportunity. Several years ago I wanted to create a db for myself that would help me at work. Our IT dept. doesn't have time to do things for individuals so I enlisted help from people like yourself. The db works great and I use it every day. Now I'm in need of another one. So here I am asking for help again. Most of what I did years ago has been forgotten. I really do appreciate your help.

    So I did what you asked and I got something that is going in the right directions. I do have a couple of questions and a few things I'm not sure of.

    First, when the query runs a box opens asking for a "qty". Quantity is really not needed but a Start Date and End Date would be helpful (actually I think I can do this by copying what I did in my other db).

    Next, the fields NoFailed, NoPassed, NoTest are blank. I'm wondering if they're necessary.

    Also, the QtyTested field didn't have quantities in it. It should have displayed 77, 85, 50. I showed them in red.

    I've attached a picture that hopefully will help you understand what I've said here.

    Here are the queries as they appear in my db :

    SELECT [inspdate], SUM(QTY) AS NoPassed, count([passfail]) AS NoPassedTests
    FROM Independence
    WHERE passfail="P"
    GROUP BY [inspdate];

    SELECT [inspdate], Sum(QTY) AS NoFailed, Count([passfail]) AS NoFailedTests
    FROM Independence
    WHERE [Independence].[passfail]="F"
    GROUP BY [inspdate];

    SELECT DISTINCT ([Independence].[InspDate]) AS InspectionDate, qNoFailed.NoFailed, qNoFailed.NoFailedTests, qNoPassed.NoPassed, qNoPassed.NoPassedTests, [NoFailedTests]+[NoPassedTests] AS NoTests, [NoFailed]+[NoPassed] AS QtyTested
    FROM (Independence LEFT JOIN qNoFailed ON Independence.INSPDATE = qNoFailed.inspdate) LEFT JOIN qNoPassed ON Independence.INSPDATE = qNoPassed.inspdate;


    Once again I really do appreciate your time and patience,
    Don............
    Attached Thumbnails Attached Thumbnails On Run.jpg  

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    First, when the query runs a box opens asking for a "qty". Quantity is really not needed but a Start Date and End Date would be helpful (actually I think I can do this by copying what I did in my other db).
    if you are getting a prompt box up then the SQL runtime is saying it doesn't know what 'qty' is so it asks you for the value to use in place.
    the proble,m is that the column that holds the quantity is not called 'qty'
    so the fix is to either change the queries and replace 'qty' with what ever your quantity column is called in your table OR change the quantity column in your table to qty. bear in mind I don't have you detailed table design, I don't know what your columns are called, what datatype they are so sometimes you just have to guess

    Next, the fields NoFailed, NoPassed, NoTest are blank. I'm wondering if they're necessary.
    I guess they aren't neccessary, but then again you want to do some calculations. in your original post you said you required
    how do I make a query show how many passed and how many failed by date and in relation to how many were checked on that date
    although you can derive the quantity tested by adding quantity failed and quantity passed, the query presents that to you automatically. incidentally if you do decide you don't want that data then personally I'd leave it in the query and just not pull the data through on your for or report. I would expect that its the sort of metrics that may be of use at some stage and it doesn't cost a great deal (in terms of processor time or resources) to get the information

    as to what start date and end date are I dunno.. is that something you've just made up or forgot to post in the original question ?

    if this is a query you are going to run frequently then it may be smarter to parameterise it and create a stored procedure that accepts your cut off points. If that is beyond your comfort zone then I'd write the queries as required (with your cutoff dates to the query collection and then execute them as required.
    to do that limit the rows concerned by adding to the where clause
    Code:
    WHERE passfail="P" AND inspdate BETWEEN #LOWERLIMIT# and #UPPERLIMIT#
    say you wanted rows for october 2013
    Code:
    WHERE passfail="P" AND inspdate BETWEEN #2013/10/01# and #2013/10/30#
    ..tbh that may fail so you might have to use US formatfor dates, but the one thing I can guarantee is that you will need to delimit date literals with the # symbol
    Code:
    WHERE passfail="P" AND inspdate BETWEEN #10/01/2013# and #10/30/2013#
    if you have stored the time element on your inspection date then you may need to add one to the upper limit
    Code:
    WHERE passfail="P" AND inspdate BETWEEN #10/01/2013# and #11/01/2013#
    the reason being that Access/JET stores dates as datetime values, so if you set your upperlimit to be the day you want (30 oct 2013), the sql engine interprets that as 30 Oct 2013 00:00:00, not 30 Oct 2013 23:59:59

    the where clause is used to limit the number of rows returned so it will pay to apply that filter to the sub queries AS WELL as the final query, although it may not be significant if you want to keep your queries simple

    if you run these queries from within the form you want the results from then you could pull the values for the dates from that form. but for now just get the damn queries working and worry about refining things after the event
    Once again I really do appreciate your time and patience,

    Don............
    its what a site like this is about, helping people solve their problems... but usually you have to do a lot of the work for yourself, there isn't always enough time for contributors to create a db, add a table, add data, then create the queries and so on...
    the more information you can provide, the more detail the better.

    often the best approach is to zip your db and attach it as part of a post having
    copied your original to another file
    using that copy stripped out anything confidential
    ...or anything not relevant
    ...or anything that would cause you problems if it got out in to the public domain. this site is actively trawled by spiders so anything added to this site is in the public domain
    then compact and repair a copy of that db
    then zip that copy and attach it to a post
    Last edited by healdem; 11-25-13 at 15:35.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2007
    Posts
    34
    Okay I understand. I changed the "Qty" to what my value is "lotsize". BTW, I originally had it as "Lot Size" but when I inserted that for qty I got an error messge. I eliminated the space between the two words and everything is working and correct. Including the columns that didn't have anything in them before.

    Now I need to duplicate this for 18 other products. I should be able to do that by just copying what you've given me, or so I believe.

    Thank you very much for your help and I hope I won't have to bother you again with this. Also I doubt I'll be doing any creating of db going forward as I hope to retire in the next couple of years.

    Thanks again,
    Don..........

  11. #11
    Join Date
    Oct 2007
    Posts
    34
    Okay so I'm pulling back on my promise of not bothering you. In my other db I have a form called "Set Date" and it's written like this"
    Between [forms]![set date].[start date] and........ (same thing for the end date).
    I've also used "Between [beginning date] and [end date].

    Both of these work well in my other db but when I tried them in this one they don't work. So I'm trying to understand what you've written on dates and date ranges.

    Where would I insert the code you've mentioned above?
    Don...........

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you use a space in a table or column name then you must delimit the table/column name with square brackets. eg:
    [my table].[my column]

    Between [forms]![set date].[start date] and........ (same thing for the end date).
    shows you are using values pulled from a form, which is a smart move. just bear in mind that the specified form MUST be open when you try to run the query, otherwise you will bet a similar message as with qty before, when the SQL runtime doens't know what the value is.

    when you say it doesn't work I'd guess you haven't got a form called 'set date' in the current db. so the solution is to create a form that captures that data or change the reference to [set date] to the name of the form that does exist. bear in mind you will also need to use the same control names. you will make your life a hedck of a lot easier if you use either underscores or camel case to differentiate elements of your column/table names.
    don't use spaces as you will have to delimit with [ & ], eg:- [my column name]
    instead use underscores:- my_column_name
    or CamelCase:- MyColumnName
    I think the underscore is more favoured as the recommendation is that tabel and column names should all be lower case to redce complications of porting code from db to db and server to server, but in Access it doesnt' really matter

    im slightly concerned to hear you say that you now need to apply this to 17 other products... I fear that indicates your physical design (of tables and columns isn't 'right').

    if your products test results were all stored in the same table then you'd end up with 3 queries rather than 18x3 queires.

    right now prove your queries work, prove they give the results you want. then and only then start to embellish with a cutoff date.
    Last edited by healdem; 11-26-13 at 09:10.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Oct 2007
    Posts
    34
    The queries you provided do work and report what I'm looking for. So now I need to take the next step and that is to create more queries after I create the tables and forms.
    My project consists of many different aspects. The part of the project you helped me with is for one product in one operation. There are several operations; packaging, machining, hydro testing, and shipping. Each of these has several different products or parts. We audit these with log sheets and the results from the audits will go into a summary via the queries you provided. In the end I will issue a report that will summarize pass/fail for each of the operations.
    Now, after your help on the queries, I'm beginning to see how big this task is. I'm going to pursue it anyway because if I can complete this it will save me about 8 hours per week of data entry.
    Thanks much for your help, I can't tell you how much I appreciate what you've done.

    Don..............

Posting Permissions

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