Duplicate data in query

[Beth Eadie]

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.

 

[Evi]

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

 

[Beth Eadie]

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!

 

[Evi]

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

 

[Beth Eadie]

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!

 

[Evi]

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

 

[Beth Eadie]

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!

 

[Evi]

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

 

[Beth Eadie]

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?

 

[Evi]

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