Results 1 to 4 of 4
  1. #1
    nd0911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    31

    Monthly customer debt's query

    Hello,



    I think I need something complicated:

    I have a product table that conteins the day in the month (from 1 to 28 not more) that the customer need to pay his bill (the products is a monthly retainer, exept of one product that is need to be ignored).

    I need a sql statment that check all the products records, and if the product meets the following conditions it will insert a debt record in the "debts table", with the next (following) month of the last record and with the "PayDay" value as the day in the month, if it is a new record it suppose to take the actual year and month with the "PayDay".

    here are the conditions that need to be TRUE to insert the records:

    1. that in the "product table", the product name is not "other"
    2. that in the "product table", the product stats is "Active"
    3. that in the "debts table", this product do not have any record (this condition will acrros most likely after adding new product)
    4. that in the "debts table", the last record (with the newest date) are in status "payed"



    product table looks like this:
    ProductId, ProductName, ProductStatus, PayDay (like I said, this is the day in the month that the customer need to pay from 1 to 28)

    debts table look like this:
    DebtsId, ProductId, PayDate, PaymentStatus

    in the above tables there is more fields but do not Important to this Q.

    I hope there is a nice solution and I was understood

    Thank you !!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Click image for larger version. 

Name:	debt.png 
Views:	7 
Size:	19.3 KB 
ID:	31458

    You seem to be missing a field, ClientID in tDebts (whos debt is it?)
    This may work tho:


    build a query to get the MAX date of the clients debts, qsLastDebt
    sql = SELECT tDebts.ClientID, Max(tDebts.PayDate) AS MaxOfPayDate, tDebts.PaymentStatus FROM tDebts GROUP BY tDebts.ClientID, tDebts.PaymentStatus;


    then in an new query, qaAddDebt, (shown in photo)
    Take the client joined to qsLastDebt to see if they are eligible,(via criteria, 'payed', 'active')
    then add the Product to the tDebt table.

  3. #3
    nd0911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    31
    thanks for your answer but I think that customerId is not importent to the solution (of corse in my tables I have customerId) because I want a query that over all of the products and not all of the customers, every customer may have several product's...

    I'm new with access query so I don't no if it possible what I want to do, that in one/two query's I create all of the new records for the debts from all of the product's.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    youd have to limit it according to your data.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 09-01-2015, 12:20 PM
  2. Replies: 3
    Last Post: 05-13-2015, 05:54 PM
  3. Replies: 6
    Last Post: 12-11-2013, 09:27 PM
  4. Replies: 6
    Last Post: 11-10-2012, 09:49 PM
  5. Replies: 5
    Last Post: 11-24-2010, 11:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums