MS Access Query Question

  • Thread starter Thread starter RC-
  • Start date Start date
R

RC-

I need to write a query that will take the count of the records found and
divide that by a number in one of the fields. Here is a copy of the current
query that I have now. I only have three records so far:

SELECT Movies.Title, Movies.Year, Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented], Movies.[Rent Amount],
Movies.Rental_Source
FROM Movies
GROUP BY Movies.Title, Movies.Year, Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented], Movies.[Rent Amount],
Movies.Rental_Source
HAVING (((Movies.Rent_Own)="rent") AND ((Movies.[Date Rented]) Between
#4/1/2005# And #4/30/2005#) AND ((Movies.Rental_Source)="blockbuster")) OR
(((Movies.Rental_Source)="blockbuster online"));

I need to take the count of the records (3 in this case) and divide that by
Movies.[Rent Amount]

TIA
RC-
 
RC- said:
I need to write a query that will take the count of the records found
and divide that by a number in one of the fields. Here is a copy of
the current query that I have now. I only have three records so far:

SELECT Movies.Title, Movies.Year, Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented], Movies.[Rent Amount],
Movies.Rental_Source
FROM Movies
GROUP BY Movies.Title, Movies.Year, Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented], Movies.[Rent Amount],
Movies.Rental_Source
HAVING (((Movies.Rent_Own)="rent") AND ((Movies.[Date Rented]) Between
#4/1/2005# And #4/30/2005#) AND
((Movies.Rental_Source)="blockbuster")) OR
(((Movies.Rental_Source)="blockbuster online"));

I need to take the count of the records (3 in this case) and divide
that by Movies.[Rent Amount]

TIA
RC-
Click to expand...

Do you really want to "divide by" the rent amount, or do you want to
"divide into" the rent amount? The latter makes more sense to me. If
that's what you had in mind, how about this:

SELECT
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source
([Rent Amount] / Count(*)) As AmtPerMovie
FROM Movies
GROUP BY
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source
WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster online")
);

*Note*
1. I've replaced your HAVING clause with a WHERE clause, because it
appears that a WHERE clause would do just as well, and it will restrict
the records before the grouping takes place.

2. I changed the actual criteria a bit, because I think maybe you made a
mistake. Your original criteria would have included any movie with
Rental_Source = "blockbuster online", regardless of [Date Rented] or
even whether Rent_Own = "rent" or not. I could be wrong about your
intentions, though, so feel free to put the criteria back the way you
had them if that's the case.
 
Dirk,
Thank you for the reply. I've tried the query listed below and I am
getting an error stating:

Syntax error (missing operator) in query expression 'Movies.Rental_Source
WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster o".

The end goal of dividing the number is to get the average cost of the
movies. In this case, it would be $14.99 / 3 or $4.99 for each movie.

Thanks again for you help. As you can probably tell, I'm rather new to SQL
queries ;-)




Dirk Goldgar said:
RC- said:
I need to write a query that will take the count of the records found
and divide that by a number in one of the fields. Here is a copy of
the current query that I have now. I only have three records so far:

SELECT Movies.Title, Movies.Year, Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented], Movies.[Rent Amount],
Movies.Rental_Source
FROM Movies
GROUP BY Movies.Title, Movies.Year, Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented], Movies.[Rent Amount],
Movies.Rental_Source
HAVING (((Movies.Rent_Own)="rent") AND ((Movies.[Date Rented]) Between
#4/1/2005# And #4/30/2005#) AND
((Movies.Rental_Source)="blockbuster")) OR
(((Movies.Rental_Source)="blockbuster online"));

I need to take the count of the records (3 in this case) and divide
that by Movies.[Rent Amount]

TIA
RC-
Click to expand...

Do you really want to "divide by" the rent amount, or do you want to
"divide into" the rent amount? The latter makes more sense to me. If
that's what you had in mind, how about this:

SELECT
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source
([Rent Amount] / Count(*)) As AmtPerMovie
FROM Movies
GROUP BY
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source
WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster online")
);

*Note*
1. I've replaced your HAVING clause with a WHERE clause, because it
appears that a WHERE clause would do just as well, and it will restrict
the records before the grouping takes place.

2. I changed the actual criteria a bit, because I think maybe you made a
mistake. Your original criteria would have included any movie with
Rental_Source = "blockbuster online", regardless of [Date Rented] or
even whether Rent_Own = "rent" or not. I could be wrong about your
intentions, though, so feel free to put the criteria back the way you
had them if that's the case.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
RC- said:
Dirk,
Thank you for the reply. I've tried the query listed below and I
am getting an error stating:

Syntax error (missing operator) in query expression
'Movies.Rental_Source WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster o".

The end goal of dividing the number is to get the average cost of the
movies. In this case, it would be $14.99 / 3 or $4.99 for each movie.

Thanks again for you help. As you can probably tell, I'm rather new
to SQL queries ;-)
Click to expand...

Hmm. Now that I think about it, I have the WHERE clause in the wrong
place, and I left off a parenthesis. Sorry about that. Try this:

SELECT
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source
([Rent Amount] / Count(*)) As AmtPerMovie
FROM Movies
WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster online"))
)
GROUP BY
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source;
 
Dirk,
No problem at all. The new query doesn't throw any errors but the
AmtPerMovie field is still showing 14.99 rather than the calculated value.
Any other ideas?

Thanks again for sticking with me ' -).

RC-


Dirk Goldgar said:
RC- said:
Dirk,
Thank you for the reply. I've tried the query listed below and I
am getting an error stating:

Syntax error (missing operator) in query expression
'Movies.Rental_Source WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster o".

The end goal of dividing the number is to get the average cost of the
movies. In this case, it would be $14.99 / 3 or $4.99 for each movie.

Thanks again for you help. As you can probably tell, I'm rather new
to SQL queries ;-)
Click to expand...

Hmm. Now that I think about it, I have the WHERE clause in the wrong
place, and I left off a parenthesis. Sorry about that. Try this:

SELECT
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source
([Rent Amount] / Count(*)) As AmtPerMovie
FROM Movies
WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster online"))
)
GROUP BY
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source;


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Maybe I'm not understanding what you want. Are you trying to divide by
the total count of records in the table that meet your criteria, not
just by the count of records in each group defined by your Group By
fields? You could get that just with

SELECT Avg([Rent Amount]) As AmtPerMovie
FROM Movies
WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster online"))
);

Does that give you what you're looking for?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


RC- said:
Dirk,
No problem at all. The new query doesn't throw any errors but the
AmtPerMovie field is still showing 14.99 rather than the calculated
value. Any other ideas?


Dirk Goldgar said:
RC- said:
Dirk,
Thank you for the reply. I've tried the query listed below and
I am getting an error stating:

Syntax error (missing operator) in query expression
'Movies.Rental_Source WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster o".

The end goal of dividing the number is to get the average cost of
the movies. In this case, it would be $14.99 / 3 or $4.99 for each
movie.

Thanks again for you help. As you can probably tell, I'm rather new
to SQL queries ;-)
Click to expand...

Hmm. Now that I think about it, I have the WHERE clause in the wrong
place, and I left off a parenthesis. Sorry about that. Try this:

SELECT
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source
([Rent Amount] / Count(*)) As AmtPerMovie
FROM Movies
WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster online"))
)
GROUP BY
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source;
Click to expand...
Click to expand...
 
The end result would be something like this

Title Year Genre Rating Rent_Own Date
Rented Rent Amount Rental_Source AmountPerMovie
Two Brothers 2004 Adventure PG Rent 04/04/2005
$14.99 Blockbuster Online $4.996666666

I need to get the total number of movies rented in a certain time period, in
this case 04/01/2005 to 04/30/2005. In this example, there are three that
fall unde that criteria. I then need to take the Rent Amount (which is the
monthly rent total) and divide that by the number of records found, in this
case 3. So the end result will be similar to the above.

As it stands now, I keep getting the same amount in the AmtPerMovie field
(14.99) this value needs to be 4.9966666 (14.99 / 3)


Dirk Goldgar said:
Maybe I'm not understanding what you want. Are you trying to divide by
the total count of records in the table that meet your criteria, not
just by the count of records in each group defined by your Group By
fields? You could get that just with

SELECT Avg([Rent Amount]) As AmtPerMovie
FROM Movies
WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster online"))
);

Does that give you what you're looking for?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


RC- said:
Dirk,
No problem at all. The new query doesn't throw any errors but the
AmtPerMovie field is still showing 14.99 rather than the calculated
value. Any other ideas?


Dirk Goldgar said:
Dirk,
Thank you for the reply. I've tried the query listed below and
I am getting an error stating:

Syntax error (missing operator) in query expression
'Movies.Rental_Source WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster o".

The end goal of dividing the number is to get the average cost of
the movies. In this case, it would be $14.99 / 3 or $4.99 for each
movie.

Thanks again for you help. As you can probably tell, I'm rather new
to SQL queries ;-)

Hmm. Now that I think about it, I have the WHERE clause in the wrong
place, and I left off a parenthesis. Sorry about that. Try this:

SELECT
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source
([Rent Amount] / Count(*)) As AmtPerMovie
FROM Movies
WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster online"))
)
GROUP BY
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source;
Click to expand...
Click to expand...
Click to expand...
 
Dirk,
Nevermind. I found the answer (figured it out). Here is a copy of the
query that does what I'm looking for:

SELECT
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own,
Movies.[Date Rented], Movies.[Rent Amount],
Movies.Rental_Source,
(SELECT Count([Rent Amount])
FROM Movies
WHERE [Rent Amount]=14.99) AS Records, [Rent Amount]/Records AS
AmtPerMovie
FROM Movies
WHERE (((Movies.Rent_Own)="rent")
AND ((Movies.[Date Rented]) Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source)="blockbuster" Or
(Movies.Rental_Source)="blockbuster online"))
GROUP BY
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source;

Thanks for all your help though!

RC-



Dirk Goldgar said:
Maybe I'm not understanding what you want. Are you trying to divide by
the total count of records in the table that meet your criteria, not
just by the count of records in each group defined by your Group By
fields? You could get that just with

SELECT Avg([Rent Amount]) As AmtPerMovie
FROM Movies
WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster online"))
);

Does that give you what you're looking for?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


RC- said:
Dirk,
No problem at all. The new query doesn't throw any errors but the
AmtPerMovie field is still showing 14.99 rather than the calculated
value. Any other ideas?


Dirk Goldgar said:
Dirk,
Thank you for the reply. I've tried the query listed below and
I am getting an error stating:

Syntax error (missing operator) in query expression
'Movies.Rental_Source WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster o".

The end goal of dividing the number is to get the average cost of
the movies. In this case, it would be $14.99 / 3 or $4.99 for each
movie.

Thanks again for you help. As you can probably tell, I'm rather new
to SQL queries ;-)

Hmm. Now that I think about it, I have the WHERE clause in the wrong
place, and I left off a parenthesis. Sorry about that. Try this:

SELECT
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source
([Rent Amount] / Count(*)) As AmtPerMovie
FROM Movies
WHERE (
(Movies.Rent_Own="rent")
AND
(Movies.[Date Rented] Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source="blockbuster") OR
(Movies.Rental_Source="blockbuster online"))
)
GROUP BY
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source;
Click to expand...
Click to expand...
Click to expand...
 
RC- said:
Dirk,
Nevermind. I found the answer (figured it out). Here is a copy
of the query that does what I'm looking for:

SELECT
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own,
Movies.[Date Rented], Movies.[Rent Amount],
Movies.Rental_Source,
(SELECT Count([Rent Amount])
FROM Movies
WHERE [Rent Amount]=14.99) AS Records, [Rent Amount]/Records AS
AmtPerMovie
FROM Movies
WHERE (((Movies.Rent_Own)="rent")
AND ((Movies.[Date Rented]) Between #4/1/2005# And #4/30/2005#)
AND ((Movies.Rental_Source)="blockbuster" Or
(Movies.Rental_Source)="blockbuster online"))
GROUP BY
Movies.Title, Movies.Year,
Movies.Genre, Movies.Rating,
Movies.Rent_Own, Movies.[Date Rented],
Movies.[Rent Amount], Movies.Rental_Source;

Thanks for all your help though!
Click to expand...

I have to say that doesn't make any sense to me. You're going to use a
count of all the movies with the same RentAmount as this movie, to come
up with an amount per movie? I'm perplexed at the logic of that, and
strongly doubt that it will continue to give you what you want, even if
it seems to now.

If you are sure you've got what you want, fine; but if you want to
pursue this further, I'd be happy to work with you. If that's what you
want, then to prevent any further confusion, please tell me what your
table Movies actually represents -- is each record a single movie, a
copy of a movie, a rental of a movie, or what? Post the complete list
of fields in the table, and explain in plain language what you want to
get out of the query.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Question regarding Access 3
Access Access 2003 Report 4
preguntas 1
help with Sum in between dates 9
the complement of two queries 5
Running Total in Access 2002 2
How do I figure out amout due from transaction file 3
converting or formatting months to numbers - Access 2000 Non Exper 2

Back
Top