1. Welcome to Tacoma World!

    You are currently viewing as a guest! To get full-access, you need to register for a FREE account.

    As a registered member, you’ll be able to:
    • Participate in all Tacoma discussion topics
    • Communicate privately with other Tacoma owners from around the world
    • Post your own photos in our Members Gallery
    • Access all special features of the site

You an Access 2010 Guru? Help please.

Discussion in 'Technology' started by Chipskip, Jun 6, 2013.

  1. Jun 13, 2013 at 11:48 AM
    #21
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,576
    Gender:
    Male
    First Name:
    Chip
    Phoenix, AZ
    Vehicle:
    98 Tacoma Limited
    Deck Plate, '17 rims, 2" spacers, Tundra Brake Upgrade, 3" Coilover/shackle lift, 01-04 Center Console, Ultragauge, LED interior lights, HO alternator...
    I created a field call "Key" in my table and added it to the qry_InfoPACCAck&TTA query.
     
  2. Jun 13, 2013 at 11:59 AM
    #22
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    Try a different name for the field. Like pkey. It might be a reserved word in Access (like "Date").
     
  3. Jun 13, 2013 at 12:00 PM
    #23
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    I just looked it up. It is a reserved word. Change it to pkey.
     
  4. Jun 13, 2013 at 12:18 PM
    #24
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,576
    Gender:
    Male
    First Name:
    Chip
    Phoenix, AZ
    Vehicle:
    98 Tacoma Limited
    Deck Plate, '17 rims, 2" spacers, Tundra Brake Upgrade, 3" Coilover/shackle lift, 01-04 Center Console, Ultragauge, LED interior lights, HO alternator...
    This worked:

    SELECT First([qry_InfoPACCAck&TTA].TicketNumber) AS FirstOfTicketNumber
    FROM [qry_InfoPACCAck&TTA]
    GROUP BY [qry_InfoPACCAck&TTA].pKey;
     
  5. Jun 13, 2013 at 12:34 PM
    #25
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    Cool. So now, include pkey in your original query and Inner Join on qry_InfoPACCCAck&TTA.pkey = to that view's pkey (whatever you named it).
     
  6. Jun 13, 2013 at 2:48 PM
    #26
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,576
    Gender:
    Male
    First Name:
    Chip
    Phoenix, AZ
    Vehicle:
    98 Tacoma Limited
    Deck Plate, '17 rims, 2" spacers, Tundra Brake Upgrade, 3" Coilover/shackle lift, 01-04 Center Console, Ultragauge, LED interior lights, HO alternator...
    I had to uncheck the show box for pkey to work on that query. So I don't have it to Join.
     
  7. Jun 13, 2013 at 3:03 PM
    #27
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    Add the pkey to this query's result

    So:
    SELECT First([qry_InfoPACCAck&TTA].TicketNumber) AS FirstOfTicketNumber, First([qry_InfoPACCAck&TTA].pKey) as pkey
    FROM [qry_InfoPACCAck&TTA]
     
    Last edited: Jun 13, 2013
  8. Jun 13, 2013 at 3:20 PM
    #28
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    I'm making a sample database to check the query. Hold Please...
     
  9. Jun 13, 2013 at 3:31 PM
    #29
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    Okay, so make a new query and use this as the SQL:

    SELECT [qry_InfoPACCAck&TTA].iTrackerTeam, [qry_InfoPACCAck&TTA].Shift, [qry_InfoPACCAck&TTA].infoPACCTicketAssignee, [qry_InfoPACCAck&TTA].TicketNumber, [qry_InfoPACCAck&TTA].CallType, [qry_InfoPACCAck&TTA].TTA
    FROM [qry_InfoPACCAck&TTA]
    INNER JOIN UniqueTicketNumber ON [qry_InfoPACCAck&TTA].pkey = [UniqueTicketNumber].pkey;
     
  10. Jun 14, 2013 at 8:00 AM
    #30
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,576
    Gender:
    Male
    First Name:
    Chip
    Phoenix, AZ
    Vehicle:
    98 Tacoma Limited
    Deck Plate, '17 rims, 2" spacers, Tundra Brake Upgrade, 3" Coilover/shackle lift, 01-04 Center Console, Ultragauge, LED interior lights, HO alternator...
    ^ This only returns one record.

    ^This asks for me to enter the parameters for qry_InfopaccTickets_NoDupes.pKey because I don't that shown in that query because it only returns one record.
     
  11. Jun 14, 2013 at 11:09 AM
    #31
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    I edited it yesterday to include first (...pkey) as pkey. Double check that yours is the same as written. It works in my dummy database.
     
  12. Jun 14, 2013 at 11:38 AM
    #32
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,576
    Gender:
    Male
    First Name:
    Chip
    Phoenix, AZ
    Vehicle:
    98 Tacoma Limited
    Deck Plate, '17 rims, 2" spacers, Tundra Brake Upgrade, 3" Coilover/shackle lift, 01-04 Center Console, Ultragauge, LED interior lights, HO alternator...
    Yeah, I tried that one, but it only comes back with one record.

    I tried it in a clean query.
     
  13. Jun 14, 2013 at 1:38 PM
    #33
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    Are you getting the pkey with the one record?
    A ticketnumber and a pkey?
     
  14. Jun 14, 2013 at 2:10 PM
    #34
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,576
    Gender:
    Male
    First Name:
    Chip
    Phoenix, AZ
    Vehicle:
    98 Tacoma Limited
    Deck Plate, '17 rims, 2" spacers, Tundra Brake Upgrade, 3" Coilover/shackle lift, 01-04 Center Console, Ultragauge, LED interior lights, HO alternator...
    tone record showing one ticketnumber and pkey
     
  15. Jun 14, 2013 at 3:20 PM
    #35
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    I wonder why the join fails if you have a pkey in both queries.

    Unfortunately, I am not a my work computer. All I have right now is my phone. I won't be able to help again until monday.
     
  16. Jun 14, 2013 at 3:29 PM
    #36
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    Try adding a group by pkey in the unique ticket number query. I don't recall if I had to take that out or put it in.

    Btw, your pkey is unique for each record, correct?
     
  17. Jun 17, 2013 at 9:50 AM
    #37
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,576
    Gender:
    Male
    First Name:
    Chip
    Phoenix, AZ
    Vehicle:
    98 Tacoma Limited
    Deck Plate, '17 rims, 2" spacers, Tundra Brake Upgrade, 3" Coilover/shackle lift, 01-04 Center Console, Ultragauge, LED interior lights, HO alternator...
    Yes, I have access autonumbering the Primary Key field in a table I created. I copied over the records for the month I need to report on.

    I have tried the unique ticket queue as grouped by and not grouped by (unclicked show). I get only one record returned when I do group by. But when I unclick show, it works, eg gives me all the tickets listed without any duplicates.

    ^ Yes, I am using the DISTINCT command here, that is the only way it works.

    ^ DISTINCT or not does not effect the results, comes back with duplicates.



    SO, Then I Join this unique ticket numbers with a different query that has all the other information I need: Associate Name, Shift, ticket call type, etc. I repopulates all the duplicate ticket numbers in the out come.
     
    Last edited: Jun 17, 2013
  18. Jun 17, 2013 at 10:18 AM
    #38
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    So this is the query for my uniqueticketnumber from my test database (the GROUP BY is "ticketnumber" not "pkey"):

    SELECT First([qry_InfoPACCAck&TTA].TicketNumber) AS FirstTicketNumber, First([qry_InfoPACCAck&TTA].pKey) AS pKey
    FROM [qry_InfoPACCAck&TTA]
    GROUP BY [qry_InfoPACCAck&TTA].TicketNumber;

    I have four records with unique pkey numbers and two duplicate ticket numbers:

    pkeys:
    6, 7, 8, 9
    Tickets:
    500, 500, 400, 400

    When I run the query, I get two records (which is what I would expect):
    8, 400
    6, 500
     
    Last edited: Jun 17, 2013
  19. Jun 17, 2013 at 10:26 AM
    #39
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,376
    Gender:
    Male
    First Name:
    Robert
    Escondido, CA
    Vehicle:
    07 4x4 DC SR5 TRD Off-road
    Weathertech front & rear mats, rear suspension TSB, Toytec AAL for TSB, Hi-Lift Jack, Bilstein 5100 & Toytec Adjustable coilovers, Built Right UCAs, KMC XD 795 Hoss Wheels, Definity Dakota MTs 285/75R16, Leer XR, Thule Tracker II & Thule MOAB basket
    DISTINCT will work here because you're only getting one field back.

    DISTINCT won't work here because your pkey in combination with your ticketnumber are seperate hits (pkey is unique to each record).

    So in my data sample above, I'd get 4 hits since the combination of ticketnumber and pkey are different for each record.

    6, 500
    7, 500
    8, 400
    9, 400

    e.g. the result of 6 & 500 is different from the 7 & 500 so it qualifies as DISTINCT.

    If my database had a duplicate 6 & 500 like this:

    6, 500
    6, 500
    7, 500
    8, 400
    9, 400

    It would ignore the second 6, 500 since it isn't DISTINCT, but I would get all the other records as a result.

    DISTINCT doesn't work on just the first field in a multi-field statement. It works against all the data returned in a record.
     
  20. Jun 17, 2013 at 10:38 AM
    #40
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,576
    Gender:
    Male
    First Name:
    Chip
    Phoenix, AZ
    Vehicle:
    98 Tacoma Limited
    Deck Plate, '17 rims, 2" spacers, Tundra Brake Upgrade, 3" Coilover/shackle lift, 01-04 Center Console, Ultragauge, LED interior lights, HO alternator...
    Right, I am completely understanding what you are saying about DISTINCT now. Thank you.
     
To Top