I must do a report by crystal report6 about testing result that concerns 4 tables as follows:
1. Product table => ID, Name, …..(other details of product)
Eg. F102 Bead wire02
2. Specification => Table that keep specification of each product
Effect_date : effective date for this product (mm/dd/yyyy)
Product_ID : id that links to product table
Aname: Type of analysis
Min: Minimum value
Target: Target value
Max: Max value
XXXX = Aname in specification
Test value = Value of testing
Eg.
Effect_date-----Product_ID------Aname--------------Min----Target---Max
06/1/2000-------F102-------------Rugosity-------------3.5---------5---------7
06/1/2000-------F102-------------Diameter -------------5----------7---------10
06/1/2000-------F102-------------WL---------------------2----------5---------7
06/1/2000-------F103-------------WL---------------------2----------8---------10
07/1/2000------F102--------------Rugosity--------------3--------- 5--------- 7
3. Production record
Ticket_no----Product_ID: --------Production_date …..other production detail
10005--------- F102------------------07/25/2000
10006--------- F102------------------07/26/2000
10007--------- F102------------------07/27/2000
4. Test result
Ticket_NO: Ticket number in production line
Product_ID: id that links to product table
TestDate: Testing date
XXXXX1 : Test value
…….
XXXXXn: Test value
Eg.
Ticket No---------Product_ID --- ------TestDate----- Rugosity---------Diameter---------WL
1005---------F102------------------08/1/2000---------5------------------ 7------------------5
1006---------F102------------------08/1/2000---------6------------------7------------------5.5
1007---------F102------------------08/2/2000---------5------------------ 7------------------5
1. How I can get specification value of the product. The specification has constraint that the effective date <= test date and it must be up to date specification.
2. How I can calculate min, avg, max value of ticket no 1005, 1006 and 1007
My designed report is….
Product ID:
Product Name: Bead wire02
Production date: 07/25/2000 – 07/27/2000
Number of product = 3
------------------------------------Test result----------------------------Specification
ITEMS--------------Min---------Avg---------Max-------------Min---------Avg---------Max
Rugosity-------------5---------5.33---------6------------------3------------5-------------7
Diameter----------- 7---------- 7------------7------------------ 5------------7------------10
WL------------------- 5---------5.16---------5.5----------------2-----------5--------------7
………….
I can code in VB to show on listview control but I can’t do it in Crystal report.. what I can do
I would like to use parameter fields but it has a lot of fields (about 10-17 analysis in specification)
