Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

averaging records 2

Status
Not open for further replies.

smuthcrmnl777

Technical User
Jan 16, 2006
104
US
averaging records Version: 2002 (10.0) XP

Posted on 01/16/06 02:36 PM
Posted by chippyles - Utter Access Addict
Posts: 86 -

Forum: Access Queries • Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Email Post
• Top of page
I have a table that contains records about manufacturing lines.

The left two chars of the Line field is the line the rest identify the sample.
The top two records are linked together. This is a test that occurred twice for the same sample. The top record shows the /F. I need to be able to create a query that will look for these two records throughout a table and take the XR fields for the two records and average them together.

DATE_CREATED TIME_CREATED Line XR
01/16/06 12:12:46 26-97/F 13.9503
01/16/06 12:11:18 26-97 11.255
01/16/06 06:24:26 26-96/F 12.6171
01/16/06 06:22:56 26-96 9.9299
01/16/06 03:59:36 26-95/F 12.7588
01/16/06 03:58:02 26-95 10.2075
01/16/06 02:48:38 26-94/F 8.4566
01/16/06 02:47:04 26-94 7.1909

I would technically want to produce a query that would produce this.

DATE_CREATED TIME_CREATED Line XR Average
01/16/06 0:00:00 26-97 12.60265
01/16/06 0:00:00 26-96 11.2735
01/16/06 0:00:00 26-95 11.48315
01/16/06 0:00:00 26-94 7.82375

What do you think?
 
create a new table with only non /F postfixed results
compare each record in original table by the third colomn with only the /f post fixed records
= (originaltable.value 1) AND (secondtable.value 1 + "/F")

if they are the same, add the fourth colomns together and divide by two


Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!
 
A starting point:
SELECT DATE_CREATED, #0:00:00# As TIME_CREATED, Left([Line],5) AS theLine, Avg(XR) AS [XR Average]
FROM tblSamples
GROUP BY DATE_CREATED, Left([Line],5);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
PHV:

Yours works but the line and sample are not always 5 chars. I can have anywhere between 4-7 chars. Can you help me change this so it works for the different char lengths?

 
Will the 2 Line values for the linked records always be the same except that one of them will have a '/F' at the end?

If so try this:

Left([Line],Instr([Line],'/') - 1)

instead of:

Left([Line],5)
 
Another way:
SELECT DATE_CREATED, #0:00:00# As TIME_CREATED
, Trim(IIf([Line] Like '*/F',Left([Line],Len([Line])-2),[Line])) AS theLine, Avg(XR) AS [XR Average]
FROM tblSamples
GROUP BY DATE_CREATED, Trim(IIf([Line] Like '*/F',Left([Line],Len([Line])-2),[Line]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
lynchg:

Your code works great!!

I like the fact that it does not return values have do not have /F associated with it.

Also, it may come up down the road, but is it possible to break up the averaging further? Currently, we are interested in only 1-11 and 1-11/F, but sometimes there will be two instances of 1-11 and 1-11/F.

So in the table it looks like this;

1-11/F
1-11
1-11/F
1-11

Your code takes all four and averages them together. I do not have a problem with this, but some engineers may want to split this up. Is there a way to do this so I can offer the solution before it becomes an issue?

Thanks again!!
 
I just wanted to add that duplicate line and sample numbers happen every once in awhile.
 
You would need to have some additional info from another field to discern between the duplicate values, then add that other field to your Group By clause.
 
I have been reviewing the numbers and the XR Avg column is not averaging the data. It is only taking the first record of the group and placing it as the results. What can be done to fix this?
 
Now that I look at the code this is something wrong with the line code

Code:
Left([sid1],InStr([sid1],'/')-1)

If there is a record that has 12-12/F and 12-12. It does not group this, but only takes the record that has the /F

 
Try:

IIf(InStr([sid1],'/')=0,[sid1],Left([sid1],InStr([sid1],'/')-1))

I don't know if you can stick that expression in a Group By clause though.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top