SELECT FROM WHERE 1

[straatlang]

have two tables :

Articles

ID Name
123 AAA
456 BBB
789 CCC
888 DDD

Prices

ID Price
123 10
456 20
789 30
999 40

The query should result in a list where all information that is available
is visiable. SO infact in need to combine these two tabels.

ID Name Price
123 AAA 10
456 BBB 20
789 CCC 30
888 DDD
999 40

Does anyone knows if this possible with MS-ACCESS.

 

[Trendsetter]

Start a new query:
Bring both tables (Articles & Prices) to the grid and connect the two together by dragging ID from one to the other which will leave a relationship.
In the grid drag ID & Name from Articles & Price from Prices.
Set the sort by on ID to ASCD and run the query... this should produce the required result.

 

[r937]

trendsetter, won't that just produce an inner join?

the way i read it, the problem requires a full outer join, which access doesn't support, so it has to be done like this:

[tt]select A.ID, A.Name, P.Price
from Articles A
left outer
join Prices P
on A.ID = P.ID
union all
select P.ID, A.Name, P.Price
from Articles A
right outer
join Prices P
on A.ID = P.ID
where A.ID is null[/tt]

rudy

http://r937.com/

 

[straatlang]

Hi Rudy,

I have tried this and your Query is correct.

Thank you very much.

Straatlang

Maybe you also have the answer on the following:


I want to create query from three Tables Marketprices, security and currency ?


Marketprices

SECURITY PRICEDAT PRICE
ABC 12/12/02 10
ABC 13/12/02 11
ABC 14/12/02 10
DEF 15/12/02 20
GHJ 13/12/02 21

Securities

SECURITY NAME
ABC ABC Company
DEF DEF Company
GHI GHI Company
JKL JKL Company
MNO MNO Company

Currency

SECURITY CURR
ABC EUR
DEF USD
GHI USD
JKL USD
STU USD
ZZZ EUR


The result should be the last know price and pricedate on every security.
But i also want to see every security and the name of the company and currency when available in the report including the ones without a date and price field.

Expected result

SECURITY DATE PRICE CURR
ABC 14/12/02 10 EUR
DEF 15/12/02 20 USD
GHI 16/12/02 25 USD
JKL USD
MNO
STU USD
ZZZ EUR

 

[swampBoogie]

select security,
 (select max(pricedat) from marketprices
     where security = s.security) as maxdate,
 (select price from marketprices mp
   where pricedat = 
      (select max(pricedat)
         from marketprices
        where security = mp.security)
     and security = s.security )) as price,
  curr
  from security s left join currency c
    on s.security = c.security
union 
select security,
 (select max(pricedat) from marketprices
     where security = s.security) ,
 (select price from marketprices mp
   where pricedat = 
      (select max(pricedat)
         from marketprices
        where security = mp.security)
     and security = s.security )),
  curr
  from security s right join currency c
    on s.security = c.security

It is possible it will make Access choke.

 

[straatlang]

You are right access chokes.

regards

Straatlang

 

[markphsd]

You could do it. You'd get something like a cartesian query. You would get more then what you think are supposed to get though, and those null numbers for either side will be hassles.

I think the problem here is that the ID field isn't really the same for both, they are just named the same; maybe they should be articleID and PriceID. You might have a better time using some action queries to make a table, then append the information into the table.

I think though, that the tables seem to be disparate and not holding any relationship. Unless someone just decided to store the price for products and their name in separate tables, that would probably indicate those aren't the only fields avalible. I would go for the make table query and append personaly.

Hope that helps.
Mark P.

Bleh