Trouble with 2 tables connected by reference table

J

jwb96

I'm trying to summarize similar data from two tables in one query but am
having issues when I bring in the second table.

I have an activity table, with storeId, activityDate, activityA and
activityB. I use two reference tables (dateRef, to roll up activityDate into
weeks and months; and storeRef, to display a storeName) to make the query
output summarized and readable. This works fine.

I then also want to bring in data from the transaction table, which has
transactionCount, transactionDate and storeId. I create relationships from
the existing reference tables to the transaction table, and while the
transactions calculate fine, all of the sudden the activity data from the
first table gets multiplied by three.

There are other columns in the activity and transaction tables that make the
rows unique - in other words, I can't simply join the tables together without
the reference tables. Currently, my relationships look like the reference
tables are acting as a bridge between the fact tables.

Any suggestions on what could be causing the troubles?

Thanks,
Jim
 
M

Michel Walsh

You probably have duplicated values ( 3 times ) under the field of one of
the table implied by the join.


table1.f1 table1.f2
a 1
a 2


table2.g1 table2.g2
a 10
a 20



SELECT * FROM table1 INNER JOIN table2 ON table1.f1=table2.g1


f1 f2 g1 g2
a 1 a 10
a 1 a 20
a 2 a 10
a 2 a 20



since each matching records in table1 and table2, matching the condition:
table1.f1=table2.g1
will be 'joined' (horizontally) with each record it matches in the other
table. SO, here, the result got 4 records. Add a third record in table2:

a 30


and the result will get 6 records. So, from the table1 point of view, it
would like as if all its records were 'multiplied' by 3; while, from table2
point of view, all records would appear to have been multiplied by 2.




Hoping it may help,
Vanderghast, Access MVP
 
J

jwb96

I discovered that this is exactly what was happening. There is a column in
each table that could have multiple values per store-date, but only one table
were there actually 3 values for each store-date. What is the fix for this?

Thanks,
Jim
 
M

Michel Walsh

Depends on what you want to do exactly. If you need the store and, say, its
address:



SELECT store, LAST(address)
FROM someTable
GROUP BY store


which will pump one record per store, and will return give one of the
address associated to it (in case there are multiple records, it is not sure
each one will have the same value for the other fields).


Then, use THAT query, having no duplicated value anymore, instead of the
table.


I use 'address' just as example, it may be some other field you need, so,
use that other field name.



Hoping it may help,
Vanderghast, Access MVP
 

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