Calculating a quantity of a type

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Something a bit different. I'm putting together an artifact database. I
have a column labled "catalog number" and a colum labled "location found".
What I need to know is the total number of artifacts found at each location.
I know this is probably really simple but I would appreciate any help I could
get.

Thanks

PS whoever helps can get a thank you in the intro to my thesis.
 
mequennoz said:
Something a bit different. I'm putting together an artifact database. I
have a column labled "catalog number" and a colum labled "location found".
What I need to know is the total number of artifacts found at each location.
I know this is probably really simple but I would appreciate any help I could
get.
Click to expand...


I think all you need is to use a Totals type (Group By)
query:

SELECT location, Count(*) As NumAtLoc
FROM artifacts
GROUP BY location
 
ok...I'm afraid all of that went right over my head. sorry. I keep trying
to go through the query wizzard but I keep going in circles.
 
The query design grid is somewhat limiting and difficult to
communicate in a text message. The way you can use a posted
SQL statement is to Copy it from the message, switch your
query design window to SQL view, Paste the SQL statement and
make sure the table and field names are the ones you are
using.

If that's still sailing overhead, I'll try to spell out the
steps and hope it's not too garbled:

Create a new query and add the artifacts table.

Then use the View - Totals menu item to show the query's
Total row.

Drag the location field from the table to the field list and
make sure it has Group By in the totals row.

Finally, drag the location field from the table to the next
field in the next field list, but this time use the drop
down list in the Totals row to select Count.

Save the query and check to make sure it produces the data
you want.

Because the query grid can't deal with the highly optimized
Count(*) syntax, this way of creating the query will be a
little different than the SQL I posted earlier. This will
be a little slower, but, unless you have a huge number of
records, you probably won't notice the difference.
 
I can't thank you guys enough. I got exactly the data I wanted. You both
are going to get a thank you in the acknowledgements of my thesis.
 
Back
Top