Table Calculations in a report

[newbie2181]

Hi,
I have got a table that i have created by importing some data from a CSV file. I want to create a report to display some information from this table. I have created the report and now want to work out the average number for each of the columns in the table and then display this information in the report. How do i go about this? I have tried inputting =Avg([ALLIANZCCV])into a text box on the report ALLIANZCCV is the name of the column that i want to calculate the average for. I am assuming the problem is that i need to tell the report what table ALLIANZCCV is in but i don't know how to do this does any one know? Or if that isn't the problem then how can i get it working?

Thanks a lot!

Dan

 

[dcurtis]

OK, first your report record source needs to be set. If you already have an existing report, open it in design view, right click the square at the intersection of the rulers (horizontal and vertical) and select properties. Once the properties box is displayed select Record Source and select the table (or query, whatever you are using) you want to base the report on.

Are you using all the fields from the table on the report?

 

[newbie2181]

Hi,
No i'm not using them all and i would ideally like to use multiple tables as well if this is possible?

Thanks

Dan

 

[dcurtis]

You can set up a query to gather all the information you need from multiple tables, then base the report on the query. That gives you the option to calculate the average right in the query, or on the report. If you need help with this post back.

----
Access 2002 on a mixed Windows OS network.

 

[newbie2181]

Ok cheers that sounds like a tidier way of doing it anyway!

I will have a play about with that and see how far i can get first! lol

Cheers for your help

Dan

 

[newbie2181]

Cani just tap your brains one more time? I am having trouble figureing out how i am gonna attack this. I have a programme that creates a load of figures and then creates files all with the same name and a different extension. which is how you know what info is in what file. Now to complete one full report i am going to need to take informaition from 6 of these files! So do i need to import all 6 of these files into individual tables and then run a query on all of them tables to extract the data i'm interested in then pull the data from the table the query creates?

hope that makes sense

Thanks

Dan

 

[dcurtis]

I don't know what the other program is, but I would think that you have to import them all, then run the query. Depending on the data that's in the different files, you may be able to import them all into one table. What type of files are they, and what program is creating them? Also what type of data is each file?

 

[newbie2181]

Right the program is a piece od software that runs batch insurance quotes. so the information is just a list of numbers with column headings. But unfortunately the programs chucks the files out as bog standard files with all the data seperated by commas. so i change the file extention to .CSV and you can view the information in an excel table. does this answer you questions ok?

Dan

 

[dcurtis]

Are all 6 files the same? Do they each have a way to individually identify a row (Primary Key)?

----
Access 2002 on a mixed Windows OS network.

 

[newbie2181]

There are two sets of files if you like all showing similar data. I have included a little snippet of each table below if that is any help. So to fill in each report there will be 3 copies of Table 1 showing different figures and three copies of table 2 showing different figures.

(table 1) Declined No1 No2 No3
AXA CONWY 2001 53 22 63 90
BRIT ANGUS 1560 0 8 5
BRIT INSURANCE 0 0 69 123
CHAUCER MOTA-MARQUE 0 0 0 0
CHURCHILL 0 62 332 428
CORIN LIFESTYLE 10% 60 0 41 74

(Table 2)
Risk No AXA CONWY 2001 BRIT ANGUS BRIT
B32 2 1 222.45 Refer 224.34
B65 8 2 165.62 Refer 210.76
B69 4 3 206.21 Refer 204.7
B71 3 4 206.21 Refer 189.27
BB4 5 5 183.66 Refer 173.11
BR1 3 6 165.62 Refer 189.27
BR3 1 7 183.66 Refer 173.11

Thanks a lot

Dan

 

[dcurtis]

It appears that you will have to import each time (at least as I can tell from here). You can cut it down to 2 tables, each containing the similiar data. You may have to add a field to identify each row. From there build your source query.

Hope this helps