ms access query duplication of rows

Question

Hi all,

I am running a query where data is retrieved from 2 different tables. The first table records the stock item, description, etc. The 2nd table records the stock item + the long description. This long description may contain 2 or 3 lines.

When I export the query to Excel and the long description is more than 1 line, the data gets exported the same amount  of times as there are long description lines.

With other words, if the long description contains 3 lines, the data gets exported 3 times to Excel.

Below is the Access query results. Note E1006024 and E10134001 where the long descriptions contains 2 lines.

Is there a way that I can export the data only once with both the long description lines?

Below the access query in SQL view:

SELECT dbo_InvMaster.StockCode, dbo_InvMaster.StockUom, dbo_InvNarration.Line, dbo_InvNarration.Text, dbo_InvMaster.Supplier, dbo_ApSupplier.SupplierName INTO UOMTable IN 'Y:\PL Chemserve\NEW PL System Backend\Packing List System NEW_be.accdb'
FROM (dbo_InvMaster LEFT JOIN dbo_InvNarration ON dbo_InvMaster.StockCode = dbo_InvNarration.StockCode) LEFT JOIN dbo_ApSupplier ON dbo_InvMaster.Supplier = dbo_ApSupplier.Supplier
WHERE (((dbo_InvMaster.StockCode) Like 'E*'));

Much appreciated

Deon

Answer 1

Hi Deon, When you say "three lines," are you referring to three separate records? If so, you can concatenate each record into one. Take a look at this simple function: http://accessmvp.com/thedbguy/codes.asp?tittle=simplecsv Hope it helps...

Answer 2

Hi Deon SA,

I think its because of Left join you used in your query.

you need to correct it.

what if you use distinct.

try to use it may solve the issue.

if not then try to check your join again.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].