Obtain desired information using queries

Create a query to select only animals with mastitis

  • Open the query you created (using wizard or using Design view) in Design view.
  • Specify Yes in the criteria row for the MASTITIS field.
  • Click the red exclamation mark in the toolbar to run the query (or Query–>Run) or alternatively, open the query in datasheet view to see the records for all mastitic cows.

Create a query to select animals with both mastitis and metritis

Start as above but specify Yes in both the MASTITIS and the METRITIS fields and run the query.

Create a query to select animals with mastitis but not metritis

Proceed as above but specify Yes in the MASTITIS field and No in the METRITIS field.

Create a query to select animals with mastitis or metritis

Start as described above but specify Yes in the MASTITIS field in the Criteria row and Yes in the METRITIS field in the Or row.

Count animals with and without mastitis

  • Create a new query with only the COWID and the MASTITIS fields.
  • In Design view, click the summation tool in the toolbar to insert a Total row in the query (or View–>Total).
  • Change the Group By in the Total row for COWID to count but leave the Group By for the MASTITIS field as such. Run the query to get the results (Try the same approach with metritis)

Count animals with and without metritis and mastitis

  • Create a query with COWID and both METRITIS and MASTITIS.
  • Specify Group By for both METRITIS and MASTITIS and Count for COWID in the Total row and run the query.

Cross-tabulate metritis and mastitis

  • Go to Query–>New–>Crosstab query wizard.
  • Select the ANIMAL table (or the metritis and mastitis query created earlier) and click Next.
  • Select MASTITIS as a row heading and click Next.
  • Select METRITIS as a column heading and click Next.
  • In the next window select COWID from the fields and Count from the Functions (meaning that we want to count COWID) and click Next.
  • Name the query and Finish the design. You will see that cows with and without mastitis are cross tabulated against cows with and without metritis (-1 means yes and 0 means no).

ENDS