Trouble with 2 tables connected by reference table

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
.

Relevant Pages

  • Re: Can some explain this to me?
    ... the subquery, but it can also reference any column in any table from ... resolved against the outer query table person. ... inside a transaction and if the count is different roll the ...
    (comp.databases.ms-sqlserver)
  • RE: Combo box bound field change
    ... Modify the row source to include both fields. ... Make the bound column the company name column. ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ...
    (microsoft.public.access.forms)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • RE: Combo box bound field change
    ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ... Modify the New transaction table and change the company name field to ... I'd run a report to show what all that company purchased. ...
    (microsoft.public.access.forms)
  • RE: Combo box bound field change
    ... You do want the companynbr field rather than the company ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ... I'd run a report to show what all that company purchased. ...
    (microsoft.public.access.forms)