Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update query (Access 2000)

    Hi

    I cannot get my brain round a problem.

    I have two tables, one for people, one for ticket numbers (for a random raffle/lottery). Each person will have multiple tickets.

    I have a field in my people table which shows the date on which a person makes his last payment. If someone enters a value in this field, I want to change a Yes/No field called "Valid" in the Ticket field to show "no". I have created a form (with a subform showing ticket numbers & Valid field) to do so, Set up a Leave event for the date field, but only manage to edit the first of the ticket nos, not all of them.

    I really can't think how to resolve this.

    Can anyone help please.


    Colin

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update query (Access 2000)

    You're missing a table. You need a "join" table that holds a record with the keyID from the people table and a keyID (or ticket number) from the Tickets table. That allows you to create a person-ticket link, and that would be where you put any valid/invalid information, etc. If the tickets belong completely to an individual, you could make the ticket ID a unique field in the join table. If people can share tickets, then your key would be the combination of the person KeyID and the ticket.

    I don't understand the payment business, so I can't tell you where that field belongs. Are the people making payments on tickets?
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Update query (Access 2000)

    I'm assuming the ticket table has both the PersonID and a TicketID in it? That is, it shows which tickets have been assigned to which people?

    Anyway, what you are trying to do you shouldn't do!!! The Ticket record should not contain any information related to the Person (other than the PersonID). This is basic Rules of Normalization stuff. You do a query joining the 2 tables, and then select those Tickets for which there is a LastPaymentDate for that Person.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    Thanks

    I was duplicating data, and looking for an easy solution.

    I've done it properly now.


    Ta



    Colin

Posting Permissions

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