Count function grouping.

[Tabix09]

Hi all, I was hoping I would be able to come here to get some help on a database I'm currently working on.

I'm creating a rental system for a DvD shop and I need to figure out a few breakpoints to do with how long each customer can rent their share of DvDs.

Depending on how many DvDs they rent, they get to have them for longer.

< 5 DvDs = 2 nights.
5-10 DvDs = 1 week
> 10 DvDs = 2 weeks

I wanted to use a count function to count how many DvDID's each RentalID has, use these numbers with an IIF function to find out what threshold it rests in and then use them with a date function and the RentalDate field to figure out when they need to be returned.

I can't get the Count function to work in my query.

I'm using a query with only RentalID (text), DvDID (text) and I want to use a count function but every time I try I keep getting 'You tried to excecute a query that does not include the specified expression'RentID' as part of an aggregate function'. This error also happens when I try something simple like Count([RentID]).

If someone could be of assistance it would be greatly appreciated

(even if you could give some tips for the next parts )



Thanks!

[Bob Fitz]

Can you post the SQL statement of the query please

[Tabix09]

I have figured it out, but now it is counting doubles and being wierd (there is 9 DvDIDs for 1 of the RentalIDs and it is counting a 2 and a 7)

SQL:


SELECT Rental.[RentID], Count(Rental.[DvDID]) AS CountOfDvDID
FROM Rental
GROUP BY Rental.[RentID];

That's one,

SELECT [Rental Query].RentID, [Rental Query].CountOfDvDID, IIf([CountOfDvdID]>10,14,IIf([CountOfDvdID]>4 And [CountOfDvdID]<11,7,IIf([CountOfDvdID]<5,2))) AS Days
FROM [Rental Query];

[Rhino373]

I'm betting your RentalID is somehow different than the first where it is grouping them seperately. This is especially possible since the datatype is text. Could be something as little as an extra space. I'd consider turning it into an AutoNumber.