Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 37 of 37

Thread: Solved: SQL help

  1. #21
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    One thing that I don't understand is where the 2nd Amount comes from?

  2. #22
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    What do you mean the first value?

    The commission amount from the first query is the commission made per request or sale. The second query would be the total commission payable when that customer pays an invoice in full.
    Someday I'll understand everything...
    Even then...I'll still pretend I'm a beginner.

  3. #23
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    The first Customer of Allen has 687.59, 19.1 and 7.64
    whereas the Commission report only has 19.1 and 7.64 = 26.74

  4. #24
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    Well the first amount is expired. EndDate has come and gone and therefore they no longer get that commission.
    Someday I'll understand everything...
    Even then...I'll still pretend I'm a beginner.

  5. #25
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK.

  6. #26
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    Quote Originally Posted by OBP
    One thing that I don't understand is where the 2nd Amount comes from?
    Sorry I missed this one.

    Basically if sales person makes a sell we run an invoice to setup the equipment. Then the following month we send a monthly invoice(recurring for subscription).

    They get 1/2 when the setup is paid and 1/2 when the first monthly is paid. So if a total commission is 100, they get 50 when after the first invoice and 50 when the subscription invoice is paid... there are various rules to that based on what plan year we work in.

    Also if it is for a prior plan then they were getting a residual on the customer. $100 sell gets a $1 commission, that starts after 36 months. So.. I've had to recalculate because I realized as I sent that spreadsheet that for a 36 month period the startDate Jan 1-2009 would actually have an end date of Dec 1-2011 as Jan 1-2012 would actually be 37 months.
    Someday I'll understand everything...
    Even then...I'll still pretend I'm a beginner.

  7. #27
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Sorry, I just can't see where the QRY_COMMISSIONS_REPORT_ALL has the values shown in your Excel sheet, for Allen I can see
    19.10, but not 7.64
    13.19, but not 5.28
    10.29, but not 4.12
    279.9 but not 3.11

    Help!

  8. #28
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    EDITED::: Sorry looked at the wrong qry.

    The 7.64 won't show up in the Commission Report until about 2012. The S2009 plan is 5% for 36 months. After 36 months it switches to 1%. Sorry I'm not an Excel guru quite yet and my counta function didn't quite catch that. All the commissions with a start date later than Now() aren't applied.
    Last edited by Imdabaum; 02-08-2010 at 08:32 AM.
    Someday I'll understand everything...
    Even then...I'll still pretend I'm a beginner.

  9. #29
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    So I've never had this problem before I've always been able to edit a recordset from the queries I make. I've looked up the problem, there are no groupings, Sums, Unions, or other aggregate functions to force it into read-only.

    The only thing left is to assume that I am performing an illegal join or something. AllenBrowne mentions that it can happen if joins are going the wrong direction. Can anyone ellaborate on that?

    SELECT COMMISSIONS.ID, COMMISSIONS.VendorID,
    COMMISSIONS.CommissionEarned, COMMISSIONS.InvoiceNBR,
    COMMISSIONS.InvoiceAMT, COMMISSIONS.CommissionType,
    COMMISSIONS.Date_Paid_In_Full, COMMISSIONS.PaymentMonth
    FROM TBL_COMMISSIONS_Earned AS COMMISSIONS INNER JOIN
    TBL_INVOICES ON COMMISSIONS.InvoiceNBR =
    TBL_INVOICES.INVOICE_NBR;

    --I think I didn't setup the primary key on my Invoice table. This is going to be ugly trying to switch all these records to the InvoiceID instead of the InvoiceNBR. Does anyone know whether this is even going to help in the long run?
    Last edited by Imdabaum; 02-10-2010 at 03:48 PM.
    Someday I'll understand everything...
    Even then...I'll still pretend I'm a beginner.

  10. #30
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Why do you want to edit the Recordset, I thought it was for a Report?
    It is better to have a Primary Key Linking the tables. I just wish I could work out what you want it to do so that I could try it myself.

  11. #31
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    Quote Originally Posted by OBP
    Why do you want to edit the Recordset, I thought it was for a Report?
    It is better to have a Primary Key Linking the tables. I just wish I could work out what you want it to do so that I could try it myself.
    Well I have the SQL for the report that I think I've gotten working. But as I got the report working I realized that the process I had made to insert commissions was adding commissions where they didn't belong.

    Then I went through testing everything else and realized that one of the subforms didn't allow me to edit them. Subform showing a commissions history for each salesperson, I can't mark them as paid off, so they're going to constantly show up in the report.

    So in short. I worked through the report. And now I've discovered another problem with my query for the subform.
    Someday I'll understand everything...
    Even then...I'll still pretend I'm a beginner.

  12. #32
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Why does your subform need a Join Query?
    Shouldn't the query just be based on the Commissions Table or it's query.
    The Join for the subform should be established by the Form's Master/Child links?

  13. #33
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    Quote Originally Posted by OBP
    Why does your subform need a Join Query?
    Shouldn't the query just be based on the Commissions Table or it's query.
    The Join for the subform should be established by the Form's Master/Child links?

    Because I'm still learning and I thought I was normalizing the database.

    The commission stores an ID, CommissionStructureID, RequestID, and the InvoiceNbr, InvoiceAmt, VendorID, Date it was paid to the vendor. I have nothing connecting it to the Customer. So I joined the Commission Table to the Invoices table with the Invoice amount, and then pulled the Customer information from the CustID in the Invoice Table.
    Someday I'll understand everything...
    Even then...I'll still pretend I'm a beginner.

  14. #34
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Perhaps nested Subforms would be better, Customer Mainform with one subform for the Invoice with a subform of it's own for the Commission?

  15. #35
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    Quote Originally Posted by OBP
    Perhaps nested Subforms would be better, Customer Mainform with one subform for the Invoice with a subform of it's own for the Commission?
    That's a good idea. I've got the main form showing the vendors. I'm trying to show the commissions they earned.

    --Thinking--
    If I put a subform showing the Customers that the Vendor had commissions with. Then Show the invoices based on that customer as another subform and the commissions as a subform to the invoices... that might work... It's just crazy enough to possibly work. Thanks OBP.
    Someday I'll understand everything...
    Even then...I'll still pretend I'm a beginner.

  16. #36
    VBAX Expert Imdabaum's Avatar
    Joined
    Jun 2006
    Posts
    652
    Location
    Turns out A) adding the custID to the table wasn't that difficult and I was able to relate the tables. bada bing. B) Sometimes I wonder if I'm in the right field.... hence my signature. Cheers to all us who are learning. Thanks to everyone else for showing us the error of our ways.
    Someday I'll understand everything...
    Even then...I'll still pretend I'm a beginner.

  17. #37
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Great

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •