Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Location
    Kansas City
    Posts
    18

    Unanswered: Problems with simple query

    Hi all,

    Having a problem with a little query. Trying to pull in the sum of all used vacation hours from one table and stick it in a text box on a form. It will later be used in another calculation but for now I can't get the query to perform. I have tried every version of tailoring this query to get it to work but no luck thus far.

    Any ideas, I keep receiving the #NAME?

    SELECT Sum([2004UsedVaca].[Vaca_Hours_Used]) AS Expr1
    FROM 2004UsedVaca, 2004Employee
    WHERE ((([2004UsedVaca].[Name_Ident])=[2004Employee].[Name_Ident]));
    Mike R.

  2. #2
    Join Date
    Jun 2004
    Posts
    96
    you have to either join the 2 queries with [Name_Ident] or used nested query.

    inner join:
    SELECT Sum([2004UsedVaca].[Vaca_Hours_Used]) As Expr1 FROM 2004UsedVaca INNER JOIN 2004Employee ON [2004UsedVaca].[Name_Ident])=[2004Employee].[Name_Ident];

    nested:
    SELECT Sum([2004UsedVaca].[Vaca_Hours_Used]) As Expr1 FROM 2004UsedVaca WHERE [2004UsedVaca].[Name_Ident] IN (SELECT [Name_Ident] FROM [2004Employee]);

    Cyherus

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    its one thing to make a query work, and another to stuff the result in a textbox.

    guessing that you have an unbound textbox, either feed your query result to a recordset and the recordset!fieldname to your textbox

    or

    cripple the query so it does the SELECT you want but not the SUM, then
    DSUM("[Vaca_Hours_Used]) ", "nameOfYourCrippledQuery")

    or

    keep the SUM in the query and use DFIRST("fieldname", "queryname")

    in general - you cannot directly pass the result of a SELECT (containing potentialy hundreds of thousands of records) to an unbound textbox (which can contain one or zero lumps of data).

    DXXXX() domain aggregate functions return one or zero lumps of data so the return sits happily in a textbox

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City
    Posts
    18

    Thanks so much but still troubled

    I tried all of both of your suggestions....was unable to get a result. Also looks like Access 2000 will not handle the inner join, that's strange. I tried crippling the query and using DSUM, I tried the nested select.....I also maybe should have said that I was trying to put the sum of all used vacation hours in the text box for each employee, not the overall sum. I want to be able to view the sum of each employees hours, not the grand total.

    This is really frustrating, it should not be this difficult.

    Thanks for all of your help, it is very much appreciated. If you have any other suggestions please let me know. I can send the db over if you would like to look at it.

    Mike
    Mike R.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Are you unable to get this to run as ONLY A QUERY (eg: design new QUERY, insert sql, run, doesn't work), or are you having issues dumping it somewhere else?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Also looks like Access 2000 will not handle the inner join
    Not true.

    Select *
    from tableA ta
    INNER JOIN
    tableB tb ON
    ta.id = tb.id
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Jun 2004
    Posts
    55
    SELECT DISTINCTROW Sum([2004UsedVaca].[Vaca_Hours_Used]) As Expr1 FROM [2004UsedVaca] INNER JOIN [2004Employee] ON [2004UsedVaca].[Name_Ident])=[2004Employee].[Name_Ident];

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City
    Posts
    18

    Will run fine as query.....

    just can't dump it in the unbound textbox using any method so far. The query pulls the sum of all users in the table but when I dump it in the textbox I want to pull only the hours for currently selected user.

    Thanks again,
    Mike
    Mike R.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by mike5377
    just can't dump it in the unbound textbox using any method so far. The query pulls the sum of all users in the table but when I dump it in the textbox I want to pull only the hours for currently selected user.

    Thanks again,
    Mike
    Currently selected from where?

    Are you referring to grouping levels?

    If this report is only run for one user, you need to add a where clause.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select sum(v.column)
    from vacation v
    where v.employee_id = [param]
    Last edited by r123456; 06-18-04 at 10:27.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  11. #11
    Join Date
    Apr 2004
    Location
    Kansas City
    Posts
    18

    Currently selected from....

    I have the employee form which scrolls the employees and pulls it's data from the employee table and provides data for each employee, in the seperate UsedVaca table I have all of the rows of vaca days for each user, most having several rows per user and I want to place in the employee form the sum of used vaca hours for the currently selected user on the form.

    This is where I am running into problems.

    Thanks again for all of your responses, it is very much appreciated.

    Mike
    Mike R.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by mike5377
    I have the employee form which scrolls the employees and pulls it's data from the employee table and provides data for each employee, in the seperate UsedVaca table I have all of the rows of vaca days for each user
    Then you have a few options, one is DSUM() using the currently selected [Name_Ident] of the form in an unbound text box. The other would be to use a subform. You may actually find the subform more useful, as you could detail each occurance of vaca_hours, as well as the sum for the current employee.

    DSUM() would be roughly of the syntax:

    =DSUM("[Vaca_Hours_Used]", "[2004UsedVaca]", "[Name_Ident] = " & me.Name_Ident)

    The subform would be fairly self explanatory. The wizard will ask you the table and fields you'd like to use for the criteria. Once the subform is designed, you could create a form footer, and place something like =SUM([Vaca_Hours_used].

    Couple options, nothing to scary.



    Edit: As an aside, you may want to consider altering your schema a bit as well. What happens to this DB in 2005? New UsedVaca table? Perhaps you could consider simply "UsedVaca", and filtering the results based on the year you'd like to use.

    It will save you some dev time in the future.
    Last edited by Teddy; 06-18-04 at 10:40.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Apr 2004
    Location
    Kansas City
    Posts
    18

    Success!.....but now another question as well :(

    I just went with the option of creating subforms to populate the data from the query, in a bit of a time constraint. I'm actually now running into a bit of a new problem as well. Doing some reading on it right now. On my main employee form, I have the subforms that I used to solve the prior problem as well as three other subforms that are tied to the used_vacation, used_sick, and used_personal time tables. They have two bound text boxes and a commit record button on each subform and are only for data input but right now as I go through the employee records I get either the first or last record in those 3 tables populated into the subform as well. I don't ever want data to populate in them, ever. I simply want them to be there for input only and to clear when the main form is opened and clear on the click action of the save button so that another date may be entered and saved over and over again. I'm off to do some research now, as earlier, any assistance is greatly appreciated. I know it's just a little vba but i'm not too familiar with it yet.

    I sincerely appreciate everyones help and I will continue to work with it and some of your solutions.

    Thank You,
    Mike
    Mike R.

Posting Permissions

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