select from securities

[straatlang]

I hope someone can help me with the next problem.

I have a table bookings

NR ID Quantity SECNAME ID
1001 1 1000 abc 5
1002 2 2000 def 6
1003 3 3000 ghj 7
1004 4 4000 klm 8

and a table names

ID Type country
1 Client US
2 Client US
3 Client US
4 Client CAD
5 EXCHANGE SP
6 EXCHANGE SP
7 EXCHANGE CAD
8 EXCHANGE US

I have to report all bookings when a client = US and The Stockexchange <> US.

I hope someone can help me out.

Regards

Straatlang

 

[dodge20]

How can you have 2 id fields in 1 table? Which id field do you want to match up with the id in the names table?

Dodge20

 

[dodge20]

ok maybe your other field is name id? Just a tip when creating field names, it is best not to put a space between 2 words, it is best to seperate them with a _

select bNR, b.ID, b.Quantity, b.SEC, b.Name_ID from bookings b, names n
where b.Name_id = n.id
and n.type = 'Client'
and n.country != 'US'

Dodge20

 

[straatlang]

I change ID to ID_name and this what I copy and paste from Access with some small adjustments like b.nr but it does not give me the expected result I also removed '!' from the query

so this is what I have now.

SELECT b.NR, b.ID, b.Quantity, b.SEC, b.Name_ID
FROM bookings AS b, [names] AS n
WHERE b.Name_id=n.id And n.type='Client' And n.country='US';

I would like to see all trades (bookings) done by a US resident on a none US exchange. So I expect a result like

NR ID Quantity SEC Name_ID
1001 1 1000 abc 5
1002 2 2000 def 6
1003 3 3000 ghj 7

1001 1=US 5<>US
1002 2=US 6<>US
1003 3=US 7<>US
1004 4<>US 8=US this should not appear

Who can help me.

Regards

Straatlang

 

[dodge20]

What are the results that you are getting? The way you have your query, the 8th record shouldn't be showing up, because it isn't a client in the type field.

Dodge20

 

[rzff]

Dear Dodge,

Just to let know. What I have now is the following.

bookings

NR ID Quantity Sec Exchange
1001 1 10000 Abc 5
1002 2 2000 def 6
1003 3 3000 ghj 7
1004 4 4000 klm 8


Names

ID Type Country
1 client US
2 client US
3 client US
4 client CAD
5 exchange SP
6 exchange SP
7 exchange CAD
8 exchange CAD

I have tried your query with some changes this is how it looks now in MS-Access I have copy and paste
it.

SELECT b.NR, b.ID, b.Quantity, b.SEC, b.exchange
FROM bookings AS b, [names] AS n
WHERE b.exchange=n.id And n.type='Client' And n.country='US';


I get no result after running it.

But I received another solution from a Tek-Tips user (after changing the table a little bit and refrasingng
my question.

SELECT b.nr, b.id, c.country AS ccntr, b.quantity, b.sec, b.exchange, e.country AS ecntr
FROM bookings AS b, [names] AS c, [names] AS e
WHERE b.id=c.id And b.exchange=e.id And c.country='US' And e.country<>'US';


And this works correctly.Thank you anyway for try to help me out.

regards
rzff