Total Dollars for Yes/No Check Boxes

[dgriffin]

I've got a Purchase Order table that contains the Vendor Number and the Total Dollars for that particular purchase. Then I have a Vendor table, related to the Purchase Order table by Vendor Number, which contains three yes/no check boxes indicating whether or not that particular vendor is WBE (Woman Business Enterprise), MBE (Minority) or DBE (Disabled).

I need to create a summary query (for a report) that will SUM the TOTAL dollars from the PO table for each WBE/MBE/DBE that has a check mark on the Vendor record. Baring in mind that any given vendor can have any combination of WBE/MBE/DBE checked. I also need a count for each of the WBE/MBE/DBE checks.

An attempt to use the query wizard yielded only the following error:

The wizard was unable to open your query in datasheet view, possibly because the table needed by your query is exclusively locked.

It isn't.

I think it was being asked to recurs itself. Anyway, what is the solution? And please don't tell me it's CR or some facsimile.

Thanks in advance,
Dan

 

[tlbroadbent]

Here is a solution using the IIF and Sum function in an Inner Join query.

Select
Sum(IIF(WBE,1,0)) As WBECnt,
Sum(IIF(WBE,TotalDollars,0)) As WBESum,
Sum(IIF(MBE,1,0)) As MBECnt,
Sum(IIF(MBE,TotalDollars,0)) As MBESum,
Sum(IIF(DBE,1,0)) As DBECnt,
Sum(IIF(DBE,TotalDollars,0)) As DBESum
From POTable Inner Join Vendors
On POTable.VendorNumber=Vendors.VendorNumber

Notes:[ol][li]IIF(WBE,1,0): When WBE is Yes then add 1 else add 0 to the count. Ditto for MBE and DBE.
[li]IIF(WBE,TotalDollars,0): When WBE is Yes then add TotalDollars else add 0 to the Sum. Ditto for MBE and DBE.
[li]Use Sum function to add the values.[/ol]Let me know if you encounter any problems. Hope the query works for you. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost