Query calculations, help!!!! (Access 97)

Topic

New Reply

Hello again people. I’m having trouble with a calculation in a query. I have two queries, each of which has worked out a number of hours between two points (one is the number of hours an employee is at work, the other is the number of hours and employee has been usefully employed). I am trying to use a third query to give me a percentage efficiency level for each employee on the database. So far I have tried adding each of the queries individually to the calculation query, and both at the same time, no result for either.

The error is the same for all attempts, ‘invalid bracketing for *****’ despite the fact that I used the query builder, as well as trying to type the whole thing in from the basic [query]![fieldname]![etc] basis.

I am not up to writing code for this, unless it’s a fairly simple SQL mod.

Until recently I have just used the two queries to give me the data and then put the numbers into Excel and, hey presto, result. I would now like to automate this function, but Access is resisting my best efforts.

Thanks for taking the time to read this far.

Reply | Quote

Replies

We will need to see the individual queries you are currently using in order to give you much help. You can post them as SQL text strings, or you could simply attach a small Zipped database with the queries and tables.

Reply | Quote

Thanks

This is the query to work out all employees worked hours

SELECT [A Walton time step 2].SumOfExpr1, .SumOfExpr1, [D Brooks time step 2].SumOfExpr1, [D Cantelo time step 2].SumOfExpr1, [D Gough time step 2].SumOfExpr1, [D Powell time step 2_1].SumOfExpr1, [G Owen time step 2_1].SumOfExpr1, [K Jones time step 2_1].SumOfExpr1, .SumOfExpr1, [j smith time step two].SumOfExpr1
FROM [A Walton time step 2], , [D Brooks time step 2], [D Cantelo time step 2], [D Gough time step 2], [D Powell time step 2], [G Owen time step 2], [K Jones time step 2], , [D Powell time step 2] AS [D Powell time step 2_1], [G Owen time step 2] AS [G Owen time step 2_1], [K Jones time step 2] AS [K Jones time step 2_1], AS , AS , [j smith time step two];

As you can see it uses previous queries, an example of which is below

SELECT Sum([A Walton time step 1].Expr1) AS SumOfExpr1
FROM [A Walton time step 1];

And the first query in the string

SELECT [Downtime data for time based queries].[Repair Carried out by], [Downtime data for time based queries].[Assisted by 1], [Downtime data for time based queries].[Assisted by 2], [Downtime data for time based queries].[Assisted by 3], (DateDiff(‘n’,[Downtime data for time based queries]![Repair Started],[Downtime data for time based queries]![Repair Completed]))/60 AS Expr1
FROM [Downtime data for time based queries]
WHERE ((([Downtime data for time based queries].[Repair Carried out by])=”A. Walton”)) OR ((([Downtime data for time based queries].[Assisted by 1])=”A. Walton”)) OR ((([Downtime data for time based queries].[Assisted by 2])=”A. Walton”)) OR ((([Downtime data for time based queries].[Assisted by 3])=”A. Walton”));

The second of the queries I’m trying to calculate from is this one

SELECT [Total Clocked hours].[Brooks, D#], [Total Clocked hours].[Cantelo# D#], [Total Clocked hours].[Firth, S#], [Total Clocked hours].[Gough, D#], [Total Clocked hours].[Jones, K#], [Total Clocked hours].[Owen, G#], [Total Clocked hours].[Parrish, B#], [Total Clocked hours].[Smith, J#], [Total Clocked hours].[Walton, A#]
FROM [Total Clocked hours]
WHERE ((([Total Clocked hours]![Week Number])=37));

I know this is a bit of a roundabout way of doing this, but without learning to use code it’s the only way I am capable of getting these two answers. Besides, it didn’t take long to set up and works

Reply | Quote

I’ve managed to get a result out of my current queries by changing them to make table queries and then using yet another query to do the math. I was wondering if anyone had spotted what I’ve done wrong in the previous queries that stops the data in them from being manipulated by a query, but allows it to be manipulated once in table format.

I’ve looked at the properties options for the query, but can’t spot any obvious errors.

Reply | Quote

Why don’t you just zip up the database and let people have a look at it?
Just tell us which query (s) is the offender.
Pat

Reply | Quote

The database has been running for 2 years now, and is around 20 meg in size, even with only the relevant forms, queries and a little sample data I have almost a meg of ‘stuff’. It didn’t seem to zip very well, still around 800kb. As I’m on a 56K connection uploading this isn’t too high on my list of things to wait for.

Thanks for the offer of your assistance though, I don’t want to sound as though I don’t appreciate the help offered, I do.

If I can find time to re-create the working parts in question separate from the user interface I’ll zip this and upload it.

Reply | Quote

Yippee!!!!!

Solved the problem. While trying to get the ‘stuff’ into a small enough database to upload I found the error, one of the tables my queries are based on is an Excel file (linked). Apparently linked files do not have to obey the same naming conventions as tables, they have a column name, but if you try to import the table (I was doing this to upload the database) Access will not recognise the table names for Access tables. I changed the names of the columns in the Excel spreadsheet and my queries now work.

I posted this (embarrassing moment) in case it helps anyone else avoid this pitfall.

Thanks to those who offered help.

Reply | Quote

Only too glad to help
Pat

Reply | Quote