Problem with Grouping Records

[Jody Woodentop]

I have a database that contains manufacturing dates for a range of different product types. I want to be able to group by product type (number) and then find the most recent five manufacturing dates for each product.
I am being driven slowly mad by this as I think it should be relatively simple to do, but have drawn a total blankl so far - please somebody respond soon.
Cheers!

 

[CJBIRKIN]

Hello
could you tell me the frequency of manufacturing dates i.e daily or weekly etc

 

[Jody Woodentop]

Hi There!
The dates are daily. To further confuse things, there might be more than one entry in the main table per day for a given part number depending on where in the manufacturing process they are. So I need to get an overall total per day per part number first.
Hope this makes sense!

 

[CJBIRKIN]

If i understand what your asking is two queries one that counts the number of components manufactured on any given day and second for each component the last five manufacturing dates


For the first one you need to create a group by query that will select your components and your dates and then use the component field again and select count, this will give you counts of each component per day

For the second:
This SQL will rank the top five of Dates in a table just replace the table and field names and copy into the SQL view of the query builder.


SELECT TOP 5 [component table].[speccode], [component table].[ManufDate]
FROM [component table]
WHERE ((([component table].[speccode)=

))
ORDER BY [component table].[ManufDate]DESC;

The expression [code] will ask you for a code when you run the query. I don't know how to do this to show all of the components and the top 5 dates for each at the same time. 

If i've misunderstood please shout.

Chris

 

[Jon K]

Assuming the fields [Part Number](numeric), [Manufacturing Date](Date/Time) and [Quantity](numeric) are in a table tblProduct. Try these three queries (copy each to the SQL View of a new query and run the third one.)

qry1_Prooduct:
SELECT [Part Number], [Manufacturing Date], sum(a.Quantity) AS Quantity
FROM tblProduct AS a
GROUP BY [Part Number], [Manufacturing Date]
ORDER BY [Part Number], [Manufacturing Date] DESC;

qry2_Product:
SELECT a.[Part Number] AS [Part Number], a.[Manufacturing Date] AS [Manufacturing Date], a.Quantity,
dcount("*", "qry1_Product", "str([Part Number]) & format([Manufacturing Date],'yyyymmdd') >= '" & str([Part Number]) & format([Manufacturing Date],'yyyymmdd') & " ' and [Part Number] = " & [Part Number] ) AS Ranking
FROM qry1_Product AS a
ORDER BY [Part Number], [Manufacturing Date] DESC;

qry3_Product:
SELECT [Part Number], [Manufacturing Date], Quantity
FROM qry2_Product
WHERE Ranking <=5;


The first query groups the quantities of products manufactured in different locations on the same day.

The second query ranks the dates of each product.

The third query retrieves the top five from each product.


If there is no Quantity field in the table as each entry would represent one unit of production, just change "sum(a.Quantity) AS Quantity" to "count(*) as Quantity" in the first query.

 

[Blkblts]

Another Alternative

I had this same issue with a little twist. I thought I would go ahead and post for future reference to someone else. This site is so helpful to me I hardly ever get to actually contribute.

I have a tblRankTest. I created the following:

qryCountproduct

SELECT tblRankTest.VolunteerID, tblRankTest.ProductID, Count(tblRankTest.ProductID) AS CountOfProductID, tblRankTest.ProductDesc, Sum(tblRankTest.Charge) AS SumOfCharge
FROM tblRankTest
GROUP BY tblRankTest.VolunteerID, tblRankTest.ProductID, tblRankTest.ProductDesc
ORDER BY tblRankTest.VolunteerID, Count(tblRankTest.ProductID) DESC;

and this 2nd query which gets the top 4 within each product.

SELECT a.VolunteerID AS volunteerid, a.ProductID AS productid, DCount("*","qryCountProduct","[VolunteerID] & [ProductID] >= '" & [VolunteerID] & [ProductID] & " ' and [VolunteerID] = " & [VolunteerID]) AS [Top Ranking], a.ProductDesc, a.SumOfCharge
FROM qryCountProduct AS a
GROUP BY a.VolunteerID, a.ProductID, a.ProductDesc, a.SumOfCharge
HAVING (((DCount("*","qryCountProduct","[VolunteerID] & [ProductID] >= '" & [VolunteerID] & [ProductID] & " ' and [VolunteerID] = " & [VolunteerID]))<=4))
ORDER BY a.VolunteerID, a.ProductID DESC;

No third query is need because I incorporated it in the 2nd Query.

 

[Jon K]

Generally it is a good point. The fewer the better. Two queries are generally better than three.

But when applied to domain aggregate functions, 'the fewer the better' still holds. These functions, unlike the SQL aggregate functions, are very inefficient and should be avoided as much as possible, particularly if the table is very large in terms of number of records. With two such functions in a select statement, they are run twice in each record.

 

[Jon K]

Following Pat's direction in another post to the knowledge base for "Q153747 - ACC How to Create a Top N Values per Group Query", I have been able to avoid using the inefficient DCount() function.


qry1_Prooduct:
SELECT [Part Number], [Manufacturing Date], sum(a.Quantity) AS Quantity
FROM tblProduct AS a
GROUP BY [Part Number], [Manufacturing Date];

qry2_Product:
SELECT *
FROM qry1_Product AS b
WHERE [Manufacturing Date] in (Select Top 5 [Manufacturing Date] from qry1_Product where [Part Number]=b.[Part Number] order by [Manufacturing Date] DESC);

Run the second query.

The first query groups the quantities of products manufactured in different locations on the same day.

The second query retrieves the top 5 Manufacturing Dates for each Part Number from the first query.