Urgent Help needed with table create Query.

[hardrock]

Hi all, i need some help constructing a query that will create a table for me that does not include the Type value of 'promotion' if the same ID value exists elsewhere within the query.

For instance, in the example below, i would want to delete the first entry containing 'Promotion' because ID2001 exists in two other places. However, if only one instance of ID2001 'Promotion' existed in my query, i would want to keep that entry. I would prefer to write all my data to a new table called "Test", This query has to sort out about 1000 entries when executed, otherwise i'd do it manually.

I'm pulling my hair out with this one, so could someone help me?

ID Company Name Type
ID2001 Disc plc John Promotion
ID2001 Disc plc Paul Agent
ID2001 Disc plc George Agent

 

[ajetrumpet]

For instance, in the example below, i would want to delete the first entry containing 'Promotion' because ID2001 exists in two other places.

I'm assuming you want only one record per ID number here....if this is what you want, use a subquery to filter out the uniques...

SELECT ID, Company, Name, Type
FROM [table]
WHERE ID IN (SELECT ID FROM [table] GROUP BY ID HAVING COUNT(ID)=1);

However, if only one instance of ID2001 'Promotion' existed in my query, i would want to keep that entry.

If the only fields that contain different values are the "ID" and "TYPE" fields, the following statement would work to satisfy this inquiry....(make-table query):

SELECT DISTINCTROW ID, Company, Name, Type
FROM [table];

 

[hardrock]

Clarification

Thanks for the reply.. The query that contains the data may have many repetitions of the same ID number, for instance ID2001 may occur several times with different customer names and Types. I want to keep all these records I also want to keep records where Type = 'Promotion' if it has a unique ID number. If however there is more than 1 instance of the same ID number and Type promotion is found within the same ID number range, it is that record i want to filter out or delete. When it has done this process, then i want to create a new table from the new data. I hope this is clearer. Thanks again

 

[ajetrumpet]

Re: Clarification

If there is more than 1 instance of the same ID number and Type promotion is found within the same ID number range, it is that record i want to filter out or delete. When it has done this process, then i want to create a new table from the new data. I hope this is clearer. Thanks again

You have multiple steps involved here...you will probably have to make a couple of different queries for this and append one's filtered records to the other one....If you're not sure how to go about this, you should read up on the "types" of queries available to you in the program's help menu. It wouldn't hurt to post a .zip copy of your DB either, if it's not too sensitive...