Left Join

[MrJukas]

Hello,



I have a question with Access. I hope some of you would find the time to assist me.

Problem:
Table1: Item list
Table2: Item list , Location , Quantity


With a left join Table1->Table2 on the Item list.


What I am looking for:
I'd like to have all items from Table1 and for those that are in Table2, I want to see the quantity in the location "UK".

Is it clear enough?

When I try to put the criteria "UK" for the field Location, it returns only the items from Table2 that have location as "UK".

I assume that I cannot use left join, can I?

thank you guys.
Jukas

[orange]

A few sample records from each table would be helpful.

[June7]

Don't understand. If you filter by "UK" then only records with UK will display. If you want to show all Items but Quantity only when Location is UK, use an expression in query that returns Null if location is not UK and don't filter records.

IIf(Location="UK", Quantity, Null)

But why show a location if you don't want to show Quantity?

[MrJukas]

Table1

Item

A

B

C

Table2

Item	Location	quantity
A	USA	1
A	UK	2
B	USA	3
B	UK	4

What I d like to see

Item	Location	Quantity
A	UK	2
B	UK	4
C

Makes sense?

[MrJukas]

Don't understand. If you filter by "UK" then only records with UK will display. If you want to show all Items but Quantity only when Location is UK, use an expression in query that returns Null if location is not UK and don't filter records.

IIf(Location="UK", Quantity, Null)

But why show a location if you don't want to show Quantity?

Your formula makes perfectly sense, and I was actually thinking about using something like that. But I was wondering if it would have been possible something without Iif()

thanks

[ItsMe]

While in design view of your query object, double click the line that connects the two tables (join). That should bring up a properties dialog box. Choose the option, "Include all records from Table1 and only those ..."

With this you should be able to type Like 'UK' in the criteria. Or you can just type 'UK' for an exact match. You can uncheck the box to view your Location column. And be sure to include the Quantity column in your query by dragging it from the table onto the grid.

[June7]

Maybe with a series of queries.

Query1
to filter records to UK

Query2
RIGHT join query 1 to list of locations

[MrJukas]

Unfortunately, with the left join it gives only the UK values.

I was thinking to use <>USA criteria, but it wasn't 100% successful

[ItsMe]

Did you try using single quotes around USA? <> 'USA'

[June7]

I said RIGHT join.

If you want to provide db for analysis and testing, follow instructions at bottom of my post.

[ItsMe]

I said RIGHT join...

Thanks for the clarification.

[DrGUI]

Use a LEFT join but for the Location criteria, use: 'UK' or Is NULL

[ItsMe]

Use a LEFT join but for the Location criteria, use: 'UK' or Is NULL

That makes sense because the <> will not retrieve Null.

[MrJukas]

Quick update.
I tried the iff (), it didn't work because it considers USA values a 0 quantity, so I have a table duplicates with 0s.
Yet, I might try to tell him to sum them, rather than group it by. In theory it should work.

I am not sure the left join with UK or is null would work. I haven't really the time to try it.

I ll get back to you all soon, most likely over the weekend.
Thanks for the help