Re: Trouble with 2 tables connected by reference table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

"Michel Walsh" wrote:

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: Trouble with 2 tables connected by reference table
    ... SELECT store, LAST ... address associated to it (in case there are multiple records, ... I use two reference tables (dateRef, ... I then also want to bring in data from the transaction table, ...
    (microsoft.public.access.queries)
  • Re: "Effective Date" Criteria for Rates
    ... INNER JOIN ... The Reference Number is a unique identifier for that transaction, ... Craft Code, Activity Date & Account Code determine the Rate based on the ...
    (microsoft.public.access.queries)
  • Re: Database design pattern question
    ... When it does it will be necessary to replace the disk drive and then rebuild your database using your most recent backup and a transaction file containing the transactions that have been processed since the backup was made. ... That is the problem that I really need to be able to have multiple copies of the databases being updated in parallel and to have the ability to synchronise them later. ... Maintain a transaction log and "replay" the transaction log for each copy of the database into the other databases.. ...
    (comp.databases)
  • Re: (Mis)use of transactions
    ... minimize the time between starting a transaction and then either committing ... I suggest exactly what you proposed: Cache locally all the edits the ... If the reference table does not have a small ... re-open the connection, though, as you offered. ...
    (comp.lang.java.databases)
  • Re: SUM by unique reference
    ... The problem is that the reference number changes with each transaction, ... "WendyUK" wrote: ... A/C,Name, Type, Code, Description, Value, URN Ref ...
    (microsoft.public.excel.worksheet.functions)