OVERFLOW error in both Query and Report

[LanieB]

Hello everyone,



I'll start by saying i'm relatively new to Access, but I am certified

I'm creating a database to store our Audit results. My issue is... I have all these different audit elements and each persons scores get entered into a form. I'm trying to get the average of everyones scores for each element. When I create a query and build the expressions to 'average' the score of these elements, it will only let me average 3 of them and then I get an overflow error. It acts the same way in the report as well.

I ultimately want to create a report that lists the elements followed by the percentage (average of the full company audit broke down by element). This way we can see our problem areas and where we need more training.

Only thing stopping me is OVERFLOW. Any suggestions? If you need more information from me just let me know!!

I'd appreciate any help I can get! Thank you!

[June7]

Certified what?

Can you provide data? Show query.

Info about overflow error http://bytes.com/topic/access/answer...overflow-error

[LanieB]

SELECT ([AES_AIR]![POA_A]+[AES_OCEAN]![POA_O])/2 AS [Valid POA on file], ([AES_AIR]![TIME_A]+[AES_OCEAN]![TIME_O])/2 AS [On Time], ([AES_AIR]![USPPI_A]+[AES_OCEAN]![USPPI_O])/2 AS USPPI, ([AES_AIR]![UCNEE_A]+[AES_OCEAN]![UCNEE_O])/2 AS [Ultimate CNEE], ([AES_AIR]![ICNEE_A]+[AES_OCEAN]![ICNEE_O])/2 AS [Intermediate CNEE], ([AES_AIR]![EIN_A]+[AES_OCEAN]![EIN_O])/2 AS [USPPI EIN], ([AES_AIR]![Origin_A]+[AES_OCEAN]![Origin_O])/2 AS [State of Origin], ([AES_AIR]![Related_A]+[AES_OCEAN]![Related_O])/2 AS [Related pty indicator], ([AES_AIR]![Routed_A]+[AES_OCEAN]![Routed_O])/2 AS [Routed indicator], ([AES_AIR]![Export_A]+[AES_OCEAN]![Export_O])/2 AS [Port of export], ([AES_AIR]![Destination_A]+[AES_OCEAN]![Destination_O])/2 AS [Destination country], ([AES_AIR]![B_A]+[AES_OCEAN]![B_O])/2 AS [Schedule B], ([AES_AIR]![License_A]+[AES_OCEAN]![License_O])/2 AS [License Auth], ([AES_AIR]![Weight_A]+[AES_OCEAN]![Weight_O])/2 AS [Gross Weight], ([AES_AIR]![ECCN_A]+[AES_OCEAN]![ECCN_O])/2 AS ECCN, ([AES_AIR]![Stat_A]+[AES_OCEAN]![Stat_O])/2 AS [Stat Qty], ([AES_AIR]![DF_A]+[AES_OCEAN]![DF_O])/2 AS [D/F Indicator], ([AES_AIR]![Value_A]+[AES_OCEAN]![Value_O])/2 AS [Value], ([AES_AIR]![Haz_A]+[AES_OCEAN]![Haz_O])/2 AS [Hazardous Freight Indicator], ([AES_AIR]![Verify_A]+[AES_OCEAN]![Verify_O])/2 AS [Verify Message], ([AES_AIR]![Fatal_A]+[AES_OCEAN]![Fatal_O])/2 AS [Fatal Error - same day], ([AES_AIR]![Alert_A]+[AES_OCEAN]![Alert_O])/2 AS [Compliance alert]
FROM AES_AIR, AES_OCEAN;


So this is the query that is pulling the information from the two different queries. I then want to create a totals line and average all of these so I just get one percentage... but it will only let me average 3 of them and then I get the overflow.

[June7]

What do you mean by 'let me average 3 of them and then I get the overflow'? You can average 3 but then trying a 4th fails? What 3 are you averaging? You are adding another term to the sums and dividing by 3?

You are pulling from two different queries but there is no JOIN clause.

Need data sample to analyze. And the two other queries.

Can you provide project?

[LanieB]

Tried to upload my database but it's too big let me explain a bit further.

We are freight forwarders so we have two different kinds of shipments (AIR and OCEAN). There is a Air audit table and an ocean audit table. Many elements are the same for both air and ocean, we want to have a report that shows across both air and ocean the average of the full company audit per element.

Example -- AES transmissions are done for both AIR and OCEAN so an example element would be
port of export 95% <-- that is the average I am trying to achieve. It will account for all the files audited in both AIR and OCEAN and gives us a full company average.

I created a query for AIR and a query for OCEAN, in those I am dividing points earned by points available for EACH element. Then the query I showed you is where I am adding those two numbers together (from the air and ocean query) and then dividing them by 2 so it gives you the average between AIR and OCEAN. My problem is, it gives me an average of every file and I am just trying to find the overall average, just one number. I then added a TOTAL line to the query and would select average (which would give me the one number I wanted). It would only let me do the 'AVERAGE' on the total line for 3 elements and then if you tried to do a 4th it would give you the OVERFLOW ERROR. If there is another way to achieve this 'single number' that is averaging all the audits across both air and ocean I am completely open to suggestions!!! I may have taken the most complicated route possible to get this number. I've never made a database this complicated, and I'm pretty sure I'm making it more complicated than it needs to be!!!

Thank you for all of your assistance. It is greatly appreciated!

[June7]

Did you run Compact & Repair, zip if still large, up to 2mb zip allowed. Make copy and remove data, leave just enough to test. Or extract only the relevent objects to another project. Or upload to a fileshare site such as box.net and post link to the file.

Because Air and Ocean have similar data but are not related, UNION query is needed.

[LanieB]

Here is the full database! I had to remove some stuff so it would be small enough, but everything you need to see should be there!

Thank you again for your assistance!

The Query 'Company_AES' is the one I am working on to get the average.

[June7]

As I thought, a UNION query is needed. It will produce the result that would be as if the common fields of the two tables were combined into one table and the different fields in child tables. There is no wizard or designer for a UNION, must type into the SQL View window of the query designer. Your option is to UNION the source tables or the select queries. UNION would be like:

SELECT "AIR" As Source, [AES_POA_AE] As AES_POA, ...other fields FROM [AirExport]
UNION SELECT "OCEAN", [AES_POA_OE]), ...other fields FROM [OceanExport];

Now you can use this query as the basis for other queries to calc fields and to sum and average with grouping.

If fields in table or saved SELECT queries are in the same order and have same names, can do:
SELECT * FROM query1
UNION SELECT * FROM query2;

Be aware that UNION will not allow duplicate records. If the data is the same for all fields of multiple records, only one will show. If you want all, even if apparently duplicate, to return, use UNION ALL.

[LanieB]

You are the greatest person on the face of this plant! It's working... for now, until I screw something up again!

JUNE7 IS THE BEST!

[June7]

Glad it worked. Another thing to be aware of about UNION queries. It is my experience that VBA will not open a recordset from a UNION query or from any query that draws data from a UNION query. If a UNION query is involved anywhere in a sequence of queries, VBA will not work with the data.