Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks

    This is the query to work out all employees worked hours

    SELECT [A Walton time step 2].SumOfExpr1, [B Parrish time step 2].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, [S Firth time step 2_2].SumOfExpr1, [j smith time step two].SumOfExpr1
    FROM [A Walton time step 2], [B Parrish 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], [S Firth 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], [S Firth time step 2] AS [S Firth time step 2_1], [S Firth time step 2] AS [S Firth time step 2_2], [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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    Only too glad to help <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Pat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •