Separating data in a combo box into individual pieces

A

AMessyGuy

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
 
M

Marshall Barton

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
 
J

John Spencer (MVP)

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
 
A

AMessyGuy

One question for both John and Marsh: Where do I put the commands you
mentioned? Do they go in the query in the Criteria section of the item I
need to count or is this code going someplace else?

--
Sometimes I wonder if men and women really suit each other. Perhaps they
should live next door and just visit now and then.


John Spencer (MVP) said:
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
Click to expand...
 
M

Marshall Barton

AMessyGuy said:
One question for both John and Marsh: Where do I put the commands you
mentioned? Do they go in the query in the Criteria section of the item I
need to count or is this code going someplace else?
Click to expand...


Both of us provided SQL statement, which are queries. You
would create a new query (don't bother selecting a table),
switch it to SQL view, and Paste either one to replace
whatever Access put there automatically. Then, just switch
the query to sheet view to see the data it selected.

You can export the query as easily as a table, so there's no
need to use an intermediate storage mechanism.
 
A

AMessyGuy

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.
 
M

Marshall Barton

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.
 
Top