Access 2007 query with null values

45 pts.
Tags:
Microsoft Access 2007
Microsoft Access queries
I am currently creating an Access 2007 database for calculating salesperson commissions. I have a table with 5 fields I'm working with: SalesRep, SOWRep, TerritoryRep, Period and Commission. I am trying to build a query that will calculate commission for a salesperson for each record where their name appears in one of the first three fields. Each time their name appears, they get the commission listed in the Commission field for the stated Period. I have managed to do this part successfully. My problem now is that I am creating a query that will sum all of their commissions by Period. I have run into a situation where sometimes a salesperson will not appear in one of the rep fields, resulting in NULL values in the previous queries. How can I create a final query that will sum correctly even if there are NULL values returned on the previous queries?

Software/Hardware used:
Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • stlawr
    This is the Expression I have so far: November: Avg((SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin1] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin2] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin3] WHERE [Period] Like "November*"))
    45 pointsBadges:
    report
  • philpl1jb
    Could you give us a sample of some data from the original file and the desired output. Make the data representative of the range of issues. tks Phil
    51,365 pointsBadges:
    report
  • coldrunner
    Another way is to use the Access query grid tool to create a new query. Add the fields you need in the report or output. Add a calculated field that references a field in the table that you cannot have Nulls in. (in an empty query grid column) Use the following in the caluclated field in the query grid: DollarsCalc: IIf(IsNull([Dollars]),0,[Dollars]) 1. This means a create a new field called DollarsCalc 2. Reference the real field Dollars in the IIF statement, which means Immediate IF 3. IsNull checks for Null in Dollars with true of false returned 4. IIF, if it is true moves zero in, else IIF keeps Dollars as is. 5. You can SUM the results in an Access sum query 6. IF you need SQL you generate it directly from the Access query grid - by choosing View (then down Arrow)/ SQL (after you load the fields in of course).
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following