Duplicate records in query to join fields from several imported txt files

[Bart2008]

Hi all,

I have the following issue:

In my job we work with several raw data .txt files exported from Oracle ERP system. These data include information about: inventory, sales, backorders, purchase orders, forecasts, product line.

My goal is to put information from all of these imported txt files together to create an easy to use snapshot file.

The common field between al of these files is the item description. I have an issue where records are repeated for several fields every time the same item description is showed. For example for the same product description the january sales forecast QTY is repeated on several rows because there are several orders for this product description. Is there a way to make forecast QTY appear only once but keep the multiple orders and their information?

Example of current result

[/URL][/IMG]

to achieve result

[/URL][/IMG]

thank you for any comments

 

[neileg]

I can't see the logic of displaying the data the way you have in the second image. If I wanted to do this, I would have a header that showed the common data for each product and a detail section that shows the individual orders. This is pretty easy to do in a form or report.

 

[Bart2008]

hi, thx for the reply ...

My final goal is to export a .txt file that can be used by employees all over europe as source data to create a pivot table in excel that will show for each product description

stock QTY - open purchase order QTY - customer order QTY - forecast QTY - sales QTY

I succeeded in doing this for the overall totals but when I want to add more detail for example add a field sales region to show sales/orders/forecast per region or add a field customer to show customer order QTY in detail I am facing the issue of duplicate data in the table.

Maybe I am using the wrong approach and there is an easier method. If there are suggestions please tell me.

 

[neileg]

Without knowing the business process, it's difficult to advise. If you're going to end up with an Excel pivot, you could do this in the pivot, anyway.

 

[Bart2008]

My main issue is that the data I need is in several seperate raw data reports in .txt files. As far as I know it is not possible to put all of this directly in one pivot.

So I want to combine certain fields from the seperate .txt files into one new .txt file and use this as source for pivots. I succeed in access but only if I keep the detail very low, I want to have more detail but then there is duplicate data.

Any suggestions on how to combine seperate .txt files is welcome