Changing info in a existing table/form

  • Thread starter Thread starter Joanne
  • Start date Start date
J

Joanne

I have an existing database that I need to add a column to one of my tables
and move 2 product types in to because they are a secondary product. When I
delete them from the one column and set up a new column then all of my old
records in the form that we enter information in and the tables that contain
that info are messed up because the product is gone. For example:
Product Types:
Cheese
Sausage
Pepperoni
Green Pepper
1-Sausage
2-Pepperoni

1-Sausage & 2-Pepperoni can't show up in the report I have. I need the
report to say just Sausage or Pepperoni. So, I wanted to add a secondary
Product Type and have 1-Sausage & 2-Pepperoni in that category instead.
Then, I would pick Sausage from Product Type and then if need be 1-Sausage in
the Secondary Product Type or leave it blank.

I hope this makes sense. If there is a way to do it without messing up all
the old records please let me know.

Thanks!
 
Base your report on a query that doesn't include the two product types you
want omitted.

Damon
 
They still need to be included on the report. They just need to say
"Pepperoni" and "Sausage" on the report instead of "1-Sausage" and
"2-Pepperoni". If I create a query without those 2 product types in it then
they won't show up, right? See each product can have multiple different
records with different info in each record. So, I have a report that I can
type in the date and see what was all made-the report goes to the customer
and I don't want it to say 1- or 2- before the product type for them but for
me it is necessary.
 
They still need to be included on the report. They just need to say
"Pepperoni" and "Sausage" on the report instead of "1-Sausage" and
"2-Pepperoni". If I create a query without those 2 product types in it then
they won't show up, right? See each product can have multiple different
records with different info in each record. So, I have a report that I can
type in the date and see what was all made-the report goes to the customer
and I don't want it to say 1- or 2- before the product type for them but for
me it is necessary.
Click to expand...

No need to add secondary fields.

In the report use an unbound control to show the Product types.
Set it's control source to:

=IIf([ProductType] Like "*Sausage","Sausage",[ProductType])

Make sure the name of this control is not ProductType.
 
I think that will work, but I need the expression to contain both the sausage
& pepperoni? Also, if I delete the Product Type text box - Then when I go to
view my report it ask for Product Type? I left it blank and hit okay and the
report still came up - How can I get it not to do that?

fredg said:
They still need to be included on the report. They just need to say
"Pepperoni" and "Sausage" on the report instead of "1-Sausage" and
"2-Pepperoni". If I create a query without those 2 product types in it then
they won't show up, right? See each product can have multiple different
records with different info in each record. So, I have a report that I can
type in the date and see what was all made-the report goes to the customer
and I don't want it to say 1- or 2- before the product type for them but for
me it is necessary.
Click to expand...

No need to add secondary fields.

In the report use an unbound control to show the Product types.
Set it's control source to:

=IIf([ProductType] Like "*Sausage","Sausage",[ProductType])

Make sure the name of this control is not ProductType.
Click to expand...
 
I think that will work, but I need the expression to contain both the sausage
& pepperoni? Also, if I delete the Product Type text box - Then when I go to
view my report it ask for Product Type? I left it blank and hit okay and the
report still came up - How can I get it not to do that?

fredg said:
They still need to be included on the report. They just need to say
"Pepperoni" and "Sausage" on the report instead of "1-Sausage" and
"2-Pepperoni". If I create a query without those 2 product types in it then
they won't show up, right? See each product can have multiple different
records with different info in each record. So, I have a report that I can
type in the date and see what was all made-the report goes to the customer
and I don't want it to say 1- or 2- before the product type for them but for
me it is necessary.

:

Base your report on a query that doesn't include the two product types you
want omitted.

Damon

I have an existing database that I need to add a column to one of my tables
and move 2 product types in to because they are a secondary product. When
I
delete them from the one column and set up a new column then all of my old
records in the form that we enter information in and the tables that
contain
that info are messed up because the product is gone. For example:
Product Types:
Cheese
Sausage
Pepperoni
Green Pepper
1-Sausage
2-Pepperoni

1-Sausage & 2-Pepperoni can't show up in the report I have. I need the
report to say just Sausage or Pepperoni. So, I wanted to add a secondary
Product Type and have 1-Sausage & 2-Pepperoni in that category instead.
Then, I would pick Sausage from Product Type and then if need be 1-Sausage
in
the Secondary Product Type or leave it blank.

I hope this makes sense. If there is a way to do it without messing up
all
the old records please let me know.

Thanks!
Click to expand...

No need to add secondary fields.

In the report use an unbound control to show the Product types.
Set it's control source to:

=IIf([ProductType] Like "*Sausage","Sausage",[ProductType])

Make sure the name of this control is not ProductType.
Click to expand...
Click to expand...

You can nest additional criteria:

=IIf([ProductType] Like "*Sausage","Sausage",IIf([ProductType] Like
"*Pepperoni","Pepperoni",[ProductType]))

I didn't tell you to delete the [ProductType] field from the report.
I said to make sure the name of this control is not "ProductType".
Include the [ProductType] field in the report's Recordsource.

Then, if you still get prompted for that field, include that field in
the report detail section but make it Not Visible.
 
I couldn't remember how to make the text box invisible. I figured it out.
Thank you so much - it worked.

fredg said:
I think that will work, but I need the expression to contain both the sausage
& pepperoni? Also, if I delete the Product Type text box - Then when I go to
view my report it ask for Product Type? I left it blank and hit okay and the
report still came up - How can I get it not to do that?

fredg said:
On Wed, 24 Jun 2009 08:05:02 -0700, Joanne wrote:

They still need to be included on the report. They just need to say
"Pepperoni" and "Sausage" on the report instead of "1-Sausage" and
"2-Pepperoni". If I create a query without those 2 product types in it then
they won't show up, right? See each product can have multiple different
records with different info in each record. So, I have a report that I can
type in the date and see what was all made-the report goes to the customer
and I don't want it to say 1- or 2- before the product type for them but for
me it is necessary.

:

Base your report on a query that doesn't include the two product types you
want omitted.

Damon

I have an existing database that I need to add a column to one of my tables
and move 2 product types in to because they are a secondary product. When
I
delete them from the one column and set up a new column then all of my old
records in the form that we enter information in and the tables that
contain
that info are messed up because the product is gone. For example:
Product Types:
Cheese
Sausage
Pepperoni
Green Pepper
1-Sausage
2-Pepperoni

1-Sausage & 2-Pepperoni can't show up in the report I have. I need the
report to say just Sausage or Pepperoni. So, I wanted to add a secondary
Product Type and have 1-Sausage & 2-Pepperoni in that category instead.
Then, I would pick Sausage from Product Type and then if need be 1-Sausage
in
the Secondary Product Type or leave it blank.

I hope this makes sense. If there is a way to do it without messing up
all
the old records please let me know.

Thanks!



No need to add secondary fields.

In the report use an unbound control to show the Product types.
Set it's control source to:

=IIf([ProductType] Like "*Sausage","Sausage",[ProductType])

Make sure the name of this control is not ProductType.
Click to expand...
Click to expand...

You can nest additional criteria:

=IIf([ProductType] Like "*Sausage","Sausage",IIf([ProductType] Like
"*Pepperoni","Pepperoni",[ProductType]))

I didn't tell you to delete the [ProductType] field from the report.
I said to make sure the name of this control is not "ProductType".
Include the [ProductType] field in the report's Recordsource.

Then, if you still get prompted for that field, include that field in
the report detail section but make it Not Visible.
Click to expand...
 
Back
Top