Consolidating Data

[Gregc.]

G'day

I have two tables with transactional data. Table 1 has a Cost Centre,
Account, Fund Code and a YTD0-6 figure for each Account Number. Table 2

has Cost Centre, Account, Fund Code, Period 7, Period 8, Period 9,
Period 10, Period 11, Period 12 data. Both Tables are linked to 'Data
Tables' , Account Information, Cost Centres Information and Fund Type
Information.


What I want is to consoldiate the transactional data into one table so
that it would look something like this:
YTD0-6 Period7 Period8
etc
Account Cost Centre Fund type..Value Vaue Vaue


I have designed a query to consolidate the data, but for some reason
the query duplicates the records. Is there some way around this.


Thanks for your assistance.


Greg

 

[Guest]

If I understand this correctly you have the following look mup tables in your
DB: Account Information, Cost Centres Information and Fund Type Information.
Then you have 2 transaction tables as follows: Your table 1 and your table 2
shown below. What is happening with your query is that you are bringing the
data from both tables into one and your relationships between each
transactional table and the look up tables are probably saying in the join
type window "show me all records from table one and only those that match
from a linked table" and the same thing showing on table 2. thus, when you
bring them together in a query you will get duplicate records. The thing you
need to do is take a serious look at how your relationships are set. I have
ran across this problem before and I have gone into the relationship and
edited/deleted certain ones until I got the data i wanted and thar it
contained all data needed. You might try keeping the relationships to table 1
which seems to be the master transactional table and have table 2 either with
no links if all information is hand input, or maybe link it to table 1.