Can I have your help with a query related problem ?

[MarkStorer]

Hi All

This is probably an easy fix for you experts out there, but the solution is beyond a newbie like me.

I have a table containing several customer fields - with the key field 'Customer' having 3 possible permutations for each customer ie :

'Customer_ID Name Info
10149 ABC Manufacturing Co Enquiry account
10149M ABC Manufacturing Co Machine account
10149S ABC Manufacturing Co Spares Account

The main table can have any combination of these records (ie 1, 2 or all 3 exist in the same table for the same customer)

I need to not return the Customer_ID with the 'S' Suffix.
I want to use the query to return the Customer_ID with the 'M' Suffix UNLESS it doesn't exist, in which case the query should return the Customer_ID without any Suffix.
If both the 'M' & 'No Suffix' records exist, then return the 'M' Suffix

Note: I can't use the 'Name' field to refine the query, as the name is typed by the operator in differing ways (ie ABC Manufacturing Co or ABC Manf Company).

I guess the crux of it is to :

1. Search for the 'M' Suffix' records THEN
2. Search for the 'no Suffix' records IF no 'M' Suffix records exist.

That way I'll get all the 'M' Suffix records listed, together with the 'no Suffix' records (which don't have 'M' Suffix counterparts).

Hope this explains things clearly enough for you.


Thankyou in advance
Mark

[Rabbit]

Seeing as how they are the same people, you would have done better to have a Customer_ID field AND a Suffix field.

This is a three query process. Have one query return all M Suffix records. In this query, also create a column that strips the M off. Have another query that uses an outer join to select the ones without a suffix. Then union them together.

[MarkStorer]

Thankyou for you help !

Sorry, but I'm a little confused with the your answer :-(

1. I have constructed the 1st query (searching for the M suffix in the Customer_ID field & stripping the 'M' off it in another column.

2. I have created a 2nd query which searches the main table & looks for the Customer_ID without the 'M'.

3. the 3rd query (union) doesn't appear to combine query 1 & 2 together (it just seems to return the results of the first part of the SQL statement (ie before the 'UNION ALL' bit.


In essence, I get query 1 to give me a list of customer_ID with 'M's; Query 2 gives me a list of customer_ID without 'M's, but I can't recombine them in a UNION properly.

Is it possible you can give me a little more detail ?

cheers

Mark

[Rabbit]

Thankyou for you help !

Sorry, but I'm a little confused with the your answer :-(

1. I have constructed the 1st query (searching for the M suffix in the Customer_ID field & stripping the 'M' off it in another column.

2. I have created a 2nd query which searches the main table & looks for the Customer_ID without the 'M'.

3. the 3rd query (union) doesn't appear to combine query 1 & 2 together (it just seems to return the results of the first part of the SQL statement (ie before the 'UNION ALL' bit.


In essence, I get query 1 to give me a list of customer_ID with 'M's; Query 2 gives me a list of customer_ID without 'M's, but I can't recombine them in a UNION properly.

Is it possible you can give me a little more detail ?

cheers

Mark

Did you do the second query with the outer join? What you should do is join on the New ID field in the first query to the ID field in the second query using a right join in this case. Then you keep the ones where the New ID field is Null. And you keep only the ones without an M or an S at the end. This will give you only those records that don't have an M suffix ID.

I think I may have missed a step. When you go to Union two queries, they need to have the same columns. So you might have to drop that New ID field first.

[MarkStorer]

Perfect !

Thankyou for coming back to me; it's all sorted now !

I really appreciate your help - it gets kinda lonely just bashing your head against the Access Manual..

Cheers

Mark

[Rabbit]

Not a problem, good luck.