Selecting records based on multiple criteria

[bazzason]

hi,

i am querying a single table that contains card numbers and corresponding transaction codes (amongst other things). a single card number can have multiple transaction codes.

i want to select all card numbers that have both 0100 and 0802 transaction codes.

if i use a select query with

="0100" Or "0802"

as the criteria, it returns all card numbers that have either 0100 or 0802 transaction types. if i then change the query to crosstab and group by card number, it is clear that some cards have both transaction types.

when i try to change the criteria to

="0100" And "0802"

it returns nothing at all.

i'm sure the solution is something really simple - any idea what i'm doing wrong?

 

[neileg]

This code

="0100" And "0802"

is looking for individual records that have both 0100 and 0802 types. Since each record has only one type, this is always going to return no records.

To know what to do next depends on your data. Can you be sure that there is only ever one record for a card with the type? in other words, is there either no record or one record but never more than one?

 

[bazzason]

hi neil,

it is a transactional database of gift cards. each gift card can be used multiple times, and each time it is used it records one entry in the database. this entry will always have a transaction type (it could be one of a dozen different codes). the transaction types refer to Activation (0100), Redemption (0200), Top-Up (0300), Void (0802) etc etc.

a single gift card can have multiple activations, multiple voids, a single activation and a single redemption, or a combination of several different transaction types, including top ups, voids and redemptions.

i am only interested in cards that have used both the codes 0100 and 0802 at some point in their lifespan. it doesn't matter if there are other transaction types associated with that particular gift card number, but i am not interested in the card unless both 0100 and 0802 types are present.

does that make sense???

cheers

 

[Brianwarnock]

I think you will need 3 queries
First selects all 0100
Second all 0802

Third joins 1 and 2 on gift card ID

Brian

 

[neileg]

I agree with Brian. The third query needs to use an inner join to ensure you only get a result where the card exists in both queries. You should set the third query to return unique values otherwise you'll get multiple results for the same card.

 

[Simon_MT]

What about using TransType in ("0100","0802")

Simon

 

[bazzason]

Thanks, that has worked (the three query option)

 

[KKilfoil]

You could also have gotten away with 2 queries:

The first one selects only card#'s with a matching 0100 transaction record. (You can use the DISTINCT operator to eliminate multiple returns)

The second uses the result of the first, and selects only those with an 0802 matching record.