Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select lowest date (Win ME/Access 97)

    I have a query with a list of upcoming payments sorted by customer number and due date. Some of the payments have been made, and I want to select for each customer the next payment. In other words, if the customer paid the Jan 1 payment, and has pending the Feb, Mar and Apr payments, the query should show only the Feb record.

    I tried selecting the top 1 value based on the date and selecting only records with zero in the amount paid field, but that works on the whole table, and it looks like it selects only one record. I need the top record for each customer.

    Please, any ideas?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Select lowest date (Win ME/Access 97)

    In your query, Select Customer Number, Due Date, and Paid Amount. Set the Criteria for Paid Amount to 0. Select View Totals to group the data and change the total on due date to Minimum.

    This will first get all records that have a zero paid amount and then get the minimum or next due date. The data is also grouped by Customer.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select lowest date (Win ME/Access 97)

    There are 24 customers in the table, each with several pending payments. I did the query as you suggested, but it only returns the date for the first customer. Example, customer 1, next payment 14 May 03, correctly selected. For customers 2 through 24 nothing is returned. I need 24 lines of information in the query, one for each customer.

    I am tearing my hair out over this.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Select lowest date (Win ME/Access 97)

    It's possible that you have your GroupBy clauses in the wrong order - the Customer should be the first one.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select lowest date (Win ME/Access 97)

    Customer Number is first, Group By. Then Date Due, Min, Assending. Then Amount Paid, Group By. I just moved Date Due to the last position, but the same results-only one record is returned.

  6. #6
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select lowest date (Win ME/Access 97)

    I did two queries, one to select the zero payments, and then used that one to do the GroupBy and Min routine. That gets me the list of 24 customers with their next payment date.

    Now I build another query to combine that next payment date query with other queries. So far so good. Let's see what rocks are in the road now when I do the report. Thanks.

    Do you all realize the tremendous help that you are giving?

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Select lowest date (Win ME/Access 97)

    Upon reading your first post, is the query returning the paid amounts or is paid amounts in another table? For this to work, you will need customer id, date, and paid amount. If paid amount is in another table then you need to join to the information.

    Perhaps you could post the database so we can take a look at it.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select lowest date (Win ME/Access 97)

    I don't know how to reduce the DB to less than 100k. Here is a list of the tables and the queries. Will that help?

    Tables:
    TCustMst
    TFinancial
    TPagos
    TPagosHist

    Queries:
    QNextPayPre
    From TPagos, uses CustNum, DateDue, and AmountPaid
    QNextPayDate
    From QNextPayPre, uses CustNum and DateDue to get the next payment date info
    QSumPaymentsCurr
    Takes CustNum and AmountPaid from TPagos and sums up AmountPaid by CustNum
    QSumPaymentsHist
    Takes CustNum and AmountPaid from TPagosHist and sums up AmountPaid by CustNum
    QReportStatusPagos
    Combines TPagos, TFinancial, QNextPayDate, QSumPaymentsCurr and QSumPaymentsHist. The final report is based on this query.

    I selected the five with ShowTable in the design view of the query, linked the CustNum field, and drag/dropped the fields into the query.

  9. #9
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select lowest date (Win ME/Access 97)

    I have sent the zipped database. A few comments. The report that I am trying to make is AnalisisDeClientesYPagos. The fields that cause me trouble are QSumPagosCurr and QSumPagosHist. The report is based on the query QReportStatusPagos, which in turn is based on several other queries. With only QClientes, QFinancial, and QNextPayDate in the query, it runs OK. When I add the QSumPagosCurr and QSumPagosHist is when things go bad.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select lowest date (Win ME/Access 97)

    Does my reply <post#=216861>post 216861</post#> in the other thread on this subject help?

Posting Permissions

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