You an Access 2010 Guru? Help please.

I created a field call "Key" in my table and added it to the qry_InfoPACCAck&TTA query.

 

Try a different name for the field. Like pkey. It might be a reserved word in Access (like "Date").

 

I just looked it up. It is a reserved word. Change it to pkey.

 

This worked:

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

 

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).

 

I had to uncheck the show box for pkey to work on that query. So I don't have it to Join.

 

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]

 

I'm making a sample database to check the query. Hold Please...

 

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;

 

^ 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.

 

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.

 

Yeah, I tried that one, but it only comes back with one record.

I tried it in a clean query.

 

Are you getting the pkey with the one record?
A ticketnumber and a pkey?

 

tone record showing one ticketnumber and pkey

 

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.

 

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?

 

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.

 

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

 

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.

 

Right, I am completely understanding what you are saying about DISTINCT now. Thank you.