Count fields

[RichP]

Hi there,
I work a lot with excel, but I'm quite new to access, and am stuck for ideas on how to do the following:
I have a table listing the denominations of world currencies: first column is country, then currency name, currency code, then the next 14 columns contain the denominations for that country. For the US, the entries are USA, US Dollar, USD$, 1, 2, 5, 10, 20, 50, 100, and then a number of blanks.
I have a report based on a query that allows me to enter the name of the country, or the currency, or the code, and it will show me all the details listed above. So far so good. What I would like to do is ask the question: What countries have 8 denominations, or 8 denominations or more, or 8 denominations or fewer - the 8 is any number between 1-14.
How can I a) prepare the query to count the number of fields that are filled in for each record, and b) take into account that the first 3 fields don't count?

Many thanks,
RichP

 

[Corticus]

Hi RichP,

You could use a crosstab query. If you have your column heading be the denominations, then you could have your value be 1, and then the total row would be a count of the different type of denominations. If you've never used a crosstab query, just use the wizard, it's works well enough.

Onve you have this query built, you can join it to whatever other query your report looks at on currencyID, and then pull the total field.