Search results

Hi Sue, Hmmm... so we need a way to associate the Tare with the Field Crop for any particular truck. Can you explain in just words (forget about the tables and fields for now) how a particular Tare average is picked in real life to apply to a particular Field Crop for the truck? That might...

Not quite sure why you're getting *doubled* records, unless there's an issue with lacking aliases. From the looks of it, you should be getting a "running average" since every date which meets the criteria will be included in the results - meaning you should be seeing multiple dates per...

Hi Sako, Regarding qryTruckTareAvg, you're right - that's why I added in the Count column so it would show any instances out of the norm. If you need a strict requirement to average exactly 3 records, I would think you could add a condition in the HAVING clause to only show records with counts...

You're welcome :)

Use Date() function instead

Hi Sue, To begin with, let me say you've done an excellent job of full-disclosure! :) Secondly, regarding qryTruckTareAvg I finally realized after I thought about it that it would in fact give you the same results with either table reference in the subquery, so thanks for confirming that with...

Hi Sue, Apologies for not responding sooner. I've had a few read-through's of your latest issue, and I'm afraid I still can't make heads or tails of it. You're introducing a lot of different elements (tables, queries, fields, calculated fields, etc.,) and there's no real way for me to make...

Hi Sue, I suspect my main query references in the subqueries are getting disrupted by the Aggregate functions. Well there's always more than 1 way to skin a cat, so my other (and more efficient) approach would be to integrate ByteMyzer's solution into a self-join:SELECT T1.fkTruckID...

Hi Martin, That's a lot of work you put into it. I'm sure you've been learning a boat load. My hats off to you! I actually didn't have a chance to read through the document I linked you to or test out the demo, but I'm guessing you've modeled your solution on David's public variable...

Hi Martin, The problem may be stacked queries both using parameters, in which case you may want to refer to this post, or you can try using Eval() around your parameter value assignments... This is getting tricky, though, I have to admit. Maybe someone will jump in here to save the day :o...

Hi Martin, You're on the right track. For "a20091" your PARAMETERS declaration should then be:PARAMETERS HolidayYear Long, usersid Long; And for "previous2009":PARAMETERS EntitlementYear Long, usersid1 Long; Now in your VBA just an FYI - if you need to capture the value of a control you can...

Hi Martin, Well, I take that back about using "Integer" (that's wrong) - - you should definitely be able to use "Long" (that's legit) Just to be clear, you should have "HolidayYear" as the parameter in the "a20091" query and "EntitlementYear" as the parameter in the "previous2009" query. Next...

As I said, change the word "Long" to "Integer" If you have these lines saved in your queries, then you can comment them out in the code. You might still have issues after that since you have a form reference in your criteria, but see how it goes with the changes I'm suggesting ;)

As expected, there were bound to be some glitches and syntax errors. Try changing the PARAMETERS declaration to "INTEGER" data type (the fact that you had to comment them out is a tip-off) --- and also put a space after the semicolon: qdf(0).SQL = "PARAMETERS HolidayYear INTEGER; " & qdf(0).SQL...

Oy vay! Well, this will be old hat for most of the vets here (and they'll be able to streamline all this significantly, I'm sure), but I can use some practice - so for demonstration's sake I'll try to build you a parameter query in code. In fact, this is kind of taking the scenic route...

Hi Martin, I'm sorry, but I'm getting confused about what you're trying to do. So, is the 2nd query ("previous2009") only meant to compare the previous year with the current year? In other words, if the 1st query ("a20091") is for 2009, is "previous2009" supposed to look at 2008? OR: Is the...

@ ByteMyzer - I neglected to consider using the WHERE clause for the subquery, so my hats off to you for the simplification :cool: As you have it, my only question regarding that would be whether this gives Sue a 'running' Average, or only a single Avg per TruckID? I suppose the point is moot...

Hi Sue, I should've mentioned, the aggregate functions you want to use against the recordset will require "nested" subqueries, so your entire SQL would be something like this:SELECT DISTINCT main.TruckID, main.TareDate, (SELECT Count(*) As RecentRecords FROM (SELECT TOP 3 sub1.TareWeight...

Hi Martin, Have you considered using a parameter query? I only mention that since it appears you're already using form control references in your WHERE clause, so I would think you could follow suit and account for the year in this manner as well... Or maybe your question was how to do this...

Glad you got things working! So with your C#.net background, I'm inclined to believe your moniker is very tongue-in-cheek ;) Cheers, John