quotient inserted into table

  • Thread starter Thread starter Chase
  • Start date Start date
C

Chase

My Query is to be expressed so that the data in one field
to be divided by data in another field and expressed as a
quotient in another field in my table. The SQL statement
is:

UPDATE DISTINCTROW CampbellSales_Yr2003_13Dec03,
CampbellSales_Yr2003_13Dec03 AS
CampbellSales_Yr2003_13Dec03_1,
CampbellSales_Yr2003_13Dec03 AS
CampbellSales_Yr2003_13Dec03_2 SET
CampbellSales_Yr2003_13Dec03.[$/sqft] = [saleprice]/
[sqft];

where the query is set up as below:

(column 1) (column 2) (column 3)
field: saleprice sqft
table: table name table name table name
update to [saleprice]/[sqft]

When the query is 'run', an error message is displayed:

Ambiguous field reference '[saleprice]'

What corrections need to be made to accomplish my
objective to display the quotient in the table?

Thanks.
 
My Query is to be expressed so that the data in one field
to be divided by data in another field and expressed as a
quotient in another field in my table. The SQL statement
is:

UPDATE DISTINCTROW CampbellSales_Yr2003_13Dec03,
CampbellSales_Yr2003_13Dec03 AS
CampbellSales_Yr2003_13Dec03_1,
CampbellSales_Yr2003_13Dec03 AS
CampbellSales_Yr2003_13Dec03_2 SET
CampbellSales_Yr2003_13Dec03.[$/sqft] = [saleprice]/
[sqft];

where the query is set up as below:

(column 1) (column 2) (column 3)
field: saleprice sqft
table: table name table name table name
update to [saleprice]/[sqft]

When the query is 'run', an error message is displayed:

Ambiguous field reference '[saleprice]'

What corrections need to be made to accomplish my
objective to display the quotient in the table?
Click to expand...

Several concerns here:

You're including the horribly named table CampbellSales_Yr2003_13Dec03
in your query THREE TIMES, with no join line. This will give you a
Cartesian join of the query to itself - if the table has 1000 records,
you'll get 1,000,000,000 rows in the query with every possible
combination. Surely that's not what you want!

Secondly, a quotient should almost never be stored in a table in any
case. As derived data, it can best be calculated in a Query when it's
needed. Storing it only makes sense if you want the price per square
foot to be allowed to change to some value other than the quotient.

Third, using special characters such as / and $ in fieldnames is risky
and should be avoided.

To directly answer the question though - since you have three fields
named Saleprice (that in CampbellSales_Yr2003_13Dec03,
CampbellSales_Yr2003_13Dec03_1, and CampbellSales_Yr2003_13Dec03_2)
you must specify which one you mean in the query by explicitly
qualifying the fieldname, e.g. CampbellSales_Yr2003_13Dec03.Saleprice.
 
John,

Thanks for the help. I will try your suggestions. My
reply to your message is below.
-----Original Message-----
My Query is to be expressed so that the data in one field
to be divided by data in another field and expressed as a
quotient in another field in my table. The SQL statement
is:

UPDATE DISTINCTROW CampbellSales_Yr2003_13Dec03,
CampbellSales_Yr2003_13Dec03 AS
CampbellSales_Yr2003_13Dec03_1,
CampbellSales_Yr2003_13Dec03 AS
CampbellSales_Yr2003_13Dec03_2 SET
CampbellSales_Yr2003_13Dec03.[$/sqft] = [saleprice]/
[sqft];

where the query is set up as below:

(column 1) (column 2) (column 3)
field: saleprice sqft
table: table name table name table name
update to [saleprice]/[sqft]

When the query is 'run', an error message is displayed:

Ambiguous field reference '[saleprice]'

What corrections need to be made to accomplish my
objective to display the quotient in the table?
Click to expand...

Several concerns here:

You're including the horribly named table CampbellSales_Yr2003_13Dec03
in your query THREE TIMES, with no join line. This will give you a
Cartesian join of the query to itself - if the table has 1000 records,
you'll get 1,000,000,000 rows in the query with every possible
combination. Surely that's not what you want!
Click to expand...

No, that is not what I want. The Microsoft Access
software automatically the name in the table line when I
entered the field name.
Secondly, a quotient should almost never be stored in a table in any
case. As derived data, it can best be calculated in a Query when it's
needed. Storing it only makes sense if you want the price per square
foot to be allowed to change to some value other than the quotient.
Click to expand...
It is of no use unless I can display the results in a
table as the table is to be used in my presentation with
my clients.
Third, using special characters such as / and $ in fieldnames is risky
and should be avoided.
Click to expand...

Same as above. I could recode and use different field
characters but would want the characters '/' and '$' to
display in the table.
To directly answer the question though - since you have three fields
named Saleprice (that in CampbellSales_Yr2003_13Dec03,
CampbellSales_Yr2003_13Dec03_1, and CampbellSales_Yr2003_13Dec03_2)
you must specify which one you mean in the query by explicitly
qualifying the fieldname, e.g.
Click to expand...
CampbellSales_Yr2003_13Dec03.Saleprice.

Once I get the query to work, can I not save it to be
used for all future downloading of data?

Thanks. I will join your chat room.
 
It is of no use unless I can display the results in a
table as the table is to be used in my presentation with
my clients.


Same as above. I could recode and use different field
characters but would want the characters '/' and '$' to
display in the table.
Click to expand...

You're assuming that you must have your data in a table, with labels
in the fieldnames, in order to display it to your clients.

Both these assumptions are FLAT WRONG.

Table datasheets are *very* limited, and should generally never be
used for data display and presentation!

You can create a Form (for onscreen use) or Report (for printing),
based on a Query; on this Form you can put whatever field captions you
wish.

A lot of your difficulty stems from this apparent (and very common)
misconception, it appears to me!
 
Back
Top