select records VBA/SQL with sum

[EisaGoat]

I have two tables in an Access 2016 application: Customer, and CustomerLoggedHours. This is a one to many relationship.

I want to select records based on the sum of a field value in the detail table. I have tried several approaches, but none has worked. I get the error 3096 - cannot have an aggregate function in WHERE clause. Is there any way to allow me to sum the hours to calculate a total so I can determine if the hours purchased have been consumed by the customer?
Here is my SQL:

INSERT INTO tblCustomerArchive
(CustID, CustomerName, DateOfPurchase, ExpireDate, PurchaseAmount, HoursPurchased, DateArchived)
(SELECT CustID, CustomerName, DateOfPurchase, ExpireDate, PurchaseAmount, HoursPurchased, Date FROM Customer INNER JOIN CustomerLoggedHours ON Customer.ID = CustomerLoggedHours.CustID
WHERE Customer.ExpireDate < Date
OR (SUM(CustomerLoggedHours.HoursLogged - Customer.HoursPurchased <= 0))
GROUP BY CustomerLoggedHours.CustID

 

[plog]

You've got 2 syntax issues and 2 logic issues. Syntatically, if you want to use Aggregate criteria in your query it must be done in a HAVING clause (http://www.w3schools.com/sql/sql_having.asp). That means

SUM(CustomerLoggedHours.HoursLogged - Customer.HoursPurchased <= 0))

wouldn't be in the WHERE clause, but in a HAVING clause after the GROUP BY clause. Further, the above expressions is incorrect because you've put a comparison operator inside the SUM function. That <=0 part is throwing the whole thing off.


Logically, I don't know how to fix that because its ambiguous. WHERE works on the individual record level, HAVING works on the aggregate level--it does all that math to either SUM or COUNT and then applies criteria. With your SUM function its like you tried to split the baby and do it both at once which makes no sense.

You can either exclude records where HoursLogged are less than HoursPurchased (which would be in the WHERE) and/or you can exclude records that when you add them all up have a total HoursLogged less than HoursPurchased (which would go in the HAVING). You can't do both at the same time in one expression.

Perhaps it would be best if you provided some sample data from your tables and then show what you expect as results from your query.

Lastly, why are you using an INSERT INTO? Action queries are usually a sign of a misunderstanding of how databases work or an improper structure. Why must you save the results off this query?

 

[EisaGoat]

Thanks for the feedback. Let's try a different approach since I've been all over the map trying to learn how to do this.
So, I have two tables, Customer, and CustomerLoggedHours. The business scenario is a customer buys block time (on a simulator). The MS Access system allows us to set up a customer and the number of hours they have. Then we log the hours each time the customer works on the simulator. When then run out of hours, we want the record to archive. So, we need an automated process to select from the Customer and CustomerLoggedHours where either their expire date has passed, or the summed of the hours they have used is equal or greater than the number of hours they bought.
So - I want a process to read each customer, determine if the total hours is equal or greater than the number of hours they bought, and archive the record. The part I can't figure out is how to get the total hours for the customer to know if they have met or exceeded the number of hours they bought.

Thanks!!

 

[Galaxiom]

Moving records from one table to another to designate their status is incorrect design. Data is stored by values in records. Don't use the database structure to record information.

 

[EisaGoat]

Yes, I agree about moving records to another table, and I will change that design. However, my main problem is that I have not been able to flag an account as archived. There are two conditions that can render an account inactive. Either their ExpireDate has passed, or they have consumed all the simulator hours they purchased. The ExpireDate part is easy. But, how do I identify if an account has a zero or negative balance. To get that number, I need the sum of all the individual hours that the customer has used up?
So I want something that looks like:
WHERE ExpireDate < today OR Sum of hours used <= 0
Thanks!!!

 

[Galaxiom]

Your data structure looks wrong.

I would expect:

Customers: CustomerID, CustomerName, etc
Purchases: PurchaseID, CustomerID, PurchaseDate, HoursPurchased, Expiry, PurchaseAmount
Logons: LogonID, CustomerID, LogonDateTime, LogoffDateTime

 

[EisaGoat]

Ignore the SQL earlier in this post - I've done a gazillion SQL's to try to make this work, and the one I listed above is just one of many failures. The secret code I need is how to select records that have either used all of their purchased hours (ie. (SUM(HoursLogged) - HoursPurchased) <=0 OR ExpireDate < Date().

 

[arnelgp]

INSERT INTO tblCustomerArchive 
(CustID, CustomerName, DateOfPurchase, ExpireDate, PurchaseAmount, HoursPurchased, DateArchived) 
SELECT T1.ID, T1.CustomerName, T1.DateOfPurchase, T1.ExpireDate, T1.PurchaseAmount, T1.HoursPurchased, Date FROM Customer AS T1 
Where ((T1.ExpiredDate) < Date) OR ((T1.HoursPurchased) <= 
(SELECT SUM(T2.HoursLogged) FROM CustomerLoggedHours AS T2 WHERE ((T2.CustID) = (T1.ID))));

you can test this portion if it correctly retrieve the records, by creating a test query:

SELECT T1.ID, T1.CustomerName, T1.DateOfPurchase, T1.ExpireDate, T1.PurchaseAmount, T1.HoursPurchased, Date , (SELECT SUM(T2.HoursLogged) FROM CustomerLoggedHours AS T2 WHERE ((T2.CustID) = (T1.ID))) As HrsUsed FROM Customer AS T1

 

[EisaGoat]

I'm doing my happy dance! It worked. Thanks for all you help!!!!!