SELECT MAX(DATE) 2

[straatlang]

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

 

[dodge20]

Have you tried any query yet. If you are going to select a regular column along with an aggregate such as max, you need to put the regular column(s) in a group by clause.

select securities.SECURITY,max(PRICEDAT),max(PRICE), CURR
from marketprices,securities,currency
where marketprices.SECURITY = securities.SECURITY
and securities.SECURITY = currency.SECURITY
group by SECURITY,CURR Dodge20

 

[straatlang]

I have tried this Query but it does not work. Maybe you have another idea.

Thanks in advance

Straatlang

 

[dodge20]

What is wrong with the query? Does that query leave off the records that have null in the pricedat and price field? Dodge20

 

[straatlang]

Access gives a errormessage:

"the component FROM has an syntaxerror"

 

[dodge20]

What are your datatype for your pricedate field? If it is a char, that might cause some problems. Otherwise I don't see any syntax errors. Check that you don't have an extra comma. Dodge20

 

[straatlang]

The data type is Date/time. And about the comma, I have just copy and paste your Query into access.

Is the tablename "CURRENCY" maybe a problem?

 

[Transcend]

More than likely currency will be a reserved word in Access.
Try putting it into square brackets [currency] or consider changing the table name.

Transcend

 

[straatlang]

SELECT securities.SECURITY, Max(marketprices.PRICEDATE) AS MaxOfPRICEDATE, Max(marketprices.PRICE) AS MaxOfPRICE, curren.CURR
FROM marketprices, securities, curren
WHERE (((marketprices.SECURITY)=[securities].[SECURITY]) AND ((securities.SECURITY)=[curren].[SECURITY]))
GROUP BY securities.SECURITY, curren.CURR;

This is what MS-ACCESS SQL look like now. Thanks to DODGE20


These are the tables I have changed the Tablename currency into CURREN (because this might be a reserved word in Access)

Table name CURREN

ID SECURITY CURR
1 ABC EUR
2 DEF USD
3 GHI USD
4 JKL USD
5 STU USD
6 ZZZ EUR

ID SECURITY PRICEDATE PRICE
1 ABC 12-12-2002 10
2 ABC 13-12-2002 11
3 ABC 14-12-2002 10
4 DEF 15-12-2002 20
5 GHJ 13-12-2002 21

ID SECURITY NAME
1 ABC ABC COMPANY
2 DEF DEF COMPANY
3 GHI GHI COMPANY
4 JKL JKL COMPANY
5 MNO MNO COMPANY


The result is:

SECURITY MaxOfPRICEDATE MaxOfPRICE CURR
ABC 14-12-2002 11 EUR
DEF 15-12-2002 20 USD



I would like to have a result where I would also see the
name of each security and also when there now Price available.


SECURITY NAME DATE PRICE CURR
ABC ABC COMP 14-12-2002 11 EUR
DEF DEF COMP 15-12-2002 20 USD
GHI GHI COMP
JKL JHL COMP
STU STU COMP
ZZZ ZZZ COMP

Is this visiable with MS-ACCESS or not.

Does anyone has a solution.

 

[dodge20]

If you use the union operator, you should get the desired results. I don't use the access union very much, but it SHOULD remove any duplicates that are returned between the 2 queries. Most versions of SQL do this, but I am not sure on access. Also the joins in the second query may not be needed, I just put them in there to be safe.

SELECT securities.SECURITY, Max(marketprices.PRICEDATE) AS MaxOfPRICEDATE, Max(marketprices.PRICE) AS MaxOfPRICE, curren.CURR
FROM marketprices, securities, curren
WHERE (((marketprices.SECURITY)=[securities].[SECURITY]) AND ((securities.SECURITY)=[curren].[SECURITY]))
GROUP BY securities.SECURITY, curren.CURR
UNION
SELECT distinct securities.SECURITY, marketprices.PRICEDATE AS MaxOfPRICEDATE, marketprices.PRICE AS MaxOfPRICE, curren.CURR
FROM marketprices, securities, curren
WHERE (((marketprices.SECURITY)=[securities].[SECURITY]) AND ((securities.SECURITY)=[curren].[SECURITY]))
AND marketprices.PRICE is null

Dodge20

 

[straatlang]

Hi Dodge,

I have tried your solution but the result is the same.

Regards

Straatlang

 

[dodge20]

Adding the name is simple, you just need to put it into your select statement. I did it in the query below.

As far as seeing them when the price is empty, I need to know if all of the security's are in all of the tables? The way I have the query set up, I am assuming that they are, but if the tables are exactly like you have displayed, it won't work correctly. For example, do you have every security in the marketprices table? If not, I would suggest putting them in.

SELECT securities.SECURITY,securities.NAME, Max(marketprices.PRICEDATE) AS MaxOfPRICEDATE, Max(marketprices.PRICE) AS MaxOfPRICE, curren.CURR
FROM marketprices, securities, curren
WHERE (((marketprices.SECURITY)=[securities].[SECURITY]) AND ((securities.SECURITY)=[curren].[SECURITY]))
GROUP BY securities.SECURITY, curren.CURR
UNION
SELECT distinct securities.SECURITY, securites.Name, marketprices.PRICEDATE AS MaxOfPRICEDATE, marketprices.PRICE AS MaxOfPRICE, curren.CURR
FROM marketprices, securities, curren
WHERE (((marketprices.SECURITY)=[securities].[SECURITY]) AND ((securities.SECURITY)=[curren].[SECURITY]))
AND marketprices.PRICE is null



Dodge20