Query / Datasort

[bpfan]

I currenty have table with the following information.

SalesOffice Client ProductHeld Volumes

Office1 Client1 Product1 $10000
Office1 Client1 Product2 $20000
Office2 Client1 Procuct1 $5000
Office1 Client1 Product3 $50000
Office3 Client1 Product2 $60000

I need to be able to identify the sales office with the
highest frequency for a given client (ie. Office1 in this
example). With this information I need to update the
salesoffice field to reflect the sales office with the
highest frequency. For example, in the above office 1
would be identified as the sales office with the greatest
frequency. I need to then change the record for office 2
and office 3 to office1.

Can anyone help me with this problem. Thanks in advance.


.

 

[RFletch]

Are there always the same number of sales offices in the table? If so you could have an integer variable for each, eg
dim sales1 as integer, sales2 as integer, sales3 as integer
then set each variable to 0 and start at the beginning of the table and count through, incrementing each sales office each time they occur with the given client,eg

sales1 = 0
sales2 = 0
sales3 = 0
dim db as dao.database
db rcd as dao.recordset
set db = currentdb
set rcd = db.openrecordset("tablename&quot *
rcd.movefirst
Do Until rcd.EOF
if rcd!client = "client 1" Then
if rcd!salesoffice = "Office1" Then *
sales1 = sales1 + 1
else
if rcd!salesoffice = "Office2" Then *
sales2 = sales2 + 1
else
if rcd!salesoffice = "Office3" Then *
sales3 = sales3 + 1
endif
endif
endif
End If
rcd.MoveNext
Loop

The lines above with asterisks (*) next to them are lines where you need to substitute either a table name or field name for what I have written.

Hope this helps.