My first DB - how do I filter something out?

  • Thread starter Thread starter Sherry
  • Start date Start date
S

Sherry

I have a table imported from Excel. One field is customer type. Large,medium
and small. How do I create a report the shows the total number of small
customers and also large+medium customers?
 
I have a table imported from Excel. One field is customer type. Large,medium
and small. How do I create a report the shows the total number of small
customers and also large+medium customers?
Click to expand...

Create a Query based on your table with a calculated field created by
typing:

SizeCat: Switch([Size] = "Small", "Small", [Size] = "Medium" OR [Size]
= "Large", "Medium/Large", True, "Other/miscategorized")

Change the query to a Totals query and group by SizeCat to count the
records.
 
I'm sure this does exactly what I was looking for but could you give me more
of a walkthrough? I know how to create a query that displays all records
that say "medium" but I'm not sure what you mean when you say "Create a
Query based on your table with a calculated field." Where and how do I do
that?

thank you

John Vinson said:
I have a table imported from Excel. One field is customer type. Large,medium
and small. How do I create a report the shows the total number of small
customers and also large+medium customers?
Click to expand...

Create a Query based on your table with a calculated field created by
typing:

SizeCat: Switch([Size] = "Small", "Small", [Size] = "Medium" OR [Size]
= "Large", "Medium/Large", True, "Other/miscategorized")

Change the query to a Totals query and group by SizeCat to count the
records.
Click to expand...
 
I'm sure this does exactly what I was looking for but could you give me more
of a walkthrough? I know how to create a query that displays all records
that say "medium" but I'm not sure what you mean when you say "Create a
Query based on your table with a calculated field." Where and how do I do
that?

thank you
Click to expand...

Just type, or copy and paste,

SizeCat: Switch([Size] = "Small", "Small", [Size] = "Medium" OR [Size]
= "Large", "Medium/Large", True, "Other/miscategorized")

all on one line in a vacant Field cell in your query, changing the
[Size] to whatever the fieldname for your table might be. This field
will contain the word "Small" if your field is "Small"; "Medium/Large"
if it's medium or large; and "Other/Miscategorized" if your field
contains nothing or some word other than Small, Medium or Large.

You can now change the query to a Totals query by clicking on the
Greek Sigma icon (looks like a sideways M), and grouping by this new
SizeCat field.
 
Back
Top