query to return set number of records

[james_halliwell]

Hi all
not sure if this is possible but I have thought that in the past and this forum has always helped me out, the problem I face is I am building a cycle counting database and I have a table with a summary of what I need to find


Table – [TblMasterList] it has (Product) and (Total) as the fields i.e


product ---Total
123456 ------3
123457 ------2
123458 ------5
Each product has a unique lot number so there can be loads of the same product but only one lot these are entered onto the below table


Table – [TblScanMaster] it has (Product) and (Lot) and (Total) as the fields i.e


Product ------Lot -------Total
123456 -------abc-------- 1(Will always be 1)
123256 -------bbc --------1
123457------- abb-------- 1

As I can scan loads of one product but with different lots all I need to account for is the total from the [tblMasterList] so I just want a query to return the required number of lots for instance
123456 is 3 on the tblmasterlist there can be loads on the tblescanmaster but I just want the first 3 unique lots to be returned and ignore the rest

Hope this makes sence

 

[John Big Booty]

The article here should help you do what you are after.

 

[james_halliwell]

The article here should help you do what you are after.

Hi John Big Booty

Thanks for your reply, would i be able to reference it to tblmaster list, i.e each product has a toal so only return the total amount or records from the tblscanMaster

cheers

 

[John Big Booty]

Perhaps I've misunderstood what it is you are trying to achieve

Are you after the Tops three results? Or do you simply wish to group by each product and count them? If it's the latter perhaps this is what you are after?