Need a SQL Query for an Access database

[kevinj]

I have a table named Attendees that includes a field named position and a field named level. In the position field, there are letters that represent the attendees position. In the level field there are numbers that represent the grade level.

Examples of each:

Level Field

1,2,3

Position Field

B,O,P

I need a SQL query that will allow me to count the number of attendees that checked 1 for level AND B for position.

Any help would be greatly appreciated.

[aspdaddy]

This should do it:
select count(*)
from attendees
where(level like '%1%' and position like '%B%')

[kevinj]

It gave me a total of zero. When I removed the %s it gave me a count of those that had an exact Level of 1 and Position of B. I need a count of any record that contains a 1 and a B. Doesn't look like the %s work. Any other thoughts.

Thanks for your help!

[txbakers]

Is the level and integer field or a text field?

the code above will work if the level is a text field.

If it's an integer it would look like this:

select count(*)
from attendees
where(level = 1 and position like '%B%')

[aspdaddy]

Sorry I thought you had raw text in one field like "1,2,3" :)

[kevinj]

both the Level and Position fields are text fields not integers. The data is raw text, just like,

1,2,3

or

B,A,P

[aspdaddy]

Strange... well seen as its access this should work :)

select count(*)
from attendees
where instr(level, '1') and instr(position, 'B')

[kevinj]

That works perfect. One last question. What would I need to do to get a count of the people that entered a B for position but left the level field blank? I tried this:

select count(*)
from attendees
where instr(level, '') and instr(position, 'J')

but the number it came up with was definitely not correct

[kevinj]

I meant this:

select count(*)
from attendees
where instr(level, '') and instr(position, 'B')

[kevinj]

Never mind. I figured it out:

select count(*)
from attendees
where (Isnull(level) and instr(position, 'B'))

Thanks ASPdaddy and TX for the help!

Kevin

[txbakers]

That's funny though as to why the %1% didn't work.

If the field really is "1,2,3", then %1% should return a true for that data row.

But, I'm glad it's working for you.