Separating data in a combo box into individual pieces

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box that contains three choices (i.e. Yes, No, Mabye). I need
to be able to create a new table based on each separate choice or I need to
be able to export it to Excel as three different items (columns) so I can
count how many of each choice was selected. So I need to be able to create a
Yes table, a No table and a Maybe table or I need to be able to export this
into a spreadsheet so I get three columns: Yes, No, Maybe so I can use the
CountA function to determine how many of each were selected.

I was hoping there might be a way to use a Query (Make Table Query?) to do
this but I can't figure out how to create a query that can simply split or
parse the three choices in the Combo Box and put them into three separate
tables (or three separate columns in a spreadsheet).

I hope this makes sense and thanks for any help you can provide
 
AMessyGuy said:
I have a combo box that contains three choices (i.e. Yes, No, Mabye). I need
to be able to create a new table based on each separate choice or I need to
be able to export it to Excel as three different items (columns) so I can
count how many of each choice was selected. So I need to be able to create a
Yes table, a No table and a Maybe table or I need to be able to export this
into a spreadsheet so I get three columns: Yes, No, Maybe so I can use the
CountA function to determine how many of each were selected.

I was hoping there might be a way to use a Query (Make Table Query?) to do
this but I can't figure out how to create a query that can simply split or
parse the three choices in the Combo Box and put them into three separate
tables (or three separate columns in a spreadsheet).
Click to expand...


I don't think that does make much sense. If all you want is
a count of each value, making new tables and sending it to
Excel is sure the long way around.

The various counts can easily be calculated in a single
query:

SELECT Count(IIf(choicefield = "Yes", 1, Null)) As YesCount,
Count(IIf(choicefield = "No", 1, Null)) As NoCount,
Count(IIf(choicefield = "Maybe", 1, Null)) As MaybeCount
FROM the table
 
Even simpler would be

SELECT ChoiceField, Count(ChoiceField) as CountThem
FROM YourTable
GROUP BY ChoiceField

That should return three rows of two columns
Yes with a count
No with a count
Maybe with a count
 
Thanks!!! That worked! I've never done any SQL code so I wasn't familiar
with it. It looks interesting and pretty versatile--and it reminds me of
just how much I still need to learn in order to really exploit the power of
Access.

One more question: If I want to do this for more than one field in the same
table is there a way to do that? I played with the SQL statements to try to
see if I could get it to work but I got various errors depending on how I
tried it.

When I repeated the code below the original and changed the names of the
field I got one type of error. I tried repeating the code by stringing
similar code next to the original code separated by a comma, then by a
semi-colon but got an error each way, too.
 
AMessyGuy said:
One more question: If I want to do this for more than one field in the same
table is there a way to do that? I played with the SQL statements to try to
see if I could get it to work but I got various errors depending on how I
tried it.

When I repeated the code below the original and changed the names of the
field I got one type of error. I tried repeating the code by stringing
similar code next to the original code separated by a comma, then by a
semi-colon but got an error each way, too.
Click to expand...


Need more details. At this point I don't event know what
the "same thing" is.

How about posting the SQL statement that works and
explaining what else you want it to do.
 
Back
Top