Find Unique Values ONE Column - Access SQL

[emperial]

This forum has been so helpful last time, thought I would give it another try!

Have gotten to the point where I can't see the forest for the trees as I have the feeling the solution is quite simple!

I have a table with the following columns: job, name of the workmen, days of the week, rate, adj, total and comments.

To give an idea of how, a part of, the table looks like, please see below:

Job   Workman
11    11 - PROJECT NAME
11    Workman 1
11    Workman 2
11    Workman 3
12    12 - PROJECT NAME
13    13 - PROJECT NAME
13    Workman 4
14    14 - PROJECT NAME
14    Workman 5
14    Workman 6
14    Workman 7
15    15 - PROJECT NAME

Now I am trying to delete the following records:

12 12 - PROJECT NAME
15 15 - PROJECT NAME

Basically when there are no names under a project, it means no one has worked there and we don't need to see them.

The code I have at the moment is:

SELECT DISTINCT [CISPAYE].Job 
FROM [CISPAYE]
GROUP BY Job

(I use this code to select the required records and then turn it into a delete query).

However, this just returns only ONE column with ALL distinct values (e.g. 11, 12, 13, 14, 15)

I would like to keep the fields when their value appears more than once basically (PLUS the other columns weekdays, rate, adj, etc.). I hope this makes sense!!

If anyone has any suggestions that would be greatly appreciated!

Many thanks,
emperial

[twinnyfo]

First, your Table is definitely not normalized. I don't see how you can have a table with the name of the job and name of the workmen in the same column. I can't advise on this until you square away your tables first, which is my best advice.

Please see this link for advice on how to normalize your tables:

Database Normalization

[emperial]

Hi Twinnyfo,

Thanks for helping me out again!

Sorry if I didn't make it clear, the job number and workmen are two separate columns (I did try to insert some 'tabs' in the example table, but they got deleted after posting).

Does this help at all?

[emperial]

Please find attached a screenshot of the database and the records I would like to have deleted

[zmbd]

Hello,
First start with a select query to return only those records you want effected.
In this case it appears that you want

[job] = 12 or 15 
   AND
      [Workman] = "12 - PROJECT NAME" 
         OR "15 - PROJECT NAME"

Try that first...

Once you have your select query working then convert to a delete action query.

[emperial]

Hi ZMBD,

Thank you very much for the above.

It does work indeed!

However, as the projects keep changing, say, at the moment no one is working at job "12" and "15", but next week it might be "14" and "13" that I would need to delete. So is there a way for Access to check whether a number occurs only once, and if so, deletes that record from the table?

Thank you very much again (also for moderating my post ^^)

[zmbd]

Hmm,

Go to your select query,
Drag [Job] down twice to the table.
Click on the totals button
Set the first [JOB] to group by
set the second [JOB] to count
set the condition to "=1"

On the dataset in the OP you should only see: 12 and 15

[JOB]
  12
  15

so if in the OP the entries on lines 3 - 5 were deleted then you would have:

[JOB]
  11
  12
  15

BACK UP YOUR DATABASE!
You can now convert to a delete query.

The issue here is that you shouldn't be doing this at all... your database doesn't appear to be normalized.

[emperial]

Hi ZMBD,

Thanks very much for the suggestion! Am going to give this a try.

Unfortunately I am not the end user which makes it quite difficult (and frustrating!) to set up a normalized db.

Thanks again and I'll let you know how I get on.

@Twinnyfo, apologies, I ready your first reply wrong. I thought it said 'job number and name of the workmen'. I do understand it's unfortunately not normalized but if I get this to work the db does everything it should be doing.

[emperial]

Hi all,

Just in case anyone is interested, this is probably against all rules, but I have used the following steps to get what I wanted:
- Used Job Count (and Group) to show records that would need to be deleted
- Appended these jobnumbers to a table with an Append query
- Used 'Find Unmatched Query Wizard' (so all records that didn't match the jobnumbers from the appended table would show)
- Saved as a query

@ZMDB, the Count worked in the query, however I couldn't turn it into a Delete query, hence the above steps. Probably wouldn't have been unable to figure it out if it wasn't for your suggestion. Thanks a bunch!

[NeoPa]

Everything all the others have said about normalisation.

For your delete query, and this is certainly going to blow up in your face later as next week you will have similar records and the count will only ever be =1 again for jobs that have never had any work done on them, try the following simpler approach :

DELETE
FROM   [Example]
WHERE  ([Job] In(SELECT   [Job]
                 FROM     [Example]
                 GROUP BY [Job]
                 HAVING   (Count(*)=1)))

NB. It will blow up for you whether you use this approach or any other as you have based the logic on a fallacy. The fallacy is that logic that works for a single week will continue to work when you have multiple weeks' worth of data in the table.

[zmbd]

OK, should have taken this one step further...

Lets take simple table
[Table1]![pk],[Table1]![Field1]

SELECT Table1.Field1
FROM Table1
GROUP BY Table1.Field1
HAVING (((Count(Table1.Field1))=1));

Ok, what this does is the same thing I did in the prior post.
However, as you discovered this doesn't directly convert to the delete.

With the append query you almost have it...
What I had hoped was that you would have noticed when creating the agregate query that you could then create a second select query and use the agregate as a conditional to feed to the select query...

SELECT 
   Table1.Field1 AS F1
FROM 
   Table1
WHERE 
   (((Table1.Field1) 
      In 
      (SELECT [Field1] 
       FROM [Table1] 
         As Tmp 
       GROUP BY [Field1] 
       HAVING Count(*)=1 )));

This will then convert to your delete query...

I'll leave it to you to replace finish the SQL...

Note: This is only a short term solution... you need to normalize the database... enduser be flogged!

(Rabbit will most likely have a much more elegant solution, He's my SQL hero - I'm often a bit of an SQL hack!)

[emperial]

Thank you both for the suggestions!

That is indeed much more simple and have got it working now.

I used NeoPa's code.

I have only started using Access about 2 months ago, so I'm afraid I know what it needs to do but not sure what the best way (read: according to db rules!) is of getting it done!

Haha thanks ZMBD for your comment regarding the endusers. I'll make sure to let them know ;)

Thank you both very much for your time looking into this, very happy Bytes.com user once again!!

[NeoPa]

Emperial:
very happy Bytes.com user once again!!

That's what we like to see :-)