Adding a field to a table

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I have a tabel which contains a field "Product Type"
which is populated with a 4 digit number.

An example of this would be 7001-7999. I have had a
request to create a summarized "Product Type" field,
whereby any record containing the numbers 7001-7999 would
have 7000 in the new field.

What is the best way to go about this?

Any help would be greatly appreciated

Thanks
Bill
 
Assuming that this 'Product Type Number' is for display purposes only, I
would not add a field to the table at all, but calculate the product type
number as required in queries or expressions in forms and reports. For
example, in a query ...

SELECT tblTest.ProductNumber, ([ProductNumber]\1000)*1000 AS
ProductTypeNumber
FROM tblTest;

(The "\" character in the above example is the integer division operator.)
 
The product type number is for inclusion n a list for a
combo box. The user selects the product type from the
list, and executes a report that is based on a query.
The problem is that they want the report to summarize
like product types, while still having the ability to
report at the detail level
-----Original Message-----
Assuming that this 'Product Type Number' is for display purposes only, I
would not add a field to the table at all, but calculate the product type
number as required in queries or expressions in forms and reports. For
example, in a query ...

SELECT tblTest.ProductNumber, ([ProductNumber]\1000) *1000 AS
ProductTypeNumber
FROM tblTest;

(The "\" character in the above example is the integer division operator.)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

I have a tabel which contains a field "Product Type"
which is populated with a 4 digit number.

An example of this would be 7001-7999. I have had a
request to create a summarized "Product Type" field,
whereby any record containing the numbers 7001-7999 would
have 7000 in the new field.

What is the best way to go about this?

Any help would be greatly appreciated

Thanks
Bill
Click to expand...


.
Click to expand...
 
The product type number is for inclusion n a list for a
combo box. The user selects the product type from the
list, and executes a report that is based on a query.
The problem is that they want the report to summarize
like product types, while still having the ability to
report at the detail level
-----Original Message-----
Assuming that this 'Product Type Number' is for display purposes only, I
would not add a field to the table at all, but calculate the product type
number as required in queries or expressions in forms and reports. For
example, in a query ...

SELECT tblTest.ProductNumber, ([ProductNumber]\1000) *1000 AS
ProductTypeNumber
FROM tblTest;

(The "\" character in the above example is the integer division operator.)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

I have a tabel which contains a field "Product Type"
which is populated with a 4 digit number.

An example of this would be 7001-7999. I have had a
request to create a summarized "Product Type" field,
whereby any record containing the numbers 7001-7999 would
have 7000 in the new field.

What is the best way to go about this?

Any help would be greatly appreciated

Thanks
Bill
Click to expand...


.
Click to expand...
 
Then the query (used as the row source for the combo box) with a similar
expression in the query the report is based on, should solve the problem as
far as I can see.

For example, given a query like the example I posted, the report could group
on the calculated ProductTypeNumber and list individual ProductNumbers in
each group.

Or am I missing something?

If you really want to, you could create an empty field in the table and then
use the expression in an update query to put the values into that field, but
storing a calculated value like that breaks one of the fundamental rules of
relational database design. Sometimes, there are legitimate reasons to break
the rules. I don't know the details of your data and your application, so I
can't say for sure if this is one of those times, but it doesn't look like
it based on the information available so far.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

Bill said:
The product type number is for inclusion n a list for a
combo box. The user selects the product type from the
list, and executes a report that is based on a query.
The problem is that they want the report to summarize
like product types, while still having the ability to
report at the detail level
-----Original Message-----
Assuming that this 'Product Type Number' is for display purposes only, I
would not add a field to the table at all, but calculate the product type
number as required in queries or expressions in forms and reports. For
example, in a query ...

SELECT tblTest.ProductNumber, ([ProductNumber]\1000) *1000 AS
ProductTypeNumber
FROM tblTest;

(The "\" character in the above example is the integer division operator.)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

I have a tabel which contains a field "Product Type"
which is populated with a 4 digit number.

An example of this would be 7001-7999. I have had a
request to create a summarized "Product Type" field,
whereby any record containing the numbers 7001-7999 would
have 7000 in the new field.

What is the best way to go about this?

Any help would be greatly appreciated

Thanks
Bill
Click to expand...


.
Click to expand...
Click to expand...
 
In your report query add a new field.
left([product type],1)&"000"
then group by this new field in your report.

Jim
 
Back
Top