Calculations from query

  • Thread starter Thread starter sad
  • Start date Start date
S

sad

Hi,
I have a query with the following fields:
Cust-num Order-num Order-line OTP
AL234 CO343 1 4.5
AL234 CO343 2 14.6
AL234 CO764 1 25.3
AL673 CO903 1 12.3
AL982 CO356 1 5.77
AL542 CO215 1 45.4
AL542 CO215 2 23.5

I want to have a table that lists each unique Cust-num along with the
average OTP for that customer based on the previous query.

Example Table generated:
Cust-Num Average OTP
AL234 (4.5+ 14.6+25.3)/3
AL673 12.3
AL982 5.77
AL542 (45.4+23.5)/2

How do I count # of records in previous query. If I use count or sum
functions, I have to specify a specific Cust-num, but I want to do it
for all of them not a specific one.
Any help is appreciated. Thanks.
 
"Sad", you are doing this for a Report aren't you? You say you want
to view it in a table, but I'm sure that's not what you meant.

In Reports, you can Group on Cust_Num and in the Group Footer use the
Average() function to give you the answer.

Access isn't Excel, tables are just dumb containers of data they have
no calculation, recalc or other spreadsheet like abilities therefore
canculated data in tables is a no-no.

Is this what you were looking for?

Brett

Hi,
I have a query with the following fields:
Cust-num Order-num Order-line OTP
AL234 CO343 1 4.5
AL234 CO343 2 14.6
AL234 CO764 1 25.3
AL673 CO903 1 12.3
AL982 CO356 1 5.77
AL542 CO215 1 45.4
AL542 CO215 2 23.5

I want to have a table that lists each unique Cust-num along with the
average OTP for that customer based on the previous query.

Example Table generated:
Cust-Num Average OTP
AL234 (4.5+ 14.6+25.3)/3
AL673 12.3
AL982 5.77
AL542 (45.4+23.5)/2

How do I count # of records in previous query. If I use count or sum
functions, I have to specify a specific Cust-num, but I want to do it
for all of them not a specific one.
Any help is appreciated. Thanks.
Click to expand...

Cheers,
Brett
 
I am not really familiar with Reports, But I wanted to use tables or
queries, because it's easier to work with the data if it's in query or
table than report. For example, if I want to make a graph of the data
later or link the data to a form, I think it's hard to do it with a
report, but if the data is in tables or queries you can do anything.
Thanks for your help.
 
Back
Top