Query based off of another query?

[Aaron]

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

 

[Aaron]

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.

 

[Todd]

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

 

[Aaron]

I still don't quite understand the whole project, but I'll start with the
easy part:


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

 

[Aaron]

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