Mike "Wuzzy121". Rest in peace, brother

Go Back   Tacoma World Forums > Off-Topic > Technology

Notices

You an Access 2010 Guru? Help please.

Reply
 
Thread Tools Search this Thread
Old 06-06-2013, 02:29 PM   #1
Chipskip [OP] Chipskip is offline
N7MCS
Chipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shed
 
Chipskip's Avatar
Army: 31R, HHB, 2/43 ADA Battalian, 108th ADA Brigade, XVIII Airborne Corps
Name: Chip
Joined: Aug 2010, #42519
Location: Phoenix, AZ
Age: 35
Gender: Male
Posts: 5,583
Chipskip's Tacoma Gallery
You an Access 2010 Guru? Help please.

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:

Quote:
Originally Posted by My SQL

SELECT DISTINCT First(TicketAssignment_vw.TicketNumber) AS FirstOfTicketNumber, tblRoster.iTrackerTeam, tblRoster.Shift, tblRoster.infoPACCTicketAssignee, [JournalRecent150_vw]![JournalDate]-[TicketAssignment_vw]![AssignedDate] AS TTA
FROM JournalRecent150_vw INNER JOIN (tblRoster INNER JOIN (TicketActivity_vw INNER JOIN TicketAssignment_vw ON TicketActivity_vw.TicketNumber = TicketAssignment_vw.TicketNumber) ON tblRoster.infoPACCTicketAssignee = TicketAssignment_vw.AcknowledgedBy) ON JournalRecent150_vw.TicketNumber = TicketAssignment_vw.TicketNumber
WHERE (((TicketAssignment_vw.AcknowledgedDate) Between [StartDate] And [EndDate]) AND ((TicketActivity_vw.CallType) Like "Incident") AND ((tblRoster.Status)<>"Terminated"))
GROUP BY tblRoster.iTrackerTeam, tblRoster.Shift, tblRoster.infoPACCTicketAssignee, [JournalRecent150_vw]![JournalDate]-[TicketAssignment_vw]![AssignedDate]
ORDER BY tblRoster.iTrackerTeam DESC , tblRoster.Shift, tblRoster.infoPACCTicketAssignee;
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.
  Reply With Quote
Old 06-07-2013, 11:17 AM   #2
There's an evil monkey in my truck
Evil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shed
 
Evil Monkey's Avatar
Navy: Tradevman (flight simulator tech), Aviation Electrician (F-14a), Computer Scientist(civ)
Name: Robert
Joined: Aug 2007, #2352
Location: Escondido, CA
Age: 52
Gender: Male
Posts: 8,251
Evil Monkey's Tacoma Gallery
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).
  Reply With Quote
Old 06-07-2013, 11:36 AM   #3
There's an evil monkey in my truck
Evil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shed
 
Evil Monkey's Avatar
Navy: Tradevman (flight simulator tech), Aviation Electrician (F-14a), Computer Scientist(civ)
Name: Robert
Joined: Aug 2007, #2352
Location: Escondido, CA
Age: 52
Gender: Male
Posts: 8,251
Evil Monkey's Tacoma Gallery
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
  Reply With Quote
Old 06-12-2013, 07:19 AM   #4
Chipskip [OP] Chipskip is offline
N7MCS
Chipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shed
 
Chipskip's Avatar
Army: 31R, HHB, 2/43 ADA Battalian, 108th ADA Brigade, XVIII Airborne Corps
Name: Chip
Joined: Aug 2010, #42519
Location: Phoenix, AZ
Age: 35
Gender: Male
Posts: 5,583
Chipskip's Tacoma Gallery
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.
  Reply With Quote
Old 06-12-2013, 07:53 AM   #5
Chipskip [OP] Chipskip is offline
N7MCS
Chipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shed
 
Chipskip's Avatar
Army: 31R, HHB, 2/43 ADA Battalian, 108th ADA Brigade, XVIII Airborne Corps
Name: Chip
Joined: Aug 2010, #42519
Location: Phoenix, AZ
Age: 35
Gender: Male
Posts: 5,583
Chipskip's Tacoma Gallery
Quote:
Originally Posted by Evil Monkey View Post
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

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:

Quote:
SELECT DISTINCT [qry_InfoPACCAck&TTA].iTrackerTeam, [qry_InfoPACCAck&TTA].Shift, [qry_InfoPACCAck&TTA].infoPACCTicketAssignee, First([qry_InfoPACCAck&TTA].TicketNumber) AS FirstOfTicketNumber, [qry_InfoPACCAck&TTA].CallType, [qry_InfoPACCAck&TTA].TTA
FROM [qry_InfoPACCAck&TTA]
GROUP BY [qry_InfoPACCAck&TTA].iTrackerTeam, [qry_InfoPACCAck&TTA].Shift, [qry_InfoPACCAck&TTA].infoPACCTicketAssignee, [qry_InfoPACCAck&TTA].CallType, [qry_InfoPACCAck&TTA].TTA;
  Reply With Quote
Old 06-12-2013, 11:33 AM   #6
There's an evil monkey in my truck
Evil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shed
 
Evil Monkey's Avatar
Navy: Tradevman (flight simulator tech), Aviation Electrician (F-14a), Computer Scientist(civ)
Name: Robert
Joined: Aug 2007, #2352
Location: Escondido, CA
Age: 52
Gender: Male
Posts: 8,251
Evil Monkey's Tacoma Gallery
Quote:
Originally Posted by Chipskip View Post
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:
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;
  Reply With Quote
Old 06-12-2013, 01:26 PM   #7
Chipskip [OP] Chipskip is offline
N7MCS
Chipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shed
 
Chipskip's Avatar
Army: 31R, HHB, 2/43 ADA Battalian, 108th ADA Brigade, XVIII Airborne Corps
Name: Chip
Joined: Aug 2010, #42519
Location: Phoenix, AZ
Age: 35
Gender: Male
Posts: 5,583
Chipskip's Tacoma Gallery
Quote:
Originally Posted by Evil Monkey View Post
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;
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

Quote:
Originally Posted by qry_InfoPACCAck&TTA
SELECT tblRoster.iTrackerTeam, tblRoster.Shift, tblRoster.[Agent Report Name], TicketAssignment_vw.TicketNumber, TicketActivity_vw.CallType, [JournalRecent150_vw]![JournalDate]-[TicketAssignment_vw]![AssignedDate] AS TTA, tblRoster.[Agent Report Name]
FROM tblRoster INNER JOIN (TicketActivity_vw INNER JOIN (JournalRecent150_vw INNER JOIN TicketAssignment_vw ON JournalRecent150_vw.TicketNumber = TicketAssignment_vw.TicketNumber) ON TicketActivity_vw.TicketNumber = TicketAssignment_vw.TicketNumber) ON tblRoster.infoPACCTicketAssignee = TicketAssignment_vw.AcknowledgedBy
WHERE (((TicketAssignment_vw.AcknowledgedDate) Between [StartDate] And [EndDate]))
GROUP BY tblRoster.iTrackerTeam, tblRoster.Shift, TicketAssignment_vw.TicketNumber, TicketActivity_vw.CallType, [JournalRecent150_vw]![JournalDate]-[TicketAssignment_vw]![AssignedDate], tblRoster.[Agent Report Name]
ORDER BY tblRoster.iTrackerTeam DESC , tblRoster.Shift;
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.
  Reply With Quote
Old 06-12-2013, 01:30 PM   #8
Kenny Fuckin Powers
jtav2002 is one of the sharper tools in the shedjtav2002 is one of the sharper tools in the shedjtav2002 is one of the sharper tools in the shedjtav2002 is one of the sharper tools in the shedjtav2002 is one of the sharper tools in the shedjtav2002 is one of the sharper tools in the shedjtav2002 is one of the sharper tools in the shedjtav2002 is one of the sharper tools in the shedjtav2002 is one of the sharper tools in the shedjtav2002 is one of the sharper tools in the shedjtav2002 is one of the sharper tools in the shed
 
jtav2002's Avatar
Name: Josh
Joined: Jun 2009, #19074
Location: Reading, PA
Age: 30
Gender: Dude
Posts: 4,508
jtav2002's Tacoma Gallery
Damn I'm glad the reports and queries I run out of access aren't that involved lol.
  Reply With Quote
Old 06-12-2013, 02:50 PM   #9
There's an evil monkey in my truck
Evil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shed
 
Evil Monkey's Avatar
Navy: Tradevman (flight simulator tech), Aviation Electrician (F-14a), Computer Scientist(civ)
Name: Robert
Joined: Aug 2007, #2352
Location: Escondido, CA
Age: 52
Gender: Male
Posts: 8,251
Evil Monkey's Tacoma Gallery
Quote:
Originally Posted by Chipskip View Post
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.
Is there an Autonumber column? It would be a number field that increments automatically when you enter a new record.
  Reply With Quote
Old 06-12-2013, 02:54 PM   #10
Chipskip [OP] Chipskip is offline
N7MCS
Chipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shed
 
Chipskip's Avatar
Army: 31R, HHB, 2/43 ADA Battalian, 108th ADA Brigade, XVIII Airborne Corps
Name: Chip
Joined: Aug 2010, #42519
Location: Phoenix, AZ
Age: 35
Gender: Male
Posts: 5,583
Chipskip's Tacoma Gallery
Quote:
Originally Posted by Evil Monkey View Post
Is there an Autonumber column? It would be a number field that increments automatically when you enter a new record.
No there is not, not in the Table or the Queries.
  Reply With Quote
Old 06-12-2013, 02:58 PM   #11
There's an evil monkey in my truck
Evil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shed
 
Evil Monkey's Avatar
Navy: Tradevman (flight simulator tech), Aviation Electrician (F-14a), Computer Scientist(civ)
Name: Robert
Joined: Aug 2007, #2352
Location: Escondido, CA
Age: 52
Gender: Male
Posts: 8,251
Evil Monkey's Tacoma Gallery
Quote:
Originally Posted by Chipskip View Post
No there is not, not in the Table or the Queries.
How many records do you already have in your table? Is it feasible to add a new column of type Autonumber?
  Reply With Quote
Old 06-12-2013, 03:17 PM   #12
There's an evil monkey in my truck
Evil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shed
 
Evil Monkey's Avatar
Navy: Tradevman (flight simulator tech), Aviation Electrician (F-14a), Computer Scientist(civ)
Name: Robert
Joined: Aug 2007, #2352
Location: Escondido, CA
Age: 52
Gender: Male
Posts: 8,251
Evil Monkey's Tacoma Gallery
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)
  Reply With Quote
Old 06-13-2013, 06:38 AM   #13
Chipskip [OP] Chipskip is offline
N7MCS
Chipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shed
 
Chipskip's Avatar
Army: 31R, HHB, 2/43 ADA Battalian, 108th ADA Brigade, XVIII Airborne Corps
Name: Chip
Joined: Aug 2010, #42519
Location: Phoenix, AZ
Age: 35
Gender: Male
Posts: 5,583
Chipskip's Tacoma Gallery
Quote:
Originally Posted by Evil Monkey View Post
Is there an Autonumber column? It would be a number field that increments automatically when you enter a new record.
Quote:
Originally Posted by Evil Monkey View Post
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)
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
  Reply With Quote
Old 06-13-2013, 08:35 AM   #14
There's an evil monkey in my truck
Evil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shed
 
Evil Monkey's Avatar
Navy: Tradevman (flight simulator tech), Aviation Electrician (F-14a), Computer Scientist(civ)
Name: Robert
Joined: Aug 2007, #2352
Location: Escondido, CA
Age: 52
Gender: Male
Posts: 8,251
Evil Monkey's Tacoma Gallery
Quote:
Originally Posted by Chipskip View Post
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
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/For...-c359b3dc3ac5/
  Reply With Quote
Old 06-13-2013, 08:45 AM   #15
Chipskip [OP] Chipskip is offline
N7MCS
Chipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shed
 
Chipskip's Avatar
Army: 31R, HHB, 2/43 ADA Battalian, 108th ADA Brigade, XVIII Airborne Corps
Name: Chip
Joined: Aug 2010, #42519
Location: Phoenix, AZ
Age: 35
Gender: Male
Posts: 5,583
Chipskip's Tacoma Gallery
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.
  Reply With Quote
Old 06-13-2013, 10:34 AM   #16
Chipskip [OP] Chipskip is offline
N7MCS
Chipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shed
 
Chipskip's Avatar
Army: 31R, HHB, 2/43 ADA Battalian, 108th ADA Brigade, XVIII Airborne Corps
Name: Chip
Joined: Aug 2010, #42519
Location: Phoenix, AZ
Age: 35
Gender: Male
Posts: 5,583
Chipskip's Tacoma Gallery
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.
  Reply With Quote
Old 06-13-2013, 11:09 AM   #17
There's an evil monkey in my truck
Evil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shed
 
Evil Monkey's Avatar
Navy: Tradevman (flight simulator tech), Aviation Electrician (F-14a), Computer Scientist(civ)
Name: Robert
Joined: Aug 2007, #2352
Location: Escondido, CA
Age: 52
Gender: Male
Posts: 8,251
Evil Monkey's Tacoma Gallery
And they're doing this all with Access 2010? Or is that just what you use?
  Reply With Quote
Old 06-13-2013, 11:33 AM   #18
Chipskip [OP] Chipskip is offline
N7MCS
Chipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shed
 
Chipskip's Avatar
Army: 31R, HHB, 2/43 ADA Battalian, 108th ADA Brigade, XVIII Airborne Corps
Name: Chip
Joined: Aug 2010, #42519
Location: Phoenix, AZ
Age: 35
Gender: Male
Posts: 5,583
Chipskip's Tacoma Gallery
Quote:
Originally Posted by Evil Monkey View Post
And they're doing this all with Access 2010? Or is that just what you use?
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.
  Reply With Quote
Old 06-13-2013, 11:39 AM   #19
Chipskip [OP] Chipskip is offline
N7MCS
Chipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shedChipskip is one of the sharper tools in the shed
 
Chipskip's Avatar
Army: 31R, HHB, 2/43 ADA Battalian, 108th ADA Brigade, XVIII Airborne Corps
Name: Chip
Joined: Aug 2010, #42519
Location: Phoenix, AZ
Age: 35
Gender: Male
Posts: 5,583
Chipskip's Tacoma Gallery
So, when I try to run a new query with...

Quote:
SELECT First([qry_InfoPACCAck&TTA].TicketNumber), [qry_InfoPACCAck&TTA].key
FROM [qry_InfoPACCAck&TTA];
I get an error, " You tried to execute a query that does not include the specified expression "key" as part of an aggregate function."
  Reply With Quote
Old 06-13-2013, 11:46 AM   #20
There's an evil monkey in my truck
Evil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shedEvil Monkey is one of the sharper tools in the shed
 
Evil Monkey's Avatar
Navy: Tradevman (flight simulator tech), Aviation Electrician (F-14a), Computer Scientist(civ)
Name: Robert
Joined: Aug 2007, #2352
Location: Escondido, CA
Age: 52
Gender: Male
Posts: 8,251
Evil Monkey's Tacoma Gallery
Quote:
Originally Posted by Chipskip View Post
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."
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.
  Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads
Thread Thread Starter Forum Replies Last Post
2010 access cab subwoofer removal MikeZQ 2nd Gen. Tacomas 16 09-14-2013 07:00 PM
2010 Access Cab Manual mallard75 4 Cylinder 27 02-14-2013 12:02 PM
2010 tacoma access cab 4x4 bighungitacoma 2nd Gen. Tacomas 0 09-29-2012 10:29 PM
Lifted 2010 Access Cab Edrin55 2nd Gen. Tacomas 86 02-03-2010 08:48 PM
My 2010 Access Cab, so far. RWS_350MAG 2nd Gen. Tacomas 11 12-01-2009 06:27 AM


Copyright © 2014 Tacoma Forum. Tacoma World is not owned by, or affiliated with Toyota Motor Corporation.