Multiplied Duplicate Entries

[aftabn10]

Hi, i am having some problems when combining data from two tables into 1 table. Both tables have a [F Unique ID] field which I have joined but the problem I have is that within both tables I have the Unique ID 000185 that appears 65 times in each table due to other fields having different information that belongs to this ID.

When joining the two tables the Unique ID 000185 then has 4225 records rather and I have worked out that this has repeated each record 65 times (65 X 65 = 4225).

I was looking for some help as to how I can stop this from happening. Any help would be appreciated.

Thanks in advance.

 

[jdraw]

You need to design your tables.
Research Normalization

Use a naming convention that DOES NOT have embedded spaces or special characters.

see
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf

 

[aftabn10]

thanks jdraw, the tables have been designed, the only thing I can identify is that the two fields [F Unique ID] have no relationship between the two tables and I have just created the 2 fields separately. Would it best to have this field as a foreign key from table 1?

Btw thanks for the link, going through that now as well. Appreciate your help.

 

[MSAccessRookie]

Hi, i am having some problems when combining data from two tables into 1 table. Both tables have a [F Unique ID] field which I have joined but the problem I have is that within both tables I have the Unique ID 000185 that appears 65 times in each table due to other fields having different information that belongs to this ID.

When joining the two tables the Unique ID 000185 then has 4225 records rather and I have worked out that this has repeated each record 65 times (65 X 65 = 4225).

I was looking for some help as to how I can stop this from happening. Any help would be appreciated.

Thanks in advance.

How many "other fields" are there? If there are only one or two, then perhaps you could do what you need with multiple JOIN Keys. Even if you can, I agree with jdraw that the design should be reviewed, since this would be a quick fix only.

 

[jdraw]

What do your tables represent? What do the fields mean?
Tell us more about your application. It isn't clear, but it sounds like a design issue.

 

[aftabn10]

Thanks guys for your replies, it looks like a design issue as I realised that within the four tables I have there is no relationship that I have built within the tables and I have gone ahead and built queries (join queries) that have worked until I tried this query so seems like I will have to go back to the drawing board.

Currently I have 5 areas that hold information about Job Queues that vary across each site so there is data within each table that matches. When reviewing my 5 tables I have also noticed that each table has a separate field for ID that I have made as the primary key and I have then created 5 different join queries to combine everything that I require within 1 table. I have now gone back and I am starting to build an ERD to try and make sure the design is correct.

 

[aftabn10]

Sorry another point I thought of is that the tables already have duplicate fields across each one so thats where I find it confusing of how I can bring about a foreign key in order to make sure I have unique data coming across. Any tips or advice would be really appreciated.

 

[jdraw]

I find the post confusing

Currently I have 5 areas that hold information about Job Queues that vary across each site so there is data within each table that matches. When reviewing my 5 tables I have also noticed that each table has a separate field for ID that I have made as the primary key and I have then created 5 different join queries to combine everything that I require within 1 table. I have now gone back and I am starting to build an ERD to try and make sure the design is correct.

I have tried to highlight 3 "potential entities" based on your post. But what is it that the database is suppose to represent?? What is the business you are dealing with?

 

[aftabn10]

Thanks jdraw for your response.

Apologies for the confusion. I basically have 5 different tables that each represent a spreadsheet of Queue Names, Ref No from 5 different teams within a Call Centre.

Within the Call Centre we have jobs coming through to agents and all 5 different teams have different information regarding the queues that these jobs come through. Team 1 will have the Routing Information (tel no, tel no ref, queue name), Team 2 will also have a queue name, queue id, area of work etc, Team 3 will have a queue name, source of job etc.

My job is to stop the confusion with so many different naming conventions for 1 queue and to basically combine all 5 tables into 1 table so that all 5 teams can look 1 spreadsheet to get the required information and for future all updates will be added within 1 file.

The problem I have is i am not too sure how to link the tables in terms of relationships as there is duplicate data. I know its very confusing, so apologies, but I have created an ERD if that helps.

Any tips would be really appreciated, and thanks once again for looking at this.

 

[jdraw]

You mention 5 spreadsheets, and you want to look at 1 spreadsheet (with no confusion)....

Are you dealing with Excel or Access?
Here is a free data model related to Call centers that may help.
http://www.databaseanswers.org/data_models/call_centers/index.htm

Post your erD AS JPG.

 

[aftabn10]

jdraw i am dealing with Access, as I received 5 spreadsheets which I then imported as tables within Access. I have then ran queries to try and link everything up with join queries in order to try and come up with 1 table. Also I have attached the ERD (no links just highlighted duplicate fields within tables) alongwith a breakdown of the final output table.

Really appreciate your support.

 

[jdraw]

Suggest you read up on Normalization and ERDiagramming.
The first 3 topics here
http://www.rogersaccesslibrary.com/forum/topic238.html

Work through an ERD using your own data.

Define each field in your final table, then restructure into appropriate tables.

Take a look at the Call center related data model in previous post.

 

[aftabn10]

Thanks jdraw for all your help, really appreciated.