By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,909 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

MS Access Duplicates

P: 6
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
Jul 25 '07 #1
Share this Question
Share on Google+
13 Replies


Rabbit
Expert Mod 10K+
P: 12,315
I don't see any duplicates, the records look unique to me.
Jul 25 '07 #2

Expert 100+
P: 296
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.
Jul 25 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
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:

Expand|Select|Wrap|Line Numbers
  1. [ID] [Item Name]
  2. 1    Mixer
  3. 2    Mixer
  4.  
  5. [ID] [Weight]
  6. 1     33
  7. 2     22
  8.  
Jul 25 '07 #4

P: 6
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
Jul 26 '07 #5

Expert 100+
P: 296
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
Jul 26 '07 #6

Rabbit
Expert Mod 10K+
P: 12,315
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.
Jul 26 '07 #7

P: 6
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
Jul 26 '07 #8

P: 6
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
Jul 26 '07 #9

Expert 100+
P: 296
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.
Jul 26 '07 #10

Expert 100+
P: 296
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.
Jul 26 '07 #11

Rabbit
Expert Mod 10K+
P: 12,315
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.
Jul 26 '07 #12

P: 6
Thank you very much for your views.

prakash
Jul 27 '07 #13

Rabbit
Expert Mod 10K+
P: 12,315
Good luck.
Jul 27 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.