Finding specific customer depending on Product type

[BernardAtherton]

Hi,

I would like help in finding a way to find the following by using a query.

I have a table that contains customer number and product. For example

Cust No Product
1 Current Account
1 Credit Card
1 Savings Account
2 Credit Card
3 Current Account
3 Credit Card

I am trying to write a query that finds customers that JUST have the products I specify.

For example ...

'What customers just have a Credit Card'. The correct response should be just customer 2.

'What customers have a Current Account AND Credit Card'. The correct response should be Customer 3 and NOT customer 1 as they have a savings account.

Any help would be gratefully appreciated. I have tried writing queries but they do not work!

Thanks

Bernard

 

[Fizzio]

This depends on how rigid you want to be with your selection criteria. If you can have multiple choices as you seem to want ie

Credit card only
Credit Card and Savings Account
etc,

you would be better off creating the SQL string dynamically to populate the recordset. There are loads of examples in the forum if you search here. If you cannot find anything, get back.

Oh and by the way, the example questions you gave have been answered incorrectly.

1. 0 as even 2 has a Savings account as well. This is important as how you ask the question will give you a different set of results

2. 1 & 3

 

[namliam]

Just to give you a quick idea and show you it CAN be done.

Regards

The Mailman

 

[Fizzio]

namilam,

The demo has the incorrect table names in the query and so will not return the correct recordset.

The query will work, yes, using a self join but the QBE grid becomes complicated and unweildy and the SQL becomes very complex (although it will in theory run faster than a simpler dynamically produced SQL) as you need to account for Null values in a form as well as possible selected values. I find that a dynamic string makes it a little easier to program with a series of if...then and cleaner.

I'd like any other opinions on this one though.

 

[BernardAtherton]

Thank you for your speedy responses.

Namliam, I downloaded your attachment and got it working many thanks. Please note I am a novice and I am wondering how the query would be changed to just find customers that only have a Current and a Credit card. Any further assitance greatly appreciated. I tried adding the AND "Credit" to the SQL but it made no difference.

Fizzio, thank you for your advice, in your first reply I am afraid I do not understand what it is you are suggesting BUT I will search the forum and try to learn! Nothing like a challenge.

Once again I am extremely grateful that you have/are trying to help me.

Cheers

Bern

 

[namliam]

namilam,

The demo has the incorrect table names in the query and so will not return the correct recordset.

The query will work, yes, using a self join but the QBE grid becomes complicated and unweildy and the SQL becomes very complex (although it will in theory run faster than a simpler dynamically produced SQL) as you need to account for Null values in a form as well as possible selected values. I find that a dynamic string makes it a little easier to program with a series of if...then and cleaner.

I'd like any other opinions on this one though.

What's wrong with my demo? I dont understand. I agree, in the QBE its a pain in the royal butt. But building it from VBA isnt that hard....<next you/he will be asking me to do so...> but ok....

Regards

 

[namliam]

Thank you for your speedy responses.

Namliam, I downloaded your attachment and got it working many thanks. Please note I am a novice and I am wondering how the query would be changed to just find customers that only have a Current and a Credit card. Any further assitance greatly appreciated. I tried adding the AND "Credit" to the SQL but it made no difference.

Fizzio, thank you for your advice, in your first reply I am afraid I do not understand what it is you are suggesting BUT I will search the forum and try to learn! Nothing like a challenge.

Once again I am extremely grateful that you have/are trying to help me.

Cheers

Bern

Q: Who has a credit card?
A: 1,2,3
Q: Who has ONLY a credit cards
A: 1
That is what Fizzio is talking about, Semantics on "Exact" language. Tho he is right, customers can be tricky to deal with. So be sure you got the question fully right!

Anyway find attached a sample DB with an example with 2 must haves... <only those 2>

Regards

 

[Fizzio]

No problem. Namilam's method will work well if you only have a few fields to compare. My comment was really for if you have several fields to filter as the SQL becomes horrendously complex. If you need any more help with the Dynamic SQL method, search the forums here but shout iof you are stuck.

 

[Fizzio]

What's wrong with my demo? I dont understand. I agree, in the QBE its a pain in the royal butt. But building it from VBA isnt that hard....<next you/he will be asking me to do so...> but ok....

Regards

No criticism implied namilam - just that the table name was incorrect in your query that is all and so returned an error when the query was run, not a populated recordset.

 

[BernardAtherton]

Thanks for the second attachment, it is superb.

I agree the wording needs to be precise. What you have produced is the kind of things I am after. If I ask for customer numbers where they ONLY have a Current and Savings that is all I want. I am not interested in customers that have Current,Savings and Credit.

So I want the results to always return the customers that match exactly what I am after.

I hope this helps clear up my selection criteria

Cheers

Bernard

 

[namliam]

No criticism implied namilam - just that the table name was incorrect in your query that is all and so returned an error when the query was run, not a populated recordset.

I dont get any errors, niether does Bernard i suppose cause he aint complaining. Instead he is happy.Now if only he can get it flexible from VBA (a must i think) he will be extatic!

What version are you using?

Try replacing the [select...]. by (...)
Note the extra point after the last bracket!! [...].

Regards

 

[Fizzio]

A2000 on Win 2000

Must just be me then

 

[namliam]

A2002 (AXP) with Win2000

Saving as a A2000. Might be a slight miscommunication there....

Try the above... It should work on any A....

Regards

P.S. I would suggest its a UK problem (you ppl try and do everything different) but Bernard is from UK too

 

[Fizzio]

P.S. I would suggest its a UK problem (you ppl try and do everything different) but Bernard is from UK too

Cannot disagree with that - nothing better than a bit of stiff upper lip.

Anyway, cannot get either to work - but as you say it doesn't matter, I dont have the problem