Need help creating query

[BlackBearDude]

I need to take a table such as:

casenum type fee
222 elec 50.00
222 bldg 25.00
333 dway 75.00
333 elec 50.00
444 bldg 25.00

and turn it into:

casenum bldgfee elecfee dwayfee
222 25.00 50.00 0.00
333 25.00 0.00 75.00
444 25.00 0.00 0.00

I am feeling like a real idiot by not being able to come up with the solution, and I know it's going to be obvious.

Appreciate any help you guys & gals can provide.

Thanks

Patrick

 

[hindsjl]

Patrick -

There are a couple of ways to do this, but the simpler one is a Crosstab query. I will give it to you in Design view, as I can't remember the words Access puts in the SQL window for Crosstabs:

Create a query with all three fields (Casenum, Type, Fee). From the View menu, show Totals. Set Casenum and Type as "Group By", and Fee as "Sum". Then from the Query menu, select Crosstab. In the new Crosstab row, Set Casenum as a "Row Heading" field, Type as a "Column Heading" field, and Fee as the "Data" field. Run the query and you'll have your desired output.

John

 

[BlackBearDude]

John,

Thanks! The crosstab does basically what I need, except that columns aren't created where there was no value (for a particular casenum). My need beyond this is to find a way (in a report) to substitute the value $0.00 when a column for a permit type is not reflected in the crosstab.

Unfortunately, using the statement:

IIF(Not IsError([FieldName]), [FieldName], 0.00)

does not work ...

any ideas??

 

[hindsjl]

Patrick -

1. When you say that "columns aren't created", do you mean that a column is created with no amt, or that no _row_ is created for that casenum?

2. Using your example above, are we talking about a source table like this

casenum type fee
222 elec 50.00
222 bldg 25.00
333 dway 75.00
333 elec 50.00
444 bldg 25.00
555 ROOF 0.00,

where you're concerned about the treatment of the ROOF data, or is there simply no casenum=555 row in the table? In the latter case, we have to look elsewhere for a list of casenums that "need" reporting, whether they have activity this period or not. A different problem then.

John

 

[BlackBearDude]

John,

Essentially, each casenum can have one or more permit fees. When a specific permit fee is not present, I need to report 0.00 in the appropriate location on the report form.

Using the crosstab, when a permit fee doesn't exist for a casenum, no column is created in the result set. I need to find a way to determine that the field does not exist and "substitute" 0.00 for the fee amount. For example, in the sample data casenum 222 has no permit type of "dway", therefore no column labeled "dway". I need a way to make this determination in the report.

The row you mentioned "ROOF" with a value of 0.00 will never occur. The table that holds the permit fee data only shows "real" fees.

I hope this clears things up.

Again, thanks for the help.

Any thoughts?

 

[hindsjl]

Patrick -

I haven't found a way to get nz() or any other such logic to work inside a crosstab query. If Access doesn't find any value for a given row-column "cell", it just seems to want to put a null there, and won't let me replace it with zero.

There is an alternative approach that works only if you have a stable and relatively short list of Type values to report on. Rather than have Access build the crosstab for you, you create an ordinary query that forces the totals into separate columns. E.g.,

select casenum,
sum(iif(type='bldg',fee,0)) as bldgfee,
sum(iif(type='elec',fee,0)) as elecfee,
sum(iif(type='dway',fee,0)) as dwayfee
group by casenum

This approach should give you the zeroes you want.

Hope this helps.
John

 

[BlackBearDude]

John,

It is not doing the trick. I thought of this earlier today and couldn't get it to work as expected.

 

[hindsjl]

Patrick -

Well, I'm about to surrender. But there is one last possibility. The formula you suggested earlier

IIF(Not IsError([FieldName]), [FieldName], 0.00)

should be written as nz([FieldName],0)

This formula as written replaces nulls with zeroes. It works in cases where a table has explicit Nulls saved in it, or where you've got an outer join going and the "inner side" table has no matching row. It may help you in your "manual crosstab" approach.

Regards,
John

 

[BlackBearDude]

John,

Thanks a bunch for your help.

The SQL statement you presented earlier certainly should work. I'm not quite sure why it isn't. I've done this many times when I used to program in Foxpro/VFP. Unfortunately, I can't control what other people use for "data management".

I'll keep you posted with whatever I wind up doing that accomplishes what I need.

Thanks again!

Patrick

 

[BlackBearDude]

FINALLY worked

SELECT table1.[casenum],sum(iif(type='bldg',fee,0)) as bldgfee,sum(iif(type='elec',fee,0)) as elecfee, sum(iif(type='driveway',fee,0)) as drivewayfee, sum(iif(type='roof',fee,0)) as rooffee
FROM Table1 INNER JOIN Table2 ON Table1.casenum = Table2.casenum
WHERE (((Table1.casenum)="222&quot)
group by table1.casenum