Creating Table or CTQuery w/multiple criteria 1

[Boncher]

I have a table that contains four yes/no fields to designate four different program types (NBUG, NBGRAD, NNBUG and NNBGRAD). Some programs have more than one type, so the XYZ Program may have a yes for NBUG and NBGRAD. I need to create a query (hopefully a cross tab) or a table that has the four program types as headings. The data in the columns is pulled depending on the program type, so if program type is NBUG then the value is UG$; if program type is NBGRAD then the value is GRAD$; if program type is NBUG and NBGRAD, then UG$ has to go under NBUG and GRAD$ has to go under NBGRAD. Also, the programs are grouped by state and most states have more than one program. I need to sum all of the UG$ and GRAD$ by program type resulting in one row per state.

I don't think this should that hard, but I've hit a brick wall. Thanks for any help!

 

[BoxHead]

Boncher,

Open a new query in design view, select the table and drag your four fields into the grid.

In the top Field line of the NBUG column, type:

  myNBUG: IIF([NBUG] = -1, "UG$", "")

Replace the NBGRAD Field Name with

  myNBGRAD: IIF([NBGRAD] = -1, "GRAD$", "")

What these Inline If Statements do is check the value of the field to see if it meets the argument ( = -1). If that is true, it returns the string "UG$" or "GRAD$" inplace of the -1. If the test is false, it replaces the value with "".

You can't actually have the field name be the column name without causing a circular reference. That's why the column names in the query are myNBUG and myNBGRAD.

As you add these columns to your query, you can use them for sorting and grouping as necessary.

Hope this gets you on the road.

John

Use what you have,
Learn what you can,
Create what you need.

 

[Boncher]

It worked! I had to change the else part of the statement to a zero instead of a blank and sum so that states with more than one program would show all funds for all categories in one record. I had tried quite a few IIf statements that were much more involved but didn't work. I knew it had to be much more easier than I was finding it to be. You've made my day. Thank you so much.