Create a query that examines separate groups, not rows, within a t

[Peter at ARC]

I have a table with multiple transactions in it. Each transaction will have
from 2 to 12 records, all with the same transaction number. I need to create
a query which examines all the records for each transaction and selects the
following: all transactions which have an inputted value for the PCC field
but do not have an inputted value in the LC field. The PCC value for each
transaction is always the same, but the LC field varies within each
transaction. So far I've only been able to create queries which examine each
record or row instead of all of the rows or records for each transaction.

 

[Douglas J. Steele]

Can you give an example of what you're trying to do?

 

[John Spencer]

If you are looking for transactions that have one or more records where
LC is null and at least one record in the group where PCC has a value
then the following should work

SELECT TransactionID
FROM Transactions
WHERE TransactionID in
(SELECT TransactionID
FROM Transactions as T2
WHERE PCC Is Not Null
AND LC Is Null)

If you are looking for transactions where ALL the records in the group
have the same value for PCC and one or more of the records has no value
in the LC field - that is a different query

If you are looking for something else, then you need to be a bit more
specific in the description of what you want.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Peter at ARC]

American Airlines is the plating carrier (PCC field) The lifting carrier
code for American Airlines is AA, which does not appear in the LC field in
any of the segments in this ticket.

Seg Orig Dest LC PCC Transaction
1 DCA ORD UA 001 355
2 ORD NRT CX 001 355
3 NRT PVG CI 001 355

 

[Peter at ARC]

Here's an example:
American Airlines is the plating carrier (PCC field) The lifting carrier
code for American Airlines is AA, which does not appear in the LC field in
any of the segments in this ticket.

Seg Orig Dest LC PCC Transaction
1 DCA ORD UA 001 355
2 ORD NRT CX 001 355
3 NRT PVG CI 001 355

 

[John Spencer]

Ok, so do you have a table somewhere that tells me that if the PCC = 001 then
the LC is AA? AND if you do what do you want returned in the query?

I think you want all records for a transaction where none of the LC codes
equals the LC code for the PCC. OR do you just want a list of the transaction
codes that don't have the an LC that corresponds to the PCC for the transaction.

With a small set of records the following should work
SELECT DISTINCT Transaction
FROM Transactions
WHERE Transaction Not IN
(SELECT Transaction
FROM Transactions INNER JOIN SomeTable
ON Transactions.PCC = SomeTable.PCC
WHERE Transactions.LC = SomeTable.LC)

With a larger set (the following should work better, but it will probably not
be updatable).

SELECT DISTINCT Transaction
FROM Transactions LEFT JOIN
(SELECT Transaction
FROM Transactions INNER JOIN SomeTable
ON Transactions.PCC = SomeTable.PCC
WHERE Transactions.LC = SomeTable.LC) As A
ON Transactions.Transaction = A.Transaction
WHERE A.Transaction is Null

If you want more than just the transaction numbers, remove the DISTINCT in the
query and add the additional fields


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Peter at ARC]

My main table is "Tickets sent to carriers". I have another table titled
CODES 4 which has fields for the carrier name, the LC code, and the PCC code.
Yes, I need a list of the transaction codes that don't have the an LC that
corresponds to the PCC for the transaction. In SQL view I wrote the
following:

SELECT DISTINCT TRAN FROM [Tickets sent to carriers] WHERE TRAN Not In
(SELECT TRAN FROM [Tickets sent to carriers] INNER JOIN [CODES 4] ON [Tickets
sent to carriers].PCC=[CODES 4].[3 digit] WHERE [Tickets sent to
carriers].LC=[CODES 4].[2 letter])

The query ran but it did not return any hits. I know that some transactions
meet my criteria. Any ideas?

a list of the transaction

 

[John Spencer]

If the subquery could return a null value for TRAN then you would never get
any records returned. So try adding to the where clause in the subquery.

SELECT DISTINCT TC.TRAN
FROM [Tickets sent to carriers] AS TC
WHERE TRAN Not In
(SELECT T2.TRAN
FROM [Tickets sent to carriers] As T2 INNER JOIN [CODES 4]
ON [T2].PCC=[CODES 4].[3 digit]
WHERE T2.LC=[CODES 4].[2 letter]
AND T2.TRAN is NOT NULL)

Since your field and table names contain spaces, you cannot use the second
method directly. You would need to use two queries - one to get records where
the match IS correct and a second one (an unmatched query) to get those tran
numbers that don't show up in the first query.

Query one would get all the records where things matched up correctly.

SELECT T2.TRAN
FROM [Tickets sent to carriers] As T2 INNER JOIN [CODES 4]
ON [T2].PCC=[CODES 4].[3 digit]
WHERE T2.LC=[CODES 4].[2 letter]

If that gave you all the records where PCC and LC matched correctly, Then you
could use that in an unmatched query to get your problem records
SELECT DISTINCT TRAN
FROM [Tickets sent to carriers] as T LEFT JOIN qSavedQuery
ON T.TRAN = QSavedQuery.Tran
WHERE qSavedQueryTran is null
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

My main table is "Tickets sent to carriers". I have another table titled
CODES 4 which has fields for the carrier name, the LC code, and the PCC code.
Yes, I need a list of the transaction codes that don't have the an LC that
corresponds to the PCC for the transaction. In SQL view I wrote the
following:

SELECT DISTINCT TRAN FROM [Tickets sent to carriers] WHERE TRAN Not In
(SELECT TRAN FROM [Tickets sent to carriers] INNER JOIN [CODES 4] ON [Tickets
sent to carriers].PCC=[CODES 4].[3 digit] WHERE [Tickets sent to
carriers].LC=[CODES 4].[2 letter])

The query ran but it did not return any hits. I know that some transactions
meet my criteria. Any ideas?

a list of the transaction

codes that don't have the an LC that corresponds to the PCC for the transaction

Ok, so do you have a table somewhere that tells me that if the PCC = 001 then
the LC is AA? AND if you do what do you want returned in the query?

I think you want all records for a transaction where none of the LC codes
equals the LC code for the PCC. OR do you just want a list of the transaction
codes that don't have the an LC that corresponds to the PCC for the transaction.

With a small set of records the following should work
SELECT DISTINCT Transaction
FROM Transactions
WHERE Transaction Not IN
(SELECT Transaction
FROM Transactions INNER JOIN SomeTable
ON Transactions.PCC = SomeTable.PCC
WHERE Transactions.LC = SomeTable.LC)

With a larger set (the following should work better, but it will probably not
be updatable).

SELECT DISTINCT Transaction
FROM Transactions LEFT JOIN
(SELECT Transaction
FROM Transactions INNER JOIN SomeTable
ON Transactions.PCC = SomeTable.PCC
WHERE Transactions.LC = SomeTable.LC) As A
ON Transactions.Transaction = A.Transaction
WHERE A.Transaction is Null

If you want more than just the transaction numbers, remove the DISTINCT in the
query and add the additional fields


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Peter at ARC]

I reworded the query as you suggested. It ran and I haven't found either any
false negatives or false positives. Thanks a lot for your help.

If the subquery could return a null value for TRAN then you would never get
any records returned. So try adding to the where clause in the subquery.

SELECT DISTINCT TC.TRAN
FROM [Tickets sent to carriers] AS TC
WHERE TRAN Not In
(SELECT T2.TRAN
FROM [Tickets sent to carriers] As T2 INNER JOIN [CODES 4]
ON [T2].PCC=[CODES 4].[3 digit]
WHERE T2.LC=[CODES 4].[2 letter]
AND T2.TRAN is NOT NULL)

Since your field and table names contain spaces, you cannot use the second
method directly. You would need to use two queries - one to get records where
the match IS correct and a second one (an unmatched query) to get those tran
numbers that don't show up in the first query.

Query one would get all the records where things matched up correctly.

SELECT T2.TRAN
FROM [Tickets sent to carriers] As T2 INNER JOIN [CODES 4]
ON [T2].PCC=[CODES 4].[3 digit]
WHERE T2.LC=[CODES 4].[2 letter]

If that gave you all the records where PCC and LC matched correctly, Then you
could use that in an unmatched query to get your problem records
SELECT DISTINCT TRAN
FROM [Tickets sent to carriers] as T LEFT JOIN qSavedQuery
ON T.TRAN = QSavedQuery.Tran
WHERE qSavedQueryTran is null
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

My main table is "Tickets sent to carriers". I have another table titled
CODES 4 which has fields for the carrier name, the LC code, and the PCC code.
Yes, I need a list of the transaction codes that don't have the an LC that
corresponds to the PCC for the transaction. In SQL view I wrote the
following:

SELECT DISTINCT TRAN FROM [Tickets sent to carriers] WHERE TRAN Not In
(SELECT TRAN FROM [Tickets sent to carriers] INNER JOIN [CODES 4] ON [Tickets
sent to carriers].PCC=[CODES 4].[3 digit] WHERE [Tickets sent to
carriers].LC=[CODES 4].[2 letter])

The query ran but it did not return any hits. I know that some transactions
meet my criteria. Any ideas?

a list of the transaction

codes that don't have the an LC that corresponds to the PCC for the transaction

Ok, so do you have a table somewhere that tells me that if the PCC = 001 then
the LC is AA? AND if you do what do you want returned in the query?

I think you want all records for a transaction where none of the LC codes
equals the LC code for the PCC. OR do you just want a list of the transaction
codes that don't have the an LC that corresponds to the PCC for the transaction.

With a small set of records the following should work
SELECT DISTINCT Transaction
FROM Transactions
WHERE Transaction Not IN
(SELECT Transaction
FROM Transactions INNER JOIN SomeTable
ON Transactions.PCC = SomeTable.PCC
WHERE Transactions.LC = SomeTable.LC)

With a larger set (the following should work better, but it will probably not
be updatable).

SELECT DISTINCT Transaction
FROM Transactions LEFT JOIN
(SELECT Transaction
FROM Transactions INNER JOIN SomeTable
ON Transactions.PCC = SomeTable.PCC
WHERE Transactions.LC = SomeTable.LC) As A
ON Transactions.Transaction = A.Transaction
WHERE A.Transaction is Null

If you want more than just the transaction numbers, remove the DISTINCT in the
query and add the additional fields


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Peter at ARC wrote:
Here's an example:
American Airlines is the plating carrier (PCC field) The lifting carrier
code for American Airlines is AA, which does not appear in the LC field in
any of the segments in this ticket.

Seg Orig Dest LC PCC Transaction
1 DCA ORD UA 001 355
2 ORD NRT CX 001 355
3 NRT PVG CI 001 355