Calculating a quantity of a type

[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.

 

[Marshall Barton]

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.

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

SELECT location, Count(*) As NumAtLoc
FROM artifacts
GROUP BY location

 

[Guest]

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.

 

[Marshall Barton]

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.

 

[Brendan Reynolds]

Here's how that query might look in design view. Note the highlighted totals
icon in the toolbar near the top of the screen ...

http://brenreyn.brinkster.net/totals.jpg

 

[Marshall Barton]

Here's how that query might look in design view. Note the highlighted totals
icon in the toolbar near the top of the screen ...

http://brenreyn.brinkster.net/totals.jpg

Thanks Brendan. A picture is much better than text.

One of these years, I might get around to thinking about
using a web site for this kind of thing.

 

[Guest]

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.