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 6, 2013 at 2:29 PM
    #1
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,674
    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 work with Access 2010 every day and can usually get things figured out. However I have run into a issue and the Internet has been failing me, so I turn to you guys.

    I work for an IT company and I pull reports for the service desk. As you can image we have very detailed (and many) DBS. I am trying to pull a query with were it doesn't pull duplicate records for tickets. the DB creates several entries into for one ticket every time someone touches/updates it.

    I am not great with the SQL side, so I build everything in design view, but I can get around in it when I need to.

    I have tried SELECT DISTINCT, First(), setting Unique Value Yes.....

    Here is my SQL:

    As you can see this it's not a simple query, I tried to just have a query pull the info from the Table with no duplicates TicketNumbers and then run this one off of that, but that didn't seem to work either. I am open to any and all suggestion.

    Thanks.
     
  2. Jun 7, 2013 at 11:17 AM
    #2
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,375
    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
    Select Distinct is only going to get a distinct row if all of the parameters you're displaying are the same (FirstOfTicketNumber, iTrackerTeam, Shift...). So if one field is different, you'll get it as another hit. It doesn't just focus on the first field.

    Try removing the Distinct keyword. Select First(TicketAssignment...) may do what you need.

    You could also try First on every field (a lot of aliasing).
     
    Last edited: Jun 7, 2013
  3. Jun 7, 2013 at 11:36 AM
    #3
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,375
    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
    Another thing you could try is to build a unique view with your TicketNumber and primary key in the result:

    View: TicketNumberPrimaryKey
    Select First(TicketAssignment_vw.TicketNumber) AS FirstOfTicketNumber, First(primary_key) as key from tablename)

    That should give you a list of unique ticketnumbers tied to a table primary key.

    And then run your query against that view with a "where" clause matching your primary key of your main query to the result of the first query.

    e.g. Where key in TicketNumberPrimaryKey
     
  4. Jun 12, 2013 at 7:19 AM
    #4
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,674
    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...
    Thank you Good Sir.

    I though I had it working on Friday morning, but as I have been running report this week, I am sure that I failed. So now I will take you advice and see if that works.
     
  5. Jun 12, 2013 at 7:53 AM
    #5
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,674
    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...

    OK, I am missing something, I can't seem to get this to work. I went the route to run a query of my table to remove duplicates and then run my more complicated queries off this. It worked on Friday, but I must have not saved a change or saved a change that I didn't mean to; because it is still showing duplicates now. This is what I have now:

     
  6. Jun 12, 2013 at 11:33 AM
    #6
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,375
    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
    If you're just deleting the other records with a query and then trying to run your query, you have no assurance that the ticketnumber wasn't generated again in someone else's query between the time you deleted and the time you run your query.


    Note: you shouldn't use DISTINCT because each ticketnumber record can have a different results in the other fields which would result in a duplicate ticketnumber record. The only time you should use DISTINCT with multiple fields is when you can guarantee that all the fields will be the same. I typically use it to get one unique value for one field to populate something like a combo box. So, for example, the following query would generate one instance of each ticketnumber.
    SELECT DISTINCT [qry_InfoPACCAck&TTA].TicketNumber AS T
    FROM qry_InfoPACCAck&TTA

    I would get a list of all ticketnumbers:
    T
    4
    5
    7

    The minute I add a field, say "key", it could generate multiple hits for the same ticket number because each record has a unique primary key and the combination of the ticketnumber and the key is now what is compared for uniqueness.

    SELECT DISTINCT [qry_InfoPACCAck&TTA].TicketNumber AS T, [qry_InfoPACCAck&TTA].key as K
    FROM qry_InfoPACCAck&TTA

    T K
    4 1
    4 2
    5 7
    5 9
    7 3
    7 4

    So, my suggestion was to create a table with only the [qry_InfoPACCAck&TTA].TicketNumber and it's primary key using the "First" keyword to get one match for each ticketnumber.

    Then your select statement would match up the records based on matching the primary key, not the ticketnumber. The primary key is unique so you should get a one-to-one.

    So, say I generated a view called UniqueTicketNumber. I'll assume your primary key is named "key"
    SELECT FIRST([qry_InfoPACCAck&TTA].TicketNumber), key
    FROM qry_InfoPACCAck&TTA

    Now you should have a list of one ticketnumber and it's corresponding primary key.

    In your second query, join to "UniqueTicketNumber" based on matching the primary key (I've removed DISTINCT and FIRST from your query):

    SELECT [qry_InfoPACCAck&TTA].key, [qry_InfoPACCAck&TTA].iTrackerTeam, [qry_InfoPACCAck&TTA].Shift, [qry_InfoPACCAck&TTA].infoPACCTicketAssignee, [qry_InfoPACCAck&TTA].TicketNumber) AS FirstOfTicketNumber, [qry_InfoPACCAck&TTA].CallType, [qry_InfoPACCAck&TTA].TTA
    FROM [qry_InfoPACCAck&TTA]
    INNER JOIN UniqueTicketNumber ON [qry_InfoPACCAck&TTA].key = [UniqueTicketNumber].key
    GROUP BY [qry_InfoPACCAck&TTA].iTrackerTeam, [qry_InfoPACCAck&TTA].Shift, [qry_InfoPACCAck&TTA].infoPACCTicketAssignee, [qry_InfoPACCAck&TTA].CallType, [qry_InfoPACCAck&TTA].TTA;
     
    Last edited: Jun 12, 2013
  7. Jun 12, 2013 at 1:26 PM
    #7
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,674
    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...
    Robert,

    I think I am so close to completely understand this. I am starting to understand what you are saying and it makes since. However you lost me on the primary Key part.I mean, I know what the primary key is, but I don't think I have one set in qry_InfoPACCAck&TTA

    I am pulling from tables with multiple records for each ticket number, every time a change is made, it creates a new record with the primary key in that table being the ticket number. I am trying to pull the first record for a ticket that was created by a member of a certain team. I have that team built out in tblRoster table with the Agent Report Name set as the Primary Key in that table. Once I have the duplicates removed, I have queries that pull the ticket count based on the tickets Call Type.

    I really appreciate your help. I have been reading forums for the last couple of weeks trying to solve this. I also appreciate you patients with me.
     
  8. Jun 12, 2013 at 1:30 PM
    #8
    jtav2002

    jtav2002 Kenny Fuckin Powers

    Joined:
    Jun 29, 2009
    Member:
    #19074
    Messages:
    4,499
    Gender:
    Male
    First Name:
    Josh
    Reading, PA
    Vehicle:
    2013 Tundra DBL Cab TRD OR
    Damn I'm glad the reports and queries I run out of access aren't that involved lol.
     
  9. Jun 12, 2013 at 2:50 PM
    #9
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,375
    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
    Is there an Autonumber column? It would be a number field that increments automatically when you enter a new record.
     
    Last edited: Jun 12, 2013
  10. Jun 12, 2013 at 2:54 PM
    #10
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,674
    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...
    No there is not, not in the Table or the Queries.
     
  11. Jun 12, 2013 at 2:58 PM
    #11
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,375
    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
    How many records do you already have in your table? Is it feasible to add a new column of type Autonumber?
     
  12. Jun 12, 2013 at 3:17 PM
    #12
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,375
    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 noticed that you're pulling from a query which wouldn't have a primary key. Can you look at the table where the ticketnumber is being pulled from (in the query code for qry_InfoPACCAck&TTA)? Those tables might have a primary key in them. If it does, can you modify the query to include the primary key?

    My guess is you have two tables (InfoPAACCAck and TTA)
     
    Last edited: Jun 12, 2013
  13. Jun 13, 2013 at 6:38 AM
    #13
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,674
    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...
    There is no primary Key set in the table for qry_InfoPACCAck&TTA. There are four tables used in that Query:
    tblRoster (Team Roaster and coordinating login IDs for different systems)
    TicketAssignment_vw
    TicketActivity_vw
    JournalRecent150_vw

    Ticket numbers are in each of the three tables and that's how the tables are joined. Every table is well over 65k records, spanning back years. The Journal DB is so large that had to be split into 3 parts and I think I will have to split it again. Now, 99.9% of the time I only need the last month or two. So if need be, I could create a query to pull the records for the last 3 months and then add a primary key, would that be a solution?

    I report for my department, so I get a updated database to work with from that department once a week, so the tables
     
  14. Jun 13, 2013 at 8:35 AM
    #14
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,375
    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
    Wow. That's kind of a bad database design, but what are you going to do.

    You could add your own primary key. I'd probably write something in vba that automatically populates it for you so it would be less tedious.

    Is there any table which is guaranteed to only have one instance of the ticketnumber or are there duplicate entries in every table?

    If there was, I'd add an autonumber column, and populate the old entries with vba code.

    Another option, there's a way to add a rownumber with some code. You might give something like this a try:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/96354fb6-51bf-4996-a308-c359b3dc3ac5/
     
  15. Jun 13, 2013 at 8:45 AM
    #15
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,674
    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...
    Thanks, I will look at me tables to see if I can find one without any duplicates.

    Most of us were not happy when the company bought this ticketing system. It has it's own internal report app, but it is limited unless you buy the 10 different add-ons and then it still doesn't give the details we want.

    We have a reporting team that handles all the databases and reporting for customers, but they don't do any internal report. <- Dumb!
    So I do the reporting for our service desk, which has requirements for the amount of time the have to acknowledge (TTA) and work a ticket. Plus the management needs to know why is pulling their own weight and who isn't. Thus the reason for an accurate ticket could by associate.

    Thanks for your help, let me see what I can do about adding a primary key to a table and then following your previous advice. I am sure I will have a few more questions for you as I figure this out.
     
  16. Jun 13, 2013 at 10:34 AM
    #16
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,674
    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...
    Just tried to pull the last two months of records into a new table to add a row for autonumber and primary key... 138k+ records. Thats why it takes 30 mins to run some of my queries.

    Three days short of 2 full months = 131,066 records.
     
    Last edited: Jun 13, 2013
  17. Jun 13, 2013 at 11:09 AM
    #17
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,375
    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
    And they're doing this all with Access 2010? Or is that just what you use?
     
  18. Jun 13, 2013 at 11:33 AM
    #18
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,674
    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...
    They give me the tables as Access databases and Access is all they are willing to let me use.

    The reporting team doesn't like that I am doing what I am doing but they have to let me. The powers that be want numbers for our group and the reporting team doesn't do internal reporting. So I got the short end of the stick.
     
  19. Jun 13, 2013 at 11:39 AM
    #19
    Chipskip

    Chipskip [OP] N7MCS

    Joined:
    Aug 31, 2010
    Member:
    #42519
    Messages:
    6,674
    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...
    So, when I try to run a new query with...

    I get an error, " You tried to execute a query that does not include the specified expression "key" as part of an aggregate function."
     
  20. Jun 13, 2013 at 11:46 AM
    #20
    Evil Monkey

    Evil Monkey There's an evil monkey in my truck

    Joined:
    Aug 8, 2007
    Member:
    #2352
    Messages:
    8,375
    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
    Unless you have a field named "key" it will fail. It thinks it's a function since there is no "key" field. My use of the word key was a generic reference to the primary key field, whatever the name would be. So for example, if your database had a RecordNumber field that was the primary key, you'd substitute that name in for my use of the word "key"

    (e.g. SELECT First([qry_InfoPACCAck&TTA].TicketNumber), [qry_InfoPACCAck&TTA].RecordNumber
    FROM [qry_InfoPACCAck&TTA]; ).

    Since you have no primary key, it won't work. You'll have to use the example in the link I gave to create a numbering field on the fly. Then you use the method I gave to run on that result.

    Is it possible that the tables they're giving you just aren't complete? Perhaps there is a primary key in their database but when they export it, they don't include that field.
     
    Last edited: Jun 13, 2013
To Top