Results 1 to 9 of 9
  1. #1
    HelenP is offline Novice
    Windows 2K Access 2000
    Join Date
    Oct 2010
    Posts
    16

    Exclamation Identifying the first entry

    Hello



    I am creating a database for payments made towards debts. Each person will make a number of payments on different dates. I want to have a column in the payments table indicating whether it is the first payment made by the person or not.

    I have managed to create a query to display the dates of the first payments made by each person but now I am stuck!

    Do I need the query or can I just write some code within the table?

    Thanks for your help

  2. #2
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    Just a few questions to get a better idea what we're looking at:
    • Have you added this "First Payment" as a field in one of your tables, and if so, is it a Yes/No or something else?
    • Does the query you created return just the dates or does it include more specific info (Name, Amount, etc...)?
    • What exactly are you hoping to accomplish? Is it a one-time update of your existing data or do you want every payment entered to see if it is the first for a specific person?

  3. #3
    HelenP is offline Novice
    Windows 2K Access 2000
    Join Date
    Oct 2010
    Posts
    16
    Hi

    • I have not added a field in the table yet because I do not know what data type I would want.
    • At the moment the query only includes the person's ID and the date
    • I want it to be able to update every time an entry is added. There are 2000 records in the table already but it will be updated frequently. I want it to be done for every person.
    Thanks

  4. #4
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    • If you choose to add a field, it should probably be Yes/No.
    • Is the info returned by your query unique?
      • Nobody made more than one payment in a single day, no two people with the same name, etc...
      • Even if it is currently unique, is it possible it won't always be (you might not have two customers with the same name now, but you could in the future, or something like that)?
      • If it's not always going to be unique you will want to edit the query to include a unique identifier.
    • You will probably want to create an Update query based on your query to populate the exiting records and then you'll need to add code to your entry form to see if the current customer has ever made a payment before, and if not, flag the record as a first payment.

  5. #5
    HelenP is offline Novice
    Windows 2K Access 2000
    Join Date
    Oct 2010
    Posts
    16
    • The info returned by the query is unique, some people have paid twice in one day but it only displays the date once
    • How do I create an update query in Access 2000? I have not used it much and I have attempted update queries before with no success!

  6. #6
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    It doesn't sound like the query info is unique currently.
    • If you tell me only the person's name and date and ask me to mark the record as the first payment, would I find that name and date on more than one payment? If the payment date also includes time it could be unique but that's kind of hard to retroactively apply if it's not already there.
    • Is there a primary key for the table which could be used to identify the specific record that needs to be updated?
    I created a sample DB and was able to retrieve the first payment records for each person using a query to return the Min Date and unique transaction ID. This type of query cannot be used directly for updating a table but there is a way to do it:

    • Create a make table query to create a temp table with the results of the query.
    • Create an Update query linking the payment table to the temp table on the transaction ID and have it update the First Payment field in the main table. (I added in criteria to only make the change if the record is currently false).

    This will update all the old data but I would use a more efficient method for including this info on future payments. I'll go into that when I have a little more time (if you need help on it).

    If you would like to see it in action, take a look at the sample DB. There is a form in it which you can use to run the entire process (create temp, update table) and another one to reset the data so you can test it again. When you reset the data it loses the checkbox formatting so true = -1 (if you already knew that please forgive the reminder).

  7. #7
    HelenP is offline Novice
    Windows 2K Access 2000
    Join Date
    Oct 2010
    Posts
    16
    Thank you so much that is exactly what I want!

    When you get the chance please could you explain how to do this when new payments are added. That would be brilliant.

    Thanks again

  8. #8
    HelenP is offline Novice
    Windows 2K Access 2000
    Join Date
    Oct 2010
    Posts
    16
    Also, if I want to add a large number of payments at the same time is the best way to do this to import it into the payments table? It is currently stored on a spreadsheet. This is how I did it to start off with but I do not know if it is the most effective way to do it.

    Thanks again

  9. #9
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    If you add another bulk payment update (via spreadsheet or other methods) run the queries from before - they can be kind of "high maintenence" so you don't want to run them with every record, but it would be the best way to process a large number of records.

    For adding individual payments, you can set some code to run in the BeforeUpdate event to see if there are any other records for the entered customer. If no records are found, have it set the First Payment value to true (I would probably use a hidden checkbox on the form for easier handling).

    See the updated sample DB for an example. The sample uses a function I created which requires a reference to Microsoft ActiveX Data Objects to work properly. DLookup might work just as well and I'm sure there is probably a more efficient method which could also be used. I also set the code to only display the check box when it is a first payment.

    It might not be the best way, but I hope it gives you some ideas.

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

Similar Threads

  1. Make new entry at once
    By carstenhdk in forum Forms
    Replies: 1
    Last Post: 05-12-2010, 05:36 AM
  2. Identifying new data
    By manicamaniac in forum Programming
    Replies: 5
    Last Post: 04-28-2010, 11:08 AM
  3. data entry
    By ngeng4 in forum Forms
    Replies: 22
    Last Post: 03-18-2010, 07:12 PM
  4. Table Entry Sum
    By seraph in forum Access
    Replies: 5
    Last Post: 08-15-2009, 09:49 AM
  5. Entry into subform
    By lynchoftawa in forum Forms
    Replies: 1
    Last Post: 06-17-2009, 09:28 AM

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