how to make a word idenifications show up as numbers

[Guest]

Well, I have a couple of problems.
I have a main table for what i need. i have a column labeled Product Line.
In this column it shows which of three product lines was made by a certain
date. i need to find a way to make a form to just show the total number of
products in the product line column within a text box. It needs to be the
total number of products in the product line column which is all in text not
number. To put im simpler words, i need to display how much words was
entered in this column. so that is my first problem.

My second problem is i need to get the total number of each product within
the product line column to add to a total number in a box on the form. For
example if the three products where; apples, oranges and shoes. I would need
to see how much times apples was entered and display it; how much times
oranges was entered and display it; and etc.

Please i need some help and i how i presented my problems in a way someone
can understand. thank you

 

[Ron2006]

Develop the following query



field1 = your control field
field2 = iif(instr(1,[tablename].[Product Line],"product one
name")>0,1,0)
field3 = iif(instr(1,[tablename].[Product Line],"product two
name")>0,1,0)
field4 = iif(instr(1,[tablename].[Product Line],"product three
name")>0,1,0)
field5 = [field2]+[field3]+[field4]



Field 2 is count of product 1 in field
Field 3 is count of product 2 in field
field 4 is count of product 3 in field
Field 5 is total number of products.

I think you can take it from there

 

[Guest]

If I understand you correctly the first is a simple count of the number of
rows where
the Product line column contains data, so the ControlSource for a text box
would be:

= DCount("*", "[YourTable]","Not IsNull([Product Line])")

For the individual counts:

= DCount("*", "[YourTable]","[Product Line] = ""Apples""")
= DCount("*", "[YourTable]","[Product Line] = ""Oranges""")
= DCount("*", "[YourTable]","[Product Line] = ""Shoes""")

This is fine for a fixed range of named products. If the products vary,
however, a continuous form based on a query such as follows would handle any
number of products:

SELECT [Product Line], COUNT(*) AS ProductCount
FROM YourTable
WHERE [Product Line] IS NOT NULL
GROUP BY [Product Line];

The total count could be displayed in a text box in the form's header or
footer using the DCount function as in the first example above.

Ken Sheridan
Stafford, England

 

[Ron2006]

Crisyina

You have now received two solutions for your problem based on out
GUESSES at what the data is like.

The question and main difference between the solutions is what values
are in that product line field.

The second solution assums that there is only one product line name in
the field at a time. If that is true then that solution is best.

From my reading I got the impression that the field could look like the

following
apples
apples, oranges
oranges, apples
oranges

I got the impression that more than one productline was in the field at
a time. If this is true than the second solution will not work and
something like mine will need to be done.

more explicit examples of the data will help with questions in the
future.

 

[Guest]

I sincerely hope the table isn't like that as it would not be in First Normal
form! The appropriate advice would be to go back to square one and normalize
it.

Nevertheless it could still be done in a form with unbound controls of course:

= DCount("*", "[YourTable]","[Product Line] Like ""*Apples*""")

and so on.

Ken Sheridan
Stafford, England

 

[Ron2006]

I agree.

Good luck Crisyina

Ron

 

[lkk]

I sincerely hope the table isn't like that as it would not be in First
Normal
form! The appropriate advice would be to go back to square one and
normalize
it.

Nevertheless it could still be done in a form with unbound controls of
course:

= DCount("*", "[YourTable]","[Product Line] Like ""*Apples*""")

and so on.

Ken Sheridan
Stafford, England

Crisyina

You have now received two solutions for your problem based on out
GUESSES at what the data is like.

The question and main difference between the solutions is what values
are in that product line field.

The second solution assums that there is only one product line name in
the field at a time. If that is true then that solution is best.

following
apples
apples, oranges
oranges, apples
oranges

I got the impression that more than one productline was in the field at
a time. If this is true than the second solution will not work and
something like mine will need to be done.

more explicit examples of the data will help with questions in the
future.