I need to eliminate duplicates in a query

[jwrocko]

I need to be able to compare two bills of material against each other and only view the differences between the two. I'd like to have a report setup with drawing_info_a on the left and drawing_info_b on the right.

pertinent applicable information:

Tables:
BOM Query - this holds Part_Number and Component (description) fields (only information pertinent to this query) for (respectively) drawing_info_a

BOM Compare - this holds Part_Number and Component (description) fields for drawing_info_b

I have an unmatched query working now that just shows the difference of drawing_info_a from drawing_info_b.

this works well if two lists have the same number of parts. however, if list a has 5 parts and list b has 10 parts, my difference query will yield semi-correct results.

 

[GingerR]

So you could have two queries: one that shows what is in A that is not in B; and one that shows things in B that are not in A. Then in a report, you could make two sub-reports side-by-side using these queries as the subreports' recordsources.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[ck1999]

Have you tried an outer join?


ck1999

 

[jwrocko]

so, GingerR, you are saying to have two unmatched queries?

about outer join - im kind of lost on the whole query. can you give me an example of what you are talking about?

 

[GingerR]

Yes - sounds like you want to see what's in A that's not in B, and what's in B that's not in A.

After that, it depends on how you want to display them as to what you can do. Side-by-side on a report like I said above, or other options depending on what your requirements are.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jwrocko]

ok, thanks, ill try it out.

 

[jwrocko]

When I do my unmatched queries, I need these fields...
[BOM Query].

Drawing_Number, Drawing_Title, Drawing_Description, Additional_Description, Part_Number, Component, Quantity.

Currently, if the number I am comparing against contains all the parts listed in the main number, I get zero results. I still need to keep the Drawing_Number, Drawing_Title, Drawing_Description, Additional_Description information. GingerR?

 

[GingerR]

Maybe I don't understand what you are saying, but if you get zero results, then how do you expect to see any info?

Please post your table structure(s) and sample data as well as your queries' SQL statements and what you want the results to look like. Thanks.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jwrocko]

first query:

SELECT [BOM Query].Drawing_Number, [BOM Query].Drawing_Title, [BOM Query].Drawing_Description, [BOM Query].Additional_Description, [BOM Query].Part_Number, [BOM Query].Component, [BOM Query].Quantity
FROM [BOM Query] LEFT JOIN [BOM Compare] ON [BOM Query].Part_Number=[BOM Compare].Part_Number
WHERE ((([BOM Compare].Part_Number) Is Null));

the second is an exact opposite of the first - query is replaced with compare and vice versa.

 

[GingerR]

Does this work as you wish?
Please post the second query. Does it work as you wish?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jwrocko]

SELECT [BOM Compare].Drawing_Number, [BOM Compare].Drawing_Title, [BOM Compare].Drawing_Description, [BOM Compare].Additional_Description, [BOM Compare].Part_Number, [BOM Compare].Component, [BOM Compare].Quantity
FROM [BOM Compare] LEFT JOIN [BOM Query] ON [BOM Compare].Part_Number=[BOM Query].Part_Number
WHERE ((([BOM Query].Part_Number) Is Null));

they both work as they need to...I just need to retain certain information that must be created dynamically

 

[GingerR]

Sorry, I'm lost. What is it you need to do? Looks like you have two queries, one that shows records in BOM Compare that are not in BOM QUERY, and one that shows records in BOM QUERY that are not in BOM Compare. Seems that's what you originally asked for? I've read thru this whole string of posts but don't see what it is you are not getting that you need....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jwrocko]

yes. what I need now is to retain each BOM's number and information - drawing_number, drawing_title, drawing_description and additional description.

 

[GingerR]

Again, what exactly is it you want to see that you are not seeing (SAMPLE DATA PLEASE!!!!!!)

What do you mean by "retain"? Aren't you getting that information from your queries? I imagine it's spitting out that info because that's what your query calls for.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jwrocko]

The problem I am having is that when one query does not return any results...it does just that - not return any results. That means that it does not provide my drawing-specific information (drawing_number, _title, _description, additional_info). So, this results in one "side" of the report having no information.

These empty results occur when a drawing number (supplied to the second query) contains the same part numbers as the drawing number (supplied to the first query). This does not mean the BOM (drawing) numbers are the same.

drawingA has this list of part numbers: 1, 2, 3, 4
drawingB has this list of part numbers: 1, 2, 3, 4, 5, 6

this will lead to the left side (which has information about drawingA) being completely devoid of information while the right side (drawingB) will have 5 and 6 left as well as the pertinent drawing information (since the information was gathered in the unmatched query).

So when I say I need to "retain" information, I mean that, when I have such a situation happen, I need to keep the drawing-specific information.

This is leading me to think I need to create 2 MORE queries just to hold the drawing information. The existing queries can remain as they are.

Is this a little more helpful?

 

[GingerR]

Yes--Drawing Information is different and gathered differently than PART information. The queries are simply to determine which PARTS are diff/not there; you're trying to do two separate things in one query.

If you still have problems, type in here exactly what you are looking for so we can help you. I can't envision how you are wanting to put dwg info on a report - is each 'record' the drawing info, while each subreport is parts that have to do with the drawing? Or will you have many rows of data in each subreport that have drawings and parts in them?

Anyhow, if you still need help, write back.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html