Count question

[aggiemarine07]

I have a probably very silly question but I cant seem to find an answer anywhere on the forum.

Is it possible to count the number of instances within a ROW instead of a column?

For example



ID | Name | Location | Name | Location
1 Smith BAS Johnson BAS
2 Jones Hosp Williams Hosp

Using the above information, I would like to use a formula/VBA to count the total times BAS appears within a row in a specified table (i.e. BAS = 2 or Hosp = 2). Is that possible? Thanks.

[ItsMe]

It is not exactly what you are looking for but here is an example of enumerating each Column of a recordset by its Field index and name.
https://www.accessforums.net/queries...tml#post224203

There are other examples out there but, that was the first one that I found that was simple and straight forward.

[ranman256]

you could run query with sub queries... Q1 query the 1st ID,NAME/LOCATION,
a 2nd qry to get ID, NAME2, LOCATION2,
a 3rd qry that is a UNION qry of Q1 & Q2,
and a 4th qry does the count.
Simply run Q4, (this will in turn run, q3, which runs Q1 & 2.)

[John_G]

Hi -

Are there only two columns to look at, or are there more?

If there are only two, and you are looking for rows where the counts are = 2, then just look for Location1 = location2 and location1 = "BAS".

If you have more than 2 columns, then you should look at redesigning your data - it is not properly normalized.