How to modify SQL to make * = all in join query

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
672
I have Table A like this

Country, Product, Sales
==========
US, A, 100
UK, A, 200
US, B, 150
UK,B, 350

In Table A it is like this

Country, Product, Rate
===============
US, A, 0.9
UK, *, 0.7

I want to join these 2 tables if both Country and Product are same as those in Table B, so that Rate is added to right side of Table A,
and my challenge here is that I need the "*" will select all Products, and so expected outcome is:

Country, Product, Sales, Rate
==========
US, A, 100, 0.9
UK, A, 200, 0.7
UK, B, 350, 0.7

What should be the jet SQL ?
Pls help, thanks

That "*" can also be something else like % etc.
 
Last edited:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,527
One solution is to generate the value for UK on the fly. So here I have called your first table Table1 and the second table Table2.

With this approach, queries 1 and 2 work together with query 3 to create a list of all the rates without the asterisk or wildcard values (using products for the country in question as found in Table 1).

Then we can run the query that is needed using this expanded list.


Query001:
Code:
select distinct Country, Product 
from TableA
Query002:
Code:
select distinct Country, Rate 
from TableB 
where Product = '*'
Query003:
Code:
select q2.Country, q1.Product, q2.Rate 
from 
	Query002 q2 
	inner join Query001 q1 
	on q1.Country = q2.Country

union

select Country, Product, Rate 
from TableB 
where Product <> '*'
Query004:
Code:
select 
	t1.Country, t1.Product, t1.Sales, q3.Rate
from 
	Query003 q3 
	inner join TableA t1 
	on q3.Country = t1.Country
	and q3.Product = t1.Product

Just to be clear, Query003 generates the pseudo table as below:

----------------------------
| Country | Product | Rate |
----------------------------
| UK      | A       |  0.7 |
| UK      | B       |  0.7 |
| US      | A       |  0.9 |
----------------------------


and then Query004 generates the result:

------------------------------------
| Country | Product | Sales | Rate |
------------------------------------
| US      | A       |   100 |  0.9 |
| UK      | A       |   200 |  0.7 |
| UK      | B       |   350 |  0.7 |
------------------------------------


In SQL Server I would probably combine all this into one query with subqueries, but with MSAccess it generally seems to work better to break subqueries up into separate saved queries that can call each other.
 
Last edited:

Forum statistics

Threads
1,078,486
Messages
5,340,626
Members
399,387
Latest member
amrita34

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top