Hi to all..


I'd like to ask some help again, first : how to eliminate the redundant value in a query. I have query named product sold, the calculate works fine but then it will show redundant values. Like example:

Products Query Products Query Product ID Product Name Quantity UnitsReceived TotalReceived ProductSold RemainingProducts 6001 410 Silk
100 200
200 6001 410 Silk
100 200
200 6001 410 Silk

200
200 1001 BW Can Chicken & Rice 10 100 400 14 386 1001 BW Can Chicken & Rice 4 100 400 14 386 1001 BW Can Chicken & Rice 10 300 400 14 386 1001 BW Can Chicken & Rice 4 300 400 14 386 i want to eliminate the redundancy of data. its waste of space.


Second question is: how to use the DLook up function for 2 fields in one query.
AS of now my code in DLook up is like this:

strFilter = "ProductID = " & Me!ProductID

Me!ProductSold = Nz(DLookup("[ProductSold]", "Total Sold Query", strFilter), 0)

Im not good in explaining to be more precise, i've uploaded some sample data..Please take a look..



Thanks....:o

I don't understand the first question. I don't see how you are getting from group 1 to group 2. In a totals query, duplication should be summarized away.

The answer to your second question is - you can't. Functions return a single value and that's that. Usually though, the domain functions are not necessary. Try joining the lookup table to the main table and use that query as the RecordSource for your form/report.

I took a look at your sample, and the first thing that I noticed was that you had an "ambiguous outer join" error in the query. I changed the query to an inner join, and then viewed the SQL code. What I found was that you had TWO joins between the same two columns.

I went to the design mode, removed one of the joins, and the query works now, but I am not sure whjat it was supposed to do, so I suggest you do the same and try it.

As for your original problem, I am not sure I understand what you wanted, because the sample data in your post did not match the results of the query.