Report Last Status only of Each Product.

[wjmontague]

I have 20 products lines, each product line has serial numbers assigned. Each serial number takes up to a year to produce and are in various stages of production. I must give a weekly report on over a 100 different products. My report needs the last data entry for each serial number grouped by products. I do not need all the records for each serial number only the last entry.

ie.

Product Serial# Comment

aProductline 027 waiting parts
aProductline 026 testing part
aProductline 025 inspection

bProductline 423 repair
bProductline 422 etc

Each of the above lines represent the last entry for that serial number only grouped by products. I am looking for the largest ID number for each different product and serial number

 

[suntsu]

Hi Wj,

Give this SQL a try,

SELECT tblYourTable.Product, Max(tblYourTable.[Serial#]) AS [MaxOfSerial#], Last(tblYourTable.Comment) AS LastOfComment
FROM tblYourTable
GROUP BY tblYourTable.Product
ORDER BY tblYourTable.Product

(change 'tblYourTable' to the name of your table, and also check the field names are the same.)

If you have any problems just shout.

SunTsu

 

[wjmontague]

Sounds Good can you tell me also how I can implement this SQL in my report. I am very new at this and could not figure out how to use the information you gave me. Thank you.

 

[suntsu]

Create a new query in design view, then select view|SQL.
Paste the code above into the query, save it and close.

Now in your report design, press F4 to bring the properties window up, select data then within the recordsource field select the name of the query that you just created.

Hope this helps

SunTsu