A little help plz!

[Gunit]

I need some help on how I could approach getting this task done. I have a table with 3 fields.

Field 1
Field 2
Field 3

Condition: If (Alpha) is duplicate in Field 2 and the (numeric) value in Field 1 is common for the first 9 of 10 alpha-numeric char, then average Field 3 values.

I.E. Table

Field-----1Field--2|Field-3
53-5011.01|Example|$1.83
53-5011.02|Example|$1.98

 

[ecniv]

If you don't mind not having the first field in full you could probably do:

select left([field 1],9) as smlfield1,[field 2],avg([field 3]) as AverageCost
From table
group by left([field 1],9),[field 2]

Check the avg function (average) or build in the query builder...


Vince

 

[The_Doc_Man]

If the format is exactly as you described, nn-nnnn.nn, then you could do an InStr$ function to locate the . and take only the stuff up to/including it.

Left$( [Field1], InStr$(1,[Field1],".") )

(ALWAYS check me from the Help files when I shoot from the hip - I forget which of parameter 2 and 3 is the thing you wanted to find and the other is the place to look, for the InStr$ function.) Parameter 1 of InStr$ is an offset but if you use 1, you search the whole string. What this ugly fragment does is return the substring "nn-nnnn." including the dot.

NOTE: This falls flat on its face if the field ever has an aberrant format that is missing the dot. But it doesn't matter whether you have 0, 1, 2, 3, or MANY digits to the right of the dot. The Left$ function takes only the stuff to the left and you said you wanted to ignore the stuff to the right. I figure that if the extracted sequence always ends with a dot, Access won't care.

Now if you write a query with the (corrected) construct as shown above for a field name, perhaps with a label to name the field WITHIN THE QUERY, then the next step would be a simple summation query that takes your averages using the above-described field for a GROUP BY, which is one of the options within summation queries.

 

[Gunit]

My apologies...Field 1 may or may not be common in number up to the 9th char position. Of the ones that are common based on this, I want to average Field 3. For the ones that aren't common up to the 9th char position, I do not want to average. Table contains some 7000+ records. Sorry!

 

[Gunit]

Would I be able to get more help??