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

    Query problem (Access xp)

    I'm having a brian problem, and can't work out my problem

    I have a Table of people (tblPeople) which includes normal personl daat and a UniqueID. Linked to that I have a table with Ticket Nos (tblTicket) which incudes an autonumber field (TicketID) and the personal ID (PersonalID) for the link. This gives me a variable number of tickets belonging to each member.

    I want a query to pull out the persoanl data & the first ticket number (they are always consecutive) for each member. I can easily get a simple query which pulls out every ticket with multiple personal data, but I only want one record per person with the 1st ticket no.

    Unique records or values doesn't work, and I've been staring at this for an hour now.

    Can anyone help please.

    Thanks

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

    Re: Query problem (Access xp)

    I'm not Brian, but I'll try to answer.

    The best way to do this is to create two queries.

    1. A query to get the first ticket for each person.

    - Create a query based on tblTicket.
    - Add PersonalID and TicketID to the query grid.
    - Make the query into a Totals query by selecting View | Totals or by clicking the Totals button.
    - Leave the Total option for PersonalID as Group By, and set it to Min for TicketID.
    - Save this query as qryFirstTicket.

    2. A query to combine personal data and first ticket number.

    - Create a query based on tblPeople and qryFirstTicket.
    - Join them on PersonalID.
    - Add all fields from tblPeople you need (or tblPeople.*) to the query grid, plus the MinOfTicketID field.
    - If you want to display *all* persons, even those without a ticket, double click the join and specify that you want to include ALL records from tblPeople.

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

    Re: Query problem (Access xp)

    You've done better than try

    That works perfectly.

    Thanks again

    PS I wasn't quite with it earlier> It was meant to be a brain problem, not a brian problem!!!!!!!!!

Posting Permissions

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