If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query won't work in new database...Help!
OK, I have a second version of a database that I am doing because I am much,
much more proficient now. I started over from scratch because I hadn't done the relationships and stuff right the first time around. I am trying to recreate my queries and stuff and am running into a problem with my attendance tracking query. The fields I am using for my query a First_Name, Last_Name, Contact_Name (which is a formula to combine the first and last name) and the leave remaining formula. Here is the formula I am using to calculate my leave time: VRemaining: First([Vacation_Given])-Nz(Sum(IIf([Reason]='Vacation',[Hours_Taken],0)),0). Now, I copied this formula from my original database where it worked fine. However, when I try to run it on the new database I get the following error: "You tried to execute a query that does not include the specified expression 'First_Name' as part of an aggregate function. I have no idea what that means and have tried a thousand different things to troubleshoot and work around it. Nothing has worked. The only difference between this and my original database is that in the original one I had [Employee_Name] instead of [Contact_Name] and I figure it has something to do with that, but for the life of me I cannot figure out what. Can someone help me? If needed I can post the SQL code for both the original and the new. Thanks! Brooke |
#2
|
|||
|
|||
Query won't work in new database...Help!
Looks like the [Employee_name] is set to the wrong expression in the query.
Try setting it to "group by" or "where". My guess would be that it is set to sum or something else which would calculate. -- Maurice Ausum "BrookieOU" wrote: OK, I have a second version of a database that I am doing because I am much, much more proficient now. I started over from scratch because I hadn't done the relationships and stuff right the first time around. I am trying to recreate my queries and stuff and am running into a problem with my attendance tracking query. The fields I am using for my query a First_Name, Last_Name, Contact_Name (which is a formula to combine the first and last name) and the leave remaining formula. Here is the formula I am using to calculate my leave time: VRemaining: First([Vacation_Given])-Nz(Sum(IIf([Reason]='Vacation',[Hours_Taken],0)),0). Now, I copied this formula from my original database where it worked fine. However, when I try to run it on the new database I get the following error: "You tried to execute a query that does not include the specified expression 'First_Name' as part of an aggregate function. I have no idea what that means and have tried a thousand different things to troubleshoot and work around it. Nothing has worked. The only difference between this and my original database is that in the original one I had [Employee_Name] instead of [Contact_Name] and I figure it has something to do with that, but for the life of me I cannot figure out what. Can someone help me? If needed I can post the SQL code for both the original and the new. Thanks! Brooke |
#3
|
|||
|
|||
Query won't work in new database...Help!
Thank you sooooo much!!!! That was what was wrong. Sorry I bothered you for
such an easy fix. Brooke "Maurice" wrote: Looks like the [Employee_name] is set to the wrong expression in the query. Try setting it to "group by" or "where". My guess would be that it is set to sum or something else which would calculate. -- Maurice Ausum "BrookieOU" wrote: OK, I have a second version of a database that I am doing because I am much, much more proficient now. I started over from scratch because I hadn't done the relationships and stuff right the first time around. I am trying to recreate my queries and stuff and am running into a problem with my attendance tracking query. The fields I am using for my query a First_Name, Last_Name, Contact_Name (which is a formula to combine the first and last name) and the leave remaining formula. Here is the formula I am using to calculate my leave time: VRemaining: First([Vacation_Given])-Nz(Sum(IIf([Reason]='Vacation',[Hours_Taken],0)),0). Now, I copied this formula from my original database where it worked fine. However, when I try to run it on the new database I get the following error: "You tried to execute a query that does not include the specified expression 'First_Name' as part of an aggregate function. I have no idea what that means and have tried a thousand different things to troubleshoot and work around it. Nothing has worked. The only difference between this and my original database is that in the original one I had [Employee_Name] instead of [Contact_Name] and I figure it has something to do with that, but for the life of me I cannot figure out what. Can someone help me? If needed I can post the SQL code for both the original and the new. Thanks! Brooke |
#4
|
|||
|
|||
Query won't work in new database...Help!
Hi Brook,
No bother at all just glad you got it to work :-) -- Maurice Ausum "BrookieOU" wrote: Thank you sooooo much!!!! That was what was wrong. Sorry I bothered you for such an easy fix. Brooke "Maurice" wrote: Looks like the [Employee_name] is set to the wrong expression in the query. Try setting it to "group by" or "where". My guess would be that it is set to sum or something else which would calculate. -- Maurice Ausum "BrookieOU" wrote: OK, I have a second version of a database that I am doing because I am much, much more proficient now. I started over from scratch because I hadn't done the relationships and stuff right the first time around. I am trying to recreate my queries and stuff and am running into a problem with my attendance tracking query. The fields I am using for my query a First_Name, Last_Name, Contact_Name (which is a formula to combine the first and last name) and the leave remaining formula. Here is the formula I am using to calculate my leave time: VRemaining: First([Vacation_Given])-Nz(Sum(IIf([Reason]='Vacation',[Hours_Taken],0)),0). Now, I copied this formula from my original database where it worked fine. However, when I try to run it on the new database I get the following error: "You tried to execute a query that does not include the specified expression 'First_Name' as part of an aggregate function. I have no idea what that means and have tried a thousand different things to troubleshoot and work around it. Nothing has worked. The only difference between this and my original database is that in the original one I had [Employee_Name] instead of [Contact_Name] and I figure it has something to do with that, but for the life of me I cannot figure out what. Can someone help me? If needed I can post the SQL code for both the original and the new. Thanks! Brooke |
Thread Tools | |
Display Modes | |
|
|