Getting values from a column A that has same values in column B and C

[Rupa]

Hey all

I have three fields as follows:

Group, RD, Schedule

AD, LRC1, Mon-Fri 07:30-17:00
AD, LRC2, Mon-Fri 07:30-13:00
FM, LRC1, Mon-Fri 07:30-17:00
FM, LRC2, Mon-Fri 07:30-13:00
JK, LRC1, Weekdays
JK, LRC2, All day
JK, Main1, All day

I would like to get all the groups that are exactly the same so that they both have the same RD and Schedule. So I would like to get:

Group, RD, Schedule

AD, LRC1, Mon-Fri 07:30-17:00
AD, LRC2, Mon-Fri 07:30-13:00
FM, LRC1, Mon-Fri 07:30-17:00
FM, LRC2, Mon-Fri 07:30-13:00

because they both have LRC1 and LRC2 AND both the schedules are exactly the same.

The purpose of this exercise is to delete groups that have the same RD and schedules. There's no need to have two groups with the same contents.

This might make it better to understand:

Group AD has the following:
LRC1, Mon-Fri 07:30-17:00
LRC2, Mon-Fri 07:30-13:00


Group FM has also got:
LRC1, Mon-Fri 07:30-17:00
LRC2, Mon-Fri 07:30-13:00

So I don't want to duplicate groups. I want to see which ones are the same so I can delete either one.

Hope this makes sense.

Is there a way to do this? Any response will be highly appreciated.

Many thanks,

Rupa

 

[WayPay]

Something like this?

SELECT YourTable.Group,
    YourTable.RD,
    YourTable.Schedule
FROM YourTable
    INNER JOIN YourTable AS YourTable_1
    ON (YourTable.Schedule = YourTable_1.Schedule)
    AND (YourTable.RD      = YourTable_1.RD)
GROUP BY YourTable.Group,
    YourTable.RD,
    YourTable.Schedule
HAVING (((COUNT(*))>1));

BTW it might be a good idea to not have a column named 'Group'.

 

[Rupa]

Many thanks WayPay. This brings up all the rows in the table. Any other suggestions?

Many thanks once again,

Rupa

 

[Brianwarnock]

I thought ,"that should work" so I did a little test

SELECT Table3.gr, Table3.rd, Table3.scd
FROM Table3 INNER JOIN Table3 AS Table3_1 ON (Table3.scd = Table3_1.scd) AND (Table3.rd = Table3_1.rd)
GROUP BY Table3.gr, Table3.rd, Table3.scd
Having count(*)>1;


and it did.

So what have you done wrong or not told us about?

Brian

 

[Rupa]

Tried it again and am still getting 3568 rows. Any suggestions?

Thanks,

Rupa

 

[Rupa]

I just want the group names to appear. So if I select just the group name then it should show:

AD
FM

and not JK or any others. Don't know if I'm making sense. Here's what I've written:

SELECT ACG_All.Groups
FROM ACG_All INNER JOIN ACG_All AS ACG_All_1 ON (ACG_All.RD = ACG_All_1.RD) AND (ACG_All.Schedule = ACG_All_1.Schedule)
GROUP BY ACG_All.Groups, ACG_All.RD, ACG_All.Schedule
HAVING (((Count(*))>1))

Much appreciated again.

Rupa

 

[Brianwarnock]

If you can supply a small/manageable amount of test data then we could look at you DB.

Brian

 

[Rupa]

Could you please feed this into a table:

AD, LRC1, Mon-Fri 07:30-17:00
AD, LRC2, Mon-Fri 07:30-13:00
FM, LRC1, Mon-Fri 07:30-17:00
FM, LRC2, Mon-Fri 07:30-13:00
JK, LRC1, Weekdays
JK, LRC2, All day
JK, Main1, All day

And see if you are able to get AD and FM?? I might be doing something wrong?

Thanks,

Rupa

 

[Rupa]

I've just tried that data and it worked but doesn't work for the bulk data that I have. This is bizarre! I'll see if I can attach some of the original data.

Thanks,

Rupa

 

[Brianwarnock]

Can you Compact Zip and attach your DB?

Brian

 

[Rupa]

Some data is sensitive. I'll see what I can do. I'll try and filter it. Thank you for all your help. Much appreciated.

Rupa