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 based off of another query?
Hi,
Sorry if the subject is confusing, doesn't make sense, or just makes me look stupid. I've been learning Access on my own at work, and I need to reproduce a functionality that we have in Excel... Basically, let's say you have a pricing estimation method that you've established for assigning costs to applications when the application owners have been unable to estimate the price. The method works like this: you have a 4-character code, like MGWS, based on 4 different parameters of the application (like # of users, etc.). There are about 20 codes, total, and about 1800 applications that fit into one of these code "categories." Each category has an average cost and an average # of users, based on data from each of the applications that DOES have cost estimates. Every time a new application is "found," or cost information is provided, it needs to add into the data for the appropriate code, and change the averages for that code. Additionally, every application, when displayed in a form or report, has to be connected to the average data for the code "category" in which the application resides. In other words, say "excel" is an application, and its code is MGWS. I need the report and form to be able to show the MGWS average cost and average number of users next to "excel." Also, next to every other MGWS application. The only way I've been able to make this work in access so far is to have two worksheets in Excel, one with details on all the codes, as well as average information, and another with each code, row by row, with the average information from the other worksheet listed in the same row. That summary table (the second table) can be put into Access, and I can reference it to assign the right code costs to each application. The problem is that every time another cost comes in, I'll have to repaste the table and remake all of the connections. I'd like to update the detailed data in Access so that everything changes with just a simple query. Initially I've tried to program this, and I am running into an error that I'll describe below. Basically, I used a pre-programmed Access query to determine average data for each code, and then I tried to create a second query that would put this information into a table, row by row, in Access. In other words, each "row" would have a code, and the average data, effectively recreating the table we have in Excel. Here is the SQL code I (and a programmer here) came up with to take the average data from the first query and put it into the table: UPDATE Code_Table SET Code_Table.[Count] = "Count of MGWM Applications" WHERE (((Code_Table.Code)="MGWM")); Code_Table is the table we want to use to duplicate the Excel table with one code per row. MGWM_Applications_Query is the first query, where Access has pulled the averages together for the one code. [Count] is the Field in the Code_Table that holds the total count of applications with that code. "Count of MGWM Applications" is the automatically-generated field name for Access's count of MGWM Applications. Code is the field in the Code_Table that holds all of the 4-character codes, like MGWM. Here is the error I am getting: "Access did not update one field due to a type conversion failure." The field type for the Count of MGWM Applications is "General Number," and the field type for "Count" is "Number." What am I doing wrong? By the way, if I replace all the code referencing the first query, and just tell it to update Code_Table.Count as "2" where Code_Table.Code="MGWM," it works. Help! Am I trying to do the impossible, or am I just doing the really hard incorrectly? Thank you! -Aaron |
#2
|
|||
|
|||
Query based off of another query?
Aaron wrote: Hi, Sorry if the subject is confusing, doesn't make sense, or just makes me look stupid. I've been learning Access on my own at work, and I need to reproduce a functionality that we have in Excel... Basically, let's say you have a pricing estimation method that you've established for assigning costs to applications when the application owners have been unable to estimate the price. The method works like this: you have a 4-character code, like MGWS, based on 4 different parameters of the application (like # of users, etc.). There are about 20 codes, total, and about 1800 applications that fit into one of these code "categories." Each category has an average cost and an average # of users, based on data from each of the applications that DOES have cost estimates. Every time a new application is "found," or cost information is provided, it needs to add into the data for the appropriate code, and change the averages for that code. Additionally, every application, when displayed in a form or report, has to be connected to the average data for the code "category" in which the application resides. In other words, say "excel" is an application, and its code is MGWS. I need the report and form to be able to show the MGWS average cost and average number of users next to "excel." Also, next to every other MGWS application. The only way I've been able to make this work in access so far is to have two worksheets in Excel, one with details on all the codes, as well as average information, and another with each code, row by row, with the average information from the other worksheet listed in the same row. That summary table (the second table) can be put into Access, and I can reference it to assign the right code costs to each application. The problem is that every time another cost comes in, I'll have to repaste the table and remake all of the connections. I'd like to update the detailed data in Access so that everything changes with just a simple query. Initially I've tried to program this, and I am running into an error that I'll describe below. Basically, I used a pre-programmed Access query to determine average data for each code, and then I tried to create a second query that would put this information into a table, row by row, in Access. In other words, each "row" would have a code, and the average data, effectively recreating the table we have in Excel. Here is the SQL code I (and a programmer here) came up with to take the average data from the first query and put it into the table: UPDATE Code_Table SET Code_Table.[Count] = "Count of MGWM Applications" WHERE (((Code_Table.Code)="MGWM")); Code_Table is the table we want to use to duplicate the Excel table with one code per row. MGWM_Applications_Query is the first query, where Access has pulled the averages together for the one code. [Count] is the Field in the Code_Table that holds the total count of applications with that code. "Count of MGWM Applications" is the automatically-generated field name for Access's count of MGWM Applications. Code is the field in the Code_Table that holds all of the 4-character codes, like MGWM. Here is the error I am getting: "Access did not update one field due to a type conversion failure." The field type for the Count of MGWM Applications is "General Number," and the field type for "Count" is "Number." What am I doing wrong? By the way, if I replace all the code referencing the first query, and just tell it to update Code_Table.Count as "2" where Code_Table.Code="MGWM," it works. Help! Am I trying to do the impossible, or am I just doing the really hard incorrectly? Thank you! -Aaron Bump, please help! This ended up off the first page, right when it was posted. |
#3
|
|||
|
|||
Query based off of another query?
I still don't quite understand the whole project, but I'll start with the
easy part: Here is the error I am getting: "Access did not update one field due to a type conversion failure." The field type for the Count of MGWM Applications is "General Number," and the field type for "Count" is "Number." What am I doing wrong? You're trying to put a string into a numeric field (Count). When you try to put a "2" into Count, Access can easily convert this into a number. Access chokes on "Count of MGWM Applications" being put into the Count field. You'll need to make a query that calculates this value and then join it into this update query.. As for the whole project, as a new entry is put in, you need to update the values for all the existing records in the table, right? This is my vague understanding of what you're doing. In this case, I'd have queries that create the new averages. Then, I'd use those subqueries in the main queries that update the average columns. If you want to do something like Excel, where values cascade down from row-to-row, you need to use a recordset/cursor and a little bit of vba coding. Hope this helps a little bit |
#4
|
|||
|
|||
Query based off of another query?
Todd wrote: I still don't quite understand the whole project, but I'll start with the easy part: Here is the error I am getting: "Access did not update one field due to a type conversion failure." The field type for the Count of MGWM Applications is "General Number," and the field type for "Count" is "Number." What am I doing wrong? You're trying to put a string into a numeric field (Count). When you try to put a "2" into Count, Access can easily convert this into a number. Access chokes on "Count of MGWM Applications" being put into the Count field. You'll need to make a query that calculates this value and then join it into this update query.. So if there is a formula in that space, instead of "Count of MGWM Applications," it should work, as long as the formula produces a number, right? I'll try that. As for the whole project, as a new entry is put in, you need to update the values for all the existing records in the table, right? This is my vague understanding of what you're doing. In this case, I'd have queries that create the new averages. Then, I'd use those subqueries in the main queries that update the average columns. I think this is what I'm trying to do. If you want to do something like Excel, where values cascade down from row-to-row, you need to use a recordset/cursor and a little bit of vba coding. Not sure if this is what I want or not. Hope this helps a little bit I'll try it. Thanks. -Aaron |
#5
|
|||
|
|||
Query based off of another query?
Aaron wrote: I got it. I used crosstab queries, created simply using the "new" button in the query section of the database. I just put all of the data into one table, with a column for the code, cost, and # of users. The crosstab query allowed me to make unique rows for each code, and the function you can perform in the second column is an average of cost for one query, and an average of users for the other query. So, I have the query I want, but it's in 2 queries. C'est la vie. Oh, and it's really ugly, because there are a bunch of other columns (one for each entry) in each of the queries. But I don't need them, and they don't affect anything, so all is well. One day I should learn how to do this the right way. So that someone like you doesn't have to come along and fix my database for me later, that is. =) -Aaron |
Thread Tools | |
Display Modes | |
|
|