MS Access Duplicates

[tiwariprakash]

Hello
I am having problem with duplicates. I have two table as below:

ID Item Name
1 Mixer
2 Mixer

Item Name Weight
Mixer 33
Mixer 22


ID Item Name Weight
1 Mixer 33
1 Mixer 22
2 Mixer 33
2 Mixer 22

But I want a table without dublicates. I can't use Max/Min/Average or First/Last under Weight field because there are more than 2 items with same name. (Both ID and Weight are unique).

Can somebody guide me please.

Thanks
prakash

[Rabbit]

I don't see any duplicates, the records look unique to me.

[mlcampeau]

I don't see any duplicates, the records look unique to me.

I think Prakash is looking for a result like this:

ID Item Name
1 Mixer
2 Mixer

Item Name Weight
Mixer 33
Mixer 22

ID Item Name Weight
1 Mixer 33
2 Mixer 22

I'm having the same difficulties with one of my queries (Help with query...) and have been unable to come up with a solution.

[Rabbit]

I think Prakash is looking for a result like this:

ID Item Name
1 Mixer
2 Mixer

Item Name Weight
Mixer 33
Mixer 22

ID Item Name Weight
1 Mixer 33
2 Mixer 22

I'm having the same difficulties with one of my queries (Help with query...) and have been unable to come up with a solution.

Then the tables aren't designed properly for this. There's no way to know which Mixer goes with which ID number. Rather the tables should be set up thusly:

[ID] [Item Name]
1    Mixer
2    Mixer

[ID] [Weight]
1     33
2     22

[tiwariprakash]

Thank you for your reply.
Please find 2 tables which need to be combine based on the Item.
Table 1:
ID Item
1 Mixer
2 Mixer
3 Pump
4 Valve
5 Valve
6 Valve

Table 2:
Item Weight
Mixer 22
Mixer 33
Pump 44
Valve 55
Valve 66
Valve 77

Mixer (ID=1) grab the Weight 22 and 33, then another Mixer (ID=2) does same with Weight. Therefore, I am getting duplicates. I am not concerned which Mixer pick up which Weight, either can be accepted but without duplicates of Weight and ID. Thus, combining above tables using Item, I found following table as below:
Result Table in MS Access
ID Item Weight
1 Mixer 22
1 Mixer 33
2 Mixer 22
2 Mixer 33
3 Pump 44
4 Valve 55
4 Valve 66
4 Valve 77
5 Valve 55
5 Valve 66
5 Valve 77
6 Valve 55
6 Valve 66
6 Valve 77


But I wanted a table as:
ID Item Weight
1 Mixer 22
2 Mixer 33
3 Pump 44
4 Valve 55
5 Valve 66
6 Valve 77

I hope I am able to describe the problem clearly. I would be pleased if you share your idea to solve (preferably in MS Access).

Thank you.
prakash

[mlcampeau]

Prakash, take a look at Rabbit's post

Then the tables aren't designed properly for this. There's no way to know which Mixer goes with which ID number. Rather the tables should be set up thusly:


Code: ( text )
[ID] [Item Name]
1 Mixer
2 Mixer

[ID] [Weight]
1 33
2 22

He is saying (I think) that in order to get the results you're looking for you need to change your second table to say
[ID] [Weight]
1 33
2 22

instead of
[Item] [Weight]
Mixer 33
Mixer 22

Your table 1 and table 2 should then be joined on the ID field

[Rabbit]

Prakash, take a look at Rabbit's post


He is saying (I think) that in order to get the results you're looking for you need to change your second table to say
[ID] [Weight]
1 33
2 22

instead of
[Item] [Weight]
Mixer 33
Mixer 22

Your table 1 and table 2 should then be joined on the ID field

That's correct. You may not care which mixer goes with which ID number but Access cares. And if you're not going to link by a unique field to a unique field or a unique field to a non-unique field then you're going to get "duplicates ".

The join you are doing now is many to many. What you want is a one to one or one to many.

[tiwariprakash]

That's correct. You may not care which mixer goes with which ID number but Access cares. And if you're not going to link by a unique field to a unique field or a unique field to a non-unique field then you're going to get "duplicates ".

The join you are doing now is many to many. What you want is a one to one or one to many.

Thanks for the reply. However, I am not able to link ID and Weight because both or them are unique and don't have any other field to link.

Prakash

[tiwariprakash]

Alternatively, I am wondering, whether somebody has an idea to change following table with unique fields.
ID Weight
1 22
1 33
2 22
2 33
3 44
4 55
4 66
4 77
5 55
5 66
5 77
6 55
6 66
6 77

Can we convert as:
ID Weight
1 22
2 33
3 44
4 55
5 66
6 77

Your views will be much appreciated.

Thanks

prakash

[mlcampeau]

In your table 2, instead of having the Item Name, change it to the Item ID, then you will have something to link the ID to. Again, revisit Rabbit's post where he explains how your tables should be set up. You will need to go into the table design and change the Field Name "Item" to ID and make it the same data type as your ID in Table 1. Table 2 should not have the Item Name (i.e. Mixer) anywhere in it. Once you have made the change to Table 2, then link Table 1 and Table 2 by ID.

[mlcampeau]

Alternatively, I am wondering, whether somebody has an idea to change following table with unique fields.
ID Weight
1 22
1 33
2 22
2 33
3 44
4 55
4 66
4 77
5 55
5 66
5 77
6 55
6 66
6 77

Can we convert as:
ID Weight
1 22
2 33
3 44
4 55
5 66
6 77

Your views will be much appreciated.

Thanks

prakash

There is no way to convert the first table into the second table because the first table is all unique records. A record is considered to be the entire row. Since there is at least one field in that record that is different, it is considered unique. If you follow the other suggestions posted (change your format of Table 2), you will not have a problem.

[Rabbit]

Alternatively, I am wondering, whether somebody has an idea to change following table with unique fields.
ID Weight
1 22
1 33
2 22
2 33
3 44
4 55
4 66
4 77
5 55
5 66
5 77
6 55
6 66
6 77

Can we convert as:
ID Weight
1 22
2 33
3 44
4 55
5 66
6 77

Your views will be much appreciated.

Thanks

prakash

If you were to do a First. on the first table and then join, you could get:

ID Weight
1 22
1 33
3 44
4 55
4 66
4 77

However, you can't get your desired results without either extensive programming or changing the structure of your table. The latter would be the easier and correct method.

[tiwariprakash]

Thank you very much for your views.

prakash

[Rabbit]

Good luck.