"Doubled" query results

A

Andy

I have used Access for several years (beginning w/ v. 2.0)
and have always used the same overall table scheme, but
with 2002 I'm having a problem I haven't seen before. I
have two types of tables: reference tables with account
numbers (each of which is unique and primary key) and data
tables with amounts tied to those account numbers (and
each account number may appear several times in the data
tables in different combinations). My reference tables
and data tables are tied together with one-to-many
relationships (reference is one, data is many) and
referential integrity enforced.

My problem: when I write a query asking (in separate
query fields) for values in more than one of the data
tables that correspond to each value in the reference
table, the values from one of the data tables is always
doubled for each reference value. For example: account
number 22208 will show a (correct) sum of $100 from one
data table, but when the other data table is mined in the
same query it will show a sum of $300 when the true sum
should be $150. It's as if it's summing from one of the
tables twice (but not both of them)! I've messed with the
relationships, checked data integrity, even pasted data
into new clean tables to try to isolate or fix the problem
but it keeps recurring. The only workaround seems to be
to do make-table queries based on each individual data
table, and then merge them in Excel or via a paste
append. This adds several steps and a lot of wasted time
to a process that always worked without a hitch until I
upgraded to Access 2002. Does anyone know what's going on
and how I can fix this darn thing???

Thanks--Andy Hopkins, City of Santa Fe, New Mexico
 
J

John Viescas

Andy-

Please post the SQL from your query and a brief description (Primary -
Foreign keys) of the tables involved.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top