Re: Trouble with 2 tables connected by reference table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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



"jwb96" <jwb96@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C5789649-2B19-40EF-83AD-D9076F0B716C@xxxxxxxxxxxxxxxx
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: Call SqlCommand.ExecuteReader inside another SqlCommand.Execut
    ... Do you mean that you are concerned that a row of interest in table2 could be ... updated after you execute the select on table1 but before you execute the ... begin transaction; ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Call SqlCommand.ExecuteReader inside another SqlCommand.Execut
    ... selected in table1. ... Cubicle Wars - http://www.windwardreports.com/film.htm ... Do you mean that you are concerned that a row of interest in table2 could ... begin transaction; ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: update some values in one table from another table
    ... Now I'm not woried about the entries in Table2 where the references don't ... match those in Table1 (shown as refA abd refB, but I would like to update ... If you do have this unique reference, create a new Query adding Table1 ...
    (microsoft.public.access.gettingstarted)
  • RE: DataSet.Relations and ForeignKeyConstraint
    ... The following are the columns Col1 through 5 are name of the columns in the respective tables. ... Suppose Table1 is master table and Table2 is transaction table then for every record in master table you can retrive the transactions by grouping the values in the transaction table. ...
    (microsoft.public.dotnet.framework.adonet)
  • Two combo boxes tied together on data set
    ... If I have two tables in a data set say like this ... tied to the data table table1, and i want it to show its sub items from ... table2 in cboTable2 based on the referential integirty ... how can i use the reference to do this? ...
    (microsoft.public.dotnet.languages.vb)