Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    62

    Unanswered: query to count records

    Hi
    I have two tables:
    Experiment Finished?
    1A No
    1B No

    each experiment contains 24 codes. This info in put in via a form.

    Table 2
    Experimental Code Other info
    1A1
    1A2
    1A24
    1B3
    1A9
    1B12

    Is there a way, using a query or anything, to get the database to count up when there are an experiment is finished, i.e. there are 24 codes within each experiment?

    really confused!
    thanks for any help in advance,
    Sue

  2. #2
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Re: query to count records

    Originally posted by Sizzle
    Is there a way, using a query or anything, to get the database to count up when there are an experiment is finished, i.e. there are 24 codes within each experiment?
    Hi there,
    Use a text editor to replace the table names and desired field names (only use 3 fields first...simpler when you enter design view in a minute) in the code example below. Then create a new query without adding any fields, click on SQL (upper left button) to enter SQL mode. Paste your edited code there, replacing the text that is there. Then try to see the datasheet, switch to design view and add more fields or remove one if you want.

    Code:
    SELECT tbl_clients.id_client, tbl_clients.last_name, Count(tbl_buy.ID_buy) AS CountOfID_buy
    FROM tbl_clients INNER JOIN tbl_buy ON tbl_clients.id_client = tbl_buy.client_number
    GROUP BY tbl_clients.id_client, tbl_clients.last_name
    HAVING (((Count(tbl_buy.ID_buy))=24));
    Hope this helps :-)
    Daniel

  3. #3
    Join Date
    Apr 2004
    Posts
    62
    Hi,
    thank you for helping me but I'm having trouble entering my table names.
    Where should table 1 and table 2 go?

    I don't understand the ID.buy bit?

    thanks again
    sue

  4. #4
    Join Date
    Mar 2004
    Posts
    118
    =DCount("experiment_id","Experiment_table","Experi ment_finished = 'YES'")

    in a textbox... or without the = sign in code.

    I havent tried it in SQL but you can probably put it in as an expression:

    In access query design:

    countFinished: DCount("experiment_id","Experiment_table","Experim ent_finished = 'YES'")

    OR

    DCount("experiment_id","Experiment_table","Experim ent_finished = 'YES'") AS countFinished

    In jetSQL (i think dcount is an access only function so dont use it with oracle hehe)


    EDIT:

    You can probably do it from both tables at thesame time if you write a union query and then count from the query:

    SELECT * FROM TABLE1
    UNION
    SELECT * FROM TABLE2;

    Save it whatever (union);

    Then instaid of table name call the query. (assuming both tables have identical fields (the fields you select have to be identical in both tables).
    Last edited by YevSnow; 04-20-04 at 11:23.

  5. #5
    Join Date
    Apr 2004
    Posts
    62
    I don't think this will work since this just adds up finished experiments. I won't know this yet. I want the database to work out when 24 codes have been entered for each experiment and when they are to tisk or put yes.
    thank you for helping though, maybe I can stick this information together!

  6. #6
    Join Date
    Mar 2004
    Posts
    118
    Ah I see.

    Well you probably need to write a function to do that.

    For each experiment you want to check if any of the experiment code values are null, yes?

    So the function needs to check every experiment and return a yes or a no. Tehn do the next record.

    You have 2 options here, you can do it the clever way and write 2 functions. 1 to create a recordset of codes for each experiment, and another to examine that recordset for null values, tick it and pass the value over to a field on a form or using an insert SQL.

    Another option is slightly easier but may not be very efficient. That would be to create a query that has every experiment duplicated for each code. And then have a function doing the last part.

    I dount you can do it with access wizards...

  7. #7
    Join Date
    Apr 2004
    Posts
    62

    Unhappy

    ooo way over my head there!
    I'm not sure I want to check for nulls do i?
    I want to see when there are 24 entries for a particular code, how does the null come into that?

  8. #8
    Join Date
    Mar 2004
    Posts
    118
    null is when you have nothing in the field,

    and a check would look something like this:

    Code:
    if is not null(field1) Then
     1
    else
     0
    end if
    Then all you have to do is add up all the "1"s

    And if its less than 24 that that experiment isnt finished...

    That you can do as an extra field in a query its not too hard.

    just type in in query design view for the query that pulls up the codes.

    countcodes: iif("codetable.codefield" is null, 0, 1)

    Then do a sum of that in a texbox or whatever

    =sum(countcodes)

    Just dont try it as another query field in thesame query, it creates a circular query and you cant have that.

    you could have a count query that displays the number of codes entered.

    query name for exaple CCount:

    select sum(codequery.countcodes) as countcodes
    from countcodes;

    that will give you a number (for 1 experiment) 0-24
    then its just a simple matter of saying if number = 24 experiment complete = YES...

    You just need to do it for all the experiments, and thats the hard part.

  9. #9
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by Sizzle
    Where should table 1 and table 2 go?
    I don't understand the ID.buy bit?
    OK, try this:

    Code:
    SELECT tbl_1.experiments, tbl_1.finished, Count(tbl_2.experimental_code) AS CountOfExperimental_Code
    FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.experiments = tbl_2.experiments
    GROUP BY tbl_1.experiments, tbl_1.finished
    HAVING (((Count(tbl_2.experimental_code))=24));
    Then change my tbl_1 and tbl_2 for whatever your table names are, table1 , table2, or other. Table2 is the name of the table that contains the 24 rows for each experiment.

    D.

  10. #10
    Join Date
    Apr 2004
    Posts
    62
    Thank you very much to you both. I'll give them a go and let you know how I got on!
    You'll be saving me so much time!!
    cheers
    Sue

Posting Permissions

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