Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2012
    Posts
    14

    Unanswered: 'Count' function in a query

    I have a table which records the status of various pieces of equipment. The result entered into the table will be either Pass or Fail. I'm trying to set up a query which will count 1)The number of Passes 2)The number of Fails and 3)
    The number of units yet to be tested in each area of the site.

    For No. 3 would i need a separate table with a list of all the equipment and the area it's located ?

    I'm trying to use the Count function but keep getting some kind of Mismatch error

    Thanks

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    From the error message, I suspect that you're referring to a Text Field with syntax used for a Number Field, or vice versa, but for us to help you we need to actually see the code/SQL you're using!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jul 2012
    Posts
    14
    My apologies...i'm learning but i'll get there

    I have one table called Tbl_FactoryAreas which contains 8 possible locations for machinery
    I have another table called Tbl_SampleResults which records the results for each piece of machinery. The 2 tables are linked.

    Here's the SQL of the query

    Code:
    SELECT Tbl_MainRecs.SampLocation, Tbl_SampleResults.SampleLocation, Count(Tbl_SampleResults.PassFail) AS CountOfPassFail
    FROM Tbl_MainRecs INNER JOIN Tbl_SampleResults ON Tbl_MainRecs.RecordID = Tbl_SampleResults.RecordID
    GROUP BY Tbl_MainRecs.SampLocation, Tbl_SampleResults.SampleLocation;
    Thanks

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Maggie34 View Post
    I have one table called Tbl_FactoryAreas which contains 8 possible locations for machinery
    I have another table called Tbl_SampleResults which records the results for each piece of machinery. The 2 tables are linked.

    Here's the SQL of the query

    Code:
    SELECT Tbl_MainRecs.SampLocation, Tbl_SampleResults.SampleLocation, Count(Tbl_SampleResults.PassFail) AS CountOfPassFail
    FROM Tbl_MainRecs INNER JOIN Tbl_SampleResults ON Tbl_MainRecs.RecordID = Tbl_SampleResults.RecordID
    GROUP BY Tbl_MainRecs.SampLocation, Tbl_SampleResults.SampleLocation;
    1) Your data is not normalized if those are the only two tables involved. You should have seperate tables:
    a - to identify each piece of equipment, including type, model, serial, etc.
    b - location
    c - test results
    Table b should have a one-to-many relationship to table a.
    Table a should have a one-to-many relationship to table c.
    Both table a and table b can use a primary key (autonumber) as their index. Those pk's can be used as foreign keys in the table on the 'many' side.

    2) Why are you displaying both Tbl_MainRecs.SampLocation and Tbl_SampleResults.SampleLocation? Aren't they the same? Should they be?

    Sam

  5. #5
    Join Date
    Jul 2012
    Posts
    14
    Hi Sam,
    There is actually a third table called Tbl_MainRecs which records the date and the area the equipment is located.

    Tbl_MainRecs.SampLocation and Tbl_SampleResults.SampleLocation are actually not the same but i could have named the fields a bit better i admit.
    You're right, the database is probably not normalized up to the standard it should be but i'm only at this about 3 months so i've a lot to learn.

    Thanks

    Here are the relationships as things stand

    Tbl_MainRecs --> Tbl_SampleResults (One to Many)
    Tbl_MainRecs --> Tbl_FactoryAreas (One to Many)
    Tbl_SampleResults --> Tbl_SampleLocations (One to Many)

    Sorry if it's a little confusing

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Maggie,

    I don't find it confusing. However, in the relationship between
    Tbl_SampleResults --> Tbl_SampleLocations (One to Many)
    Please tell us what those two tables are for.

    Also, could you tell us what circumstances cause the Mismatch error? Missinglinq was right in saying that's usually reserved for mixing up text and numeric fields, but that doesn't seem to be the case here.

    Sam

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What are the Datatypes of Tbl_MainRecs.RecordID and Tbl_SampleResults.RecordID? Actually go to the Design View for each Table and check. Even if they're both comprised entirely of 'digits,' it does not mean that they are both defined as Numbers.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Jul 2012
    Posts
    14
    Sam,
    Tbl_SampleLocations is a list of all the equipment and it's location. Tbl_SampleResults contains the test results for each piece of equipment. There are 4 results to be entered per piece. I've got a data input form which is used to enter these results. At the top of the form is a combo box (whose source is Tbl_FactoryAreas) where you can select the area which contains the equipment you're entering results for and also a control to enter the date. Beneath this is a subform (whose source is Tbl_SampleResults) where you enter the results for each bit of equipment tested...that's the best way i could think of doing it.

    Missinglinq,
    Tbl_MainRecs.RecordID and Tbl_SampleResults.RecordID are both Autonumbers

    Thanks for your time guys

    Edit : Sorry....about the Mismatch error. I was trying to create a query where the user cound enter a Factory area and see the total amount of passes and fails in that area this year to date. I was also going to try and allow the user to see how many pieces of equipment in that area have not been tested yet this year. The mismatch occured when i entered a Count function on the 'Pass/Fail' field which is 'Text'
    Last edited by Maggie34; 07-19-12 at 07:39.

  9. #9
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I see a couple of prooblems here. Not that any of them explain the Mismatch error message, but maybe if you see what I'm saying, and you fix the errors, it might go away on its own.

    1)
    Missinglinq, tbl_MainRecs.RecordID and Tbl_SampleResults.RecordID are both Autonumbers
    The error is obvious. In the previous post, you say that
    Here are the relationships as things stand Tbl_MainRecs --> Tbl_SampleResults (One to Many)...
    If there is a one-to-many relationship between fields then the IDNumbers will almost never be equal. Example: Tbl_MainRecs has a piece of equipment, with IDNumber 1. In Tbl_SampleResults, the first four records (IDNumbers 1 thru 4) refer to the same piece of equipment. Tbl_MainRecs now has a new record, new equipment, with IDNumber 2. See? The IDNumber 2 in Tbl_MainRecs and the IDNumber 2 in Tbl_SampleResults are referring to two different pieces of equipment.

    The correct way to do this is to use Tbl_MainRecs.IDNumber in Tbl_SampleResults as a foreign key called, perhaps, EquipID, datatype Long. Your join in the query should be to this foreign key, not to the IDNumber.

    2) There's nothing wrong with doing a Count() on a text field. However, you're going about it wrong. If you do a count(), it will simply count, as told, and will yield the number of records in the table. Simply because the field PassFail exists in every record, whether the value is pass, fail, null, or any other value. If you want to count the number of passes and failures, you have two simple options:

    a - simply display the PassFail field in the query, and open the query in VBA and go through the recordset record-by-record and do an analysis of each record's PassFail, or

    b - In the query, create two separate fields called, perhaps, eqPass and eqFail. For each one, create a sub-query to include the count of the passes or failures. I don't recommend this option except for experienced Access SQL programmers. Option a is easier.

    A possible third option is to have separate Boolean fields in the table for Pass and Fail. That might be the easiest option. Then your count will be
    Code:
    Count(Pass) As CountOfPass HAVING Pass = True
    and
    Code:
    Count(Fail) As CountOfFail HAVING Fail= True
    Hence, your final query might look like:
    Code:
    SELECT Tbl_MainRecs.SampLocation, Tbl_SampleResults.SampleLocation, Count(Tbl_SampleResults.Pass) AS CountOfPass,
    Count(Tbl_SampleResults.Fail) AS CountOfFail FROM Tbl_MainRecs INNER JOIN Tbl_SampleResults ON Tbl_MainRecs.RecordID = Tbl_SampleResults.equipID
    GROUP BY Tbl_MainRecs.SampLocation, Tbl_SampleResults.SampleLocation
    HAVING Pass = True Or Fail = True;
    Sam
    Last edited by Sam Landy; 07-19-12 at 23:54. Reason: Fixing typos, etc.

  10. #10
    Join Date
    Jul 2012
    Posts
    14
    Ok thanks Sam...i'll go back and try to change the relationships and see if it makes any difference. I don't pretend to understand everything you're saying but from what i gather, putting the word "Pass" in the criteria section of the query field and then using the 'Count' function in that field won't count the number of passes. I must admit i'm quite surprised that counting the instances of a certain entry in a field is not a simple thing to do (for a beginner)

    Thanks again
    Maggie

  11. #11
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    To answer your question, you would have to look at your query in SQL view. It doesn't look the same as it looks in the QBE (query by example) grid in the query designer.

    However, I goofed last night when I gave the "final" SQL for the query. I tried the various methods I advised, and it seems that the way to get it to work is using the sub-query, even if you have one PassFail field. The correct SQL, including the sub-queries, is:
    Code:
    SELECT Tbl_MainRecs.SampLocation, Tbl_SampleResults.SampleLocation, (SELECT Count(Tbl_SampleResults.PassFail) FROM Tbl_SampleResults HAVING Tbl_SampleResults.PassFail = "PASS") AS CountOfPass,
    (SELECT Count(Tbl_SampleResults.PassFail) FROM Tbl_SampleResults HAVING Tbl_SampleResults.PassFail = "FAIL") AS CountOfFail
    FROM Tbl_MainRecs INNER JOIN Tbl_SampleResults ON Tbl_MainRecs.RecordID = Tbl_SampleResults.equipID
    GROUP BY Tbl_MainRecs.SampLocation, Tbl_SampleResults.SampleLocation;
    Be aware of the following: if your query results in more than one record, all the records will have the same value for CountOfPass and CountOfFail. Don't be concerned about that. It's a total count of passes and failures.

    I apologize for the confusion.

    Sam

  12. #12
    Join Date
    Jul 2012
    Posts
    14
    Sorry for the delay in replying Sam i've been off work sick for a couple of days. I'll try to get the query working in the morning and post the results.

    Thanks

  13. #13
    Join Date
    Jul 2012
    Posts
    14
    That seems to be working Sam...thanks. Is there any way i can use the same query to count the pieces of equipment which have yet to be tested ?

  14. #14
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    That would be a different query. You would have the same two recordsets, but you would want a LEFT JOIN instead of an INNER JOIN between them, and the criteria (for the EquipID) would be "Is Null" (without the quotes). How about if you take a stab at the actual SQL string?

    Don't ignore the Help File. It is most helpful!

    Sam

    BTW, maybe you should do a manual count on the first issue to verify the SQL code before you put the issue to bed. It's hard to be 100% confident from my remote location.

  15. #15
    Join Date
    Jul 2012
    Posts
    14
    Hi Sam.....apologies again for the delay between posts.....i was sent off-site at the last minute...just got back yesterday. The "Is Null" query doesn't seem to be working. I'm getting nothing when i run it


    Code:
    SELECT Tbl_MainRecs.SampLocation, Count(Tbl_SampleResults.EquipID) AS CountOfSampLocation, Tbl_SampleResults.PassFail
    FROM Tbl_MainRecs LEFT JOIN Tbl_SampleResults ON Tbl_MainRecs.RecordID = Tbl_SampleResults.RecordID
    GROUP BY Tbl_MainRecs.SampLocation, Tbl_SampleResults.PassFail
    HAVING (((Count(Tbl_SampleResults.EquipID)) Is Null));
    I'm sure i made a mess of the code somewhere

Posting Permissions

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