 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008Duplicate data in query
I am creating a database to compare about 5 different tables (we will name them Table1, Table2, etc). I created a query to compare via Building Number (Each table has a field of building numbers). When I input a value (such as building number 108), I am getting duplicate data. Table1 has 2 records in for building 108 and each subsequent table has only 1 record for building 108. In the results of the query, I am seeing both results for Table1 (which is correct), but each other table is duplicating the information a second time. I only want to see the 2 records from Table1 and 1 record each from the other tables. Any suggestions? I can post my SQL code if needed. I may be wrong but this looks more like a database design problem. What sort of data do your tables hold and how are they linked? Or have you inherited a bad design and have no option of repairing it? Evi > I am creating a database to compare about 5 different tables (we will name > them Table1, Table2, etc). I created a query to compare via Building Number [quoted text clipped - 5 lines] > time. I only want to see the 2 records from Table1 and 1 record each from > the other tables. Any suggestions? I can post my SQL code if needed. Each table has a building number, building name, and building short name. I am trying to compare the tables to find the inconsistencies. I created 1 table that has all correct building numbers and names and in my query, I have them all connected via this table. One of my queries asks the user to enter in a building number. This is the one with the duplicate data. I have a second query that asks the user for a building name. This one works perfectly. No duplicates. But building number is duplicating the data where there is only one record (if that makes sense). Thank you for your help! > I may be wrong but this looks more like a database design problem. What sort > of data do your tables hold and how are they linked? Or have you inherited a [quoted text clipped - 15 lines] > > time. I only want to see the 2 records from Table1 and 1 record each from > > the other tables. Any suggestions? I can post my SQL code if needed. This still isn't making sense. You seem to be saying that each table contains 3 fields, Building name, Building Number, Building ShortName. Why do you have 5 tables with building names rather than one table? ie what makes a building in table 1, different from a building in table 2 especially as you *seem* to be saying that the same building can be in more than one table. What constitutes an inconsistency? I'll stab at a few suggestions but unless you can explain what you are doing, they may not do much good. have you tried using the Find Duplicates and Find Unmatched Queries? Would it help to use a Union Query which joins some or all of the tables together into 1 query? - although it is hard to work with a Union query, you can put the Union query into another query and then filter and sort on that query. But even if this works for you, it is looking increasingly likely that you need to redesign your database. Evi > Each table has a building number, building name, and building short name. I > am trying to compare the tables to find the inconsistencies. [quoted text clipped - 27 lines] > > > time. I only want to see the 2 records from Table1 and 1 record each from > > > the other tables. Any suggestions? I can post my SQL code if needed. They actually started out as 5 different Excel files that were exported from the AS/400. Each department here in the district named the buildings different ways. We are trying to find an easy way of comparing the 5 reports and making changes where necessary (rather than manually comparing 5 huge reports). I thought by making an Access database, I could easily run reports by building number or name, pulling in all records for that certain criteria, and returning all records. Other than creating a union query, I wasn't sure if there was another way of easily comparing the tables without getting duplicate data. I hope this makes a little more sense! Thanks! > This still isn't making sense. > You seem to be saying that each table contains 3 fields, Building name, [quoted text clipped - 62 lines] > from > > > > the other tables. Any suggestions? I can post my SQL code if needed. Now it makes sense, Beth (though I don't know what an AS/400 is). Create a table called TblDepartment. Give it DeptID (Primary Key, Autonumbe) DeptName Type in the names of your departments. In each table, create a number field, DeptID. Ensure that the Default Value is not 0. Also, create a tick field, Sorted, so you can indicate when a building has been sorted out. Use an Update Query to put the Correct DeptID in the DeptID field for each table. (more details available if needed) for the sorted table, use the Update Query to also set Sorted to True Once you have done this, you can use an Append query to append the buildings into one table, giving that table a primary key. If buildings can be in more than 1 department at a time, or need to keep a record of departments that they were in, in the past, then your next step in normalizing your db will be to create a seperate table for the buildings while your BuildingDepartment table will hold the foreign key field BuildID from the Building table and DeptID from the departments table. Alternatively, there is that Union Query..... Evi > They actually started out as 5 different Excel files that were exported from > the AS/400. Each department here in the district named the buildings [quoted text clipped - 75 lines] > > from > > > > > the other tables. Any suggestions? I can post my SQL code if needed. I couldn't get the update query to work properly, so I just did a union query and everything works perfectly! Thanks so much for your help! > Now it makes sense, Beth (though I don't know what an AS/400 is). > Create a table called TblDepartment. [quoted text clipped - 129 lines] > > > > > > the other tables. Any suggestions? I can post my SQL code if > needed. What went wrong with the Update query. You will need to use it eventually to Normalize your database. The Union query is just a temporary repair. Evi > I couldn't get the update query to work properly, so I just did a union query > and everything works perfectly! Thanks so much for your help! [quoted text clipped - 132 lines] > > > > > > > the other tables. Any suggestions? I can post my SQL code if > > needed. Thank you, Evi, for all your help! I redid the Update query and finally got everything to work. It seems like if I did the Append query, it's almost the same as just doing a Union query on all the tables. Is that correct? Since I would be appending the data from all my tables into 1 table to pull my reports from. Is there a difference between just doing a Union query or doing the Update and Append queries? > What went wrong with the Update query. You will need to use it eventually to > Normalize your database. The Union query is just a temporary repair. [quoted text clipped - 170 lines] > > > > > > > > the other tables. Any suggestions? I can post my SQL code if > > > needed. An enormous difference. I have 2 tables for my accounts database. Once a year, I empty my expenditure table into an Archive table (using an Append query) But I want to compare statistics over several years so I use a Union query to show both tables together while still keeping the data physically apart. The advantage of doing a union query with tables you want to join, is that you get to see visually if there are any discrepancies in the data or any unforseen problems with joining them before you actually do so. Imagine if (like one writer) you have 4 tables each representing a school term's test results called Autumn Term, Winter Term and you innocently joined them together before realizing that you needed a date field to tell you which term each record occured in. Evi > Thank you, Evi, for all your help! > [quoted text clipped - 180 lines] > > > > > > > > > the other tables. Any suggestions? I can post my SQL code if > > > > needed. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.