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

[dante2004]

I have 2 tables, each listing inventory at their respective warehouse. I've combined the tables in a query and filtered according to me needs.

However, when I create the report, I am getting duplicate data that is misleading. I know this is probably an easy fix...just don't know where to start.

There is only "1" item in storage bin "10459413" so I don't want it repeated 4 times. However, I do want all four records from the far right column.



I would like it to show up like "example2" with the items with the red line through them not showing up





The 2nd and 3rd columns are the quantity on hand and bin location in warehouse #1 (whse 1) and the 3rd and 4th columns are the same information for warehouse #2 (whse 2).


Total inventory
Whse 1 = 1
Whse 2 = 8

[dante2004]

I guess I really should have put this in the queries section...because I think that is where the "issue" starts. Here is a screenshot of the query that the report is based on.

[dante2004]

And then from design view...sorry for all the posts. Couldn't figure out how to get the second image in the old post and then delete the additional posts.

[andy49]

You could use the query designer to get the top 1 results? Would that suffice?


Sent from my iPhone using Tapatalk

[dante2004]

I'm not sure if that would do it...I'll look into that. I have over 10,000 part numbers to do this for so I'm hoping I can find a way to speed this up from manual comparisons in Excel. :-)

[andy49]

In which case we may need more info. What is it that is so special about that top result that you want it and no other?


Sent from my iPhone using Tapatalk

[dante2004]

Unless I am using it wrong, "Top Values = 1" reduces the record count of BOTH columns to just 1. I want all 4 records from the "Location" column.

[dante2004]

I don't want just the "top result". I want them all. The problem is, that in the examples (images), it is the same data point repeated. It makes it look like I have 12 pieces of that material when I actually have 9.

[orange]

What exactly are you trying to achieve - in simple terms, no jargon?

I have 2 tables, each listing inventory at their respective warehouse.

Please show us your table designs and relationships.

[dante2004]

Let me redefine my objective in different terms and maybe it will allow me to better communicate my objective. I want to learn how to do this, not just "fix this".

Let's say you are going grocery shopping and you are making a list of what you need. So first, do you an inventory of what you have (I know...crazy analogy...who does that?!? lol)


I have this list of food items


I also have this list of items


I want to combine it into a list like this


But I'm getting a list like this


What I have circled in red is what I don't want. In this case, it is showing me I have much more beer than I really have. That would be great (haha), but I don't have that much beer. Imagine planning a party and your guest being disappointed when you run out. (joke)


Hope that makes more sense.

[andy49]

Any use?

It's a union query and a crosstab query

[dante2004]

In theory that works, but the problem is we have almost 1000 locations (thing large manufacturing warehouse). So we would have columns going to the right forever...

I'm going to restructure the table and see if that works...

[dante2004]

Okay...my idea with the tables adding a WHSE # didn't work either...

[aytee111]

There is a property called "Hide Duplicates", set this to Yes for Stock and Location

[dante2004]

There is a property called "Hide Duplicates", set this to Yes for Stock and Location

I tried that...and this is what I get...

If I hide duplicates for "storage bin" I get this



This is what my query looks like that feeds the report



Query design




If I hide duplicates for "stock" and "location"