Query: owners with balance greater than

[Chadsterman]

I am rather new to access, but "I think" I am trying to create an expression that will return owners with a total owner net pay of greater than 100.00. Any help would be greatly appreciated. I have "owners" that have multiple "owner net pay" amounts. Example - Owner - Joe has 15 owner net pay records that total $95.00. Owner - Mary has 12 owner net pay records that total $120.00. I need the expression that will pull in Mary, but not Joe. Additionally, it is not built yet, but if Mary gets paid the $120.00 and a check number field has been populated, then I need the expression to exclude those particular owner net pay amounts when the query is ran the next time.

[CJ_London]

the description of your requirement is pretty vague and what is missing in particular is your table structure and relationships.

Best I can suggest is use an aggregate query (group by) to sum the payments and exclude those where the sum of payments>100

[ranman256]

make query1: qsSumQry , that sums all the Owners,
Then make Q2:
select ID, Owner, Amt from qsSumQry where Amt > 100

[Micron]

ranman's is the simpler approach for a newbie but requires that you create 2 queries when one ought to be enough. IMO that would be to create a Select query that gives you the fields and records you want, then use the Sigma symbol on the ribbon to convert it to a Totals query. It's likely that you would have to research Access Totals Queries first because I think it would be just luck if you were successful right off the bat. If you don't know how to create a Select query, then you should be looking for tutorials or get a book on the basics because that's Access 101.

[isladogs]

Request to mods:
Can this thread please be moved from the code repository (intended for code samples) to the Queries section of the forum

[Chadsterman]

Thanks for the school of hard knocks. I am on the right track now.

Isladogs - Yes, it can be moved. My apology. I was toggling between both forums and inadvertently posted here. So sorry.

[pbaldy]

Request to mods:
Can this thread please be moved from the code repository (intended for code samples) to the Queries section of the forum

Done. thanks for the heads-up.

[isladogs]

Thanks Paul and no problem as far as the OP is concerned.