Search results

Can anyone offer any input on this please? I think there's a few ways to achieve what I want, but I'd like the most efficient / best practice really... Thanks in advance.

When I think about it more, is it best to make a query to filter dates first, then use DAVG to look up averages in the filtered data?

Hi everyone, I'm using DAVG to look up average days in a query. All works fine but I'd like additional functionality. Here's what I have so far: =DAvg("[kitted2completed]","KittedComp") What I would like is the above to return an average based on dates from and to in column "Comp". For...

Bingo - got 12,000 with the LEFT. Thanks again! (I think I need to study those links you gave, seems like I might be able to use this more often)

Paul, sorry, one more thing... Bizarrely the "wip" table has 57,000 records, the "NewMasterData" has 12,000 yet the end result of the query has only 3,900. Is that saying it's only found 3,900 matches? Is there a way to drag in all the data from "NewMasterData" and leave blank any it can't...

Excellent, thanks Paul. I hadn't even thought about doing that - works perfectly.

Hi everyone, I'm just doing a fairly simple DLookUp and I'm having trouble. I'm making a query that returns all the fields in a table called "NewMasterData" with an additional field called "COMPLETED". The "COMPLETED" field needs to be populated by data from a table called "WIP". What I have...

Got it! Thanks very much for your help :D

Thanks, that was a typo - fixed it. Any idea on the text box linking?

Hi guys & girls, Doing a fairly simple thing here which I'm sure I've almost cracked, however no matter what I've tried I've not had any luck. Searching didn't shed much light either. I have the following SQL: WHERE DATEDIFF('d'.startdate,enddate) > 0 The idea is to have startdate and...

Yeah both sub queries ran fine, just the last one gave me that error.

No I didn't change anything, I also made sure I saved them with the right name. I tried twice just in case I'd missed something... It's identical to what you posted.

Thanks Plog, when I run the final query I get: "The specified field 'LiveData.Batch' could refer to more than one table in the FROM clause of your SQL statement" I've had a look at it and can't figure out what the problem is - looks to me like LiveData is specified wherever it needs to be...

Yes you're correct and I've checked the data to make sure: Zones would never change as a reference number is always made in the same zone. Status relates to reference number and as such would never change, as the reference number is either work in progress or isn't, this would be the same on...

Right, well then this example is better: Cls1 Ref Part Number Item Desc Qty Mfg End Date Prod Locn Status WO Balance 247 23110004 985209-14 123456 10.000 01/02/2012 zone1 503.000 98772530S 247 23110004 985209-14 123456 2.000 01/02/2012 zone1 503.000 98772530S 247 23110004 985209-14 123456...

Right, on importing the data this is what I get, including 'duplicates': Cls1 Ref Part Number Item Desc Qty Mfg End Date Prod Locn Status WO Balance 247 23110004 985209-14 123456 10.000 01/02/2012 zone1 503.000 98772530S 247 23110004 985209-14 123456 10.000 01/02/2012 zone1 503.000...

Actually, Qty can be duplicated also - but not in every case :-/ For instance, on one part number I have four records with the same Mfg End Date, of which three of those records have the same value in Qty. Spanner in the works??

I understand, thanks for the explanation. With regards to what I class as a duplicate, you're right when you say "you have a different definition of 'duplicate' where one or more of those fields can be different among records". In this instance values in all fields other than Qty will be the...

So, after modifying the queries suggested by Plog I came up with the following. This is the sub query: SELECT LiveData.Ref, Min(LiveData.[Mfg End Date]) AS FirstDue FROM LiveData GROUP BY LiveData.Ref; Which returns 13,344 records, none of which are duplicates. Desired result. This...

to add to my post above, upon checking others it seems like the date is the problem because I've witnessed this: In the master data table a product had 6 records. Your query resulted in 4 records, all with the same due date (Which of the 6 records, was the earliest date). Pretty much what I...