If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Consolidating Data
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 |
#2
|
|||
|
|||
Consolidating Data
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. "Gregc." wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
ranking query | JaimeTimbrell | General Discussion | 2 | February 16th, 2006 08:09 AM |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
Pulling data from 1 sheet to another | Dave1155 | Worksheet Functions | 1 | January 12th, 2005 05:55 PM |
Format on data to import to Access tables? (I need your advice) | Niklas Östergren | General Discussion | 5 | December 13th, 2004 02:54 PM |
Is this possible with Excel Chart? | q582gmzhi | Charts and Charting | 1 | September 8th, 2004 03:33 AM |