Topic awaiting preservation: Access question (Page 1 of 1)

I've set up a database to keep track of the different cities, states, and pharmacies that I have traveled to in the past three years with my job. I've got the database all set up and now I'm creating some different reports and queries for different things that I'm interested in.

What would be the easiest way to get a count of all the states that I have been to? I have a field in the DB set up with all states listed so I can choose them from a list. I just can't figure out a way to display the number of states I've been to in a query or report. It might also be nice to see how many times I've been to each state if that's possible.

Any clues on how I could do this?

:::11oh1:::

At first I think of the DISTINCT directive to list each value of a field once, but it'd be better to see the structure of your tables to make sure.

Here's a screenshot. It's a pretty small DB right now:



:::11oh1:::

Ok, so to get the count of states, just do :

SELECT COUNT( DISTINCT State ) FROM `Trips`

Now to get the number of times you've been in state, do :

SELECT State, count( State ) FROM `Trips` GROUP BY State

Sorry I've never used Access so the queries might need some adjustements.
Hope that helps,

Thanks for the SQL but my question has more to do with where should I use that SQL? In a report? Is there a way to add that to a query?

This is my first real attempt at using Access so I'm fairly new to it.

:::11oh1:::

doh! So I fear I can't help you with that.

You can create a custom field on a report and use that SQL as it's data source


Justice 4 Pat Richard

So when I create a textbox on the report, would the SQL go into the Control Source for that text box?

This is an entirely new piece of software for me so pardon my ignorance.

:::11oh1:::

I believe so, yes... you can even have the query feed it's control data (say, the city or state you want a count of) from another field, and if that field is blank it will prompt you for it when you generate the report


Justice 4 Pat Richard