Two queries limits results

[Gkirkup]

I have two queries combined into one query. The first query shows a sales summary by part number. No problem so far. The second query shows inventory by part number. No problem with that.
Now, I combine the queries, and get only those results that appear in BOTH queries. In other words, if there is no inventory, I do not get zero in the inventory column, the entire result for that part number is skipped.
How do I still display the results of the first query if there is no match in the second query?

 

[Frivolous Sam]

What you need to do is to choose the join type for your combined query.

At the moment, you have a join where only results for which there are matches in both tables are shown. In SQL this is called an INNER JOIN.

What you want is one of three things:

1. all items from your inventory query and only matching items from your sales query (LEFT OUTER JOIN in SQL)
2. all items from your sales query and only matching items from your inventory query (RIGHT OUTER JOIN in SQL)
3. items which appear in either list (FULL OUTER JOIN)

In Access' query window for your combined query, you can right-click on the relationship you created between the two queries and change the join properties to any of these options.

 

[neileg]

Sam, how do you create a full outer join in an Access MDB?

 

[Frivolous Sam]

You have to union a left outer join and a right outer join.

It was included for completeness -- just because Access doesn't do it natively doesn't mean you don't need to know about it.

 

[neileg]

Thanks Sam. I knew you could do it in an ADP.

 

[Gkirkup]

Thanks for your complete response. I will give that a try.

Robert

 

[Gkirkup]

I combined the two queries and all looks good - I get inventory only where there is a record, and a blank otherwise. But my 'Top 50 part numbers by sales' (my original query) no longer groups the like part numbers into single totals. I get the same part number appearing in different parts of my Top 50 list. How do I make sure that the part number totals are still grouped together?
Robert

 

[Frivolous Sam]

Show me the problem, either with screenshots or by demonstrating a simplified version of before and after...

 

[Gkirkup]

Sam:
Here is an example of the 'before'. This is a Top 50 list, of sum of sales by part number:

Part number Sales
D4CD200 765,000
E1IL121 276,899
DCCU000 87,000
E1SJ500 46,000
T1R1B20 29,000

Now I add the inventory query, and the part numbers appear multiple times - the totals are no longer combined, like this:

Part number Sales Inventory
D4CD200 320,000 35
E1IL121 276,899 200
D4CD200 120,000 35
D4CD200 100,000 35
DCCU000 87,000 6

As you can see, D4CD200 used to have a combined total of 765,000. I add the inventory query, and the total for that part becomes fragmented into several lines - presumably individual sales totals, and not combined into one line as before.
Hope this helps. Thank you.

Robert

 

[EdFred]

Dumb question, but have you clicked the "sigma" button which allows you to show sums, averages, counts, etc?