Counting Records

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I've read several posts about this subject but everybody is trying to do
something much more complicated then what I need. What I want would appear
to be quite simple but I can't seem to figure it out. Here is what I've got

I have a table for movies. I want to run a query that will list records in
the table based on a field for media type (vhs, vcd, dvd, etc.) and then
sort by the movie name. I want a column that displays the running count of
the records.

1 Movie 1
2 Movie 2
3 Movie 3

I've tried to use Count and DCount but all I get in the column is the total
number of records instead.

242 Movie 1
242 Movie 2
242 Movie 3

If anybody has an idea, please let me know.

Rick

(e-mail address removed)
 
I don't understand. I'm trying to work inside a query and don't believe I
can add a text box in a query. I can add a text box inside the form that my
query is based on but there is no 'Running Sum' property available.
 
I still had my head in the reports news group. Do you have an order to movie
list? Does the query need to be editable? One method
SELECT MovieName,
(SELECT Count(*)
FROM tblMovies M
WHERE M.MovieName <=tblMovies.MovieName) As Sequence
FROM tblMovies;
 
No, the query does not need to be editable. I have several queries that are
invoked depending on what information I want to list such as DVD list or a
VHS list. I also have queries that take user input in order to break the
list done into categories such as science fiction, comedy, etc.

Here is the SQL view of one query that lists all media type and all
categories. Can you explain to me how to use the method you described
inside this query?

SELECT Movie.Name, Movie.Media, Movie.Type, Movie.Catagory, Movie.Method,
Movie.Actor, Movie.Actor2
FROM Movie
ORDER BY Movie.Name;

Thanks,

Rick
 
Try:
SELECT Movie.Name, Movie.Media, Movie.Type, Movie.Catagory, Movie.Method,
Movie.Actor, Movie.Actor2,
(SELECT Count(*) from Movie M
WHERE M.Type = Movie.Type AND M.Name<=Movie.Name) as Sequence
FROM Movie
ORDER BY Movie.Name;
 
Thanks Duane, that did it. The way you had it set up, it was numbering by
Movie Type which is not what I wanted. I just wanted to number each movie
in sequence. I just removed the M.Type section and changed Sequence to Num
and put at the beginning instead of the end. It works just like it should.
I've done a bit of access work but not much with queries. You are indeed a
master.

Rick
 
Back
Top