Two tables, one query...remove duplicate data in report?

[dante2004]

Raw Data

[aytee111]

Your data has changed from your first post. Have you changed the record source?

[dante2004]

No, I didn't change the record source. I did add two columns to the table hoping that would allow me to sort it differently/better and achieve what I was trying to do.

"Source" and "Source2" were the additions...basically "Warehouse1" and "Warehouse2". It's actually quite a bit more complicated than that (our operational challenge, not the db setup), but these generic labels work for the db and my question here so I didn't want to confuse the question any further.

[aytee111]

There has been so much going on in this post whereas my original post referred to your original post. Can you show me the same picture as your original post with the two fields set to hide duplicates?

[dante2004]

[dante2004]

I think the problem with this is that Access will see the "1" in Total stock as a duplicate, when it may not be.

There is actually...

10423386 - 1
10423388 - 1

[aytee111]

In post #16 you show two records for LX03 - is that right? What is total stock then, is it 2 or is it 1? It is a bit confusing. If the "1" isn't a duplicate then what is it?

What is the total stock field? It has nothing to do with the qty, right?

If the hide duplicates isn't working then try setting up group on storage bin.

[dante2004]

There is 1 piece in each location, 2 total
10423386 - 1
10423388 - 1

If you look at Post #20, it doesn't show the second unit because it is hiding duplicates (I'm assuming) and sees the number "1" twice...and hides it.

I'm trying to not completely change the nomenclature in my data, not post proprietary data and have it all make sense...haha...bear with me. :-)

LX03 is "Warehouse1" and we record units there as "Total Stock"
The columns on the right hand side are another warehouse location and we record units as "Qty".

So for part 10001934, we have 28 on hand in 5 locations. Clear as mud?

[dante2004]

This is my goal...something like this. I just manually put this together in Excel.

[aytee111]

Oohh!! You are trying to show two completely separate sets of data on one line! Ain't gonna happen! The material code is the only thing they have in common.

Make two subreports and put them side by side, one for each warehouse, make them very small (vertically) with CanGrow set to yes. On the footer of each subreport you can put the total for that warehouse, and on the footer for the material you can have the total of them both added together.

[orange]

dante2004,

You may get some ideas from this article by Allen Browne on Inventory




It sounds to me that your tables would be something like this draft model.

Good luck.