Access Query to Excel Problem

[Chaper]

Hello, I have en Access created following Query:

SELECT p.Produkt AS Produkt,
(SELECT TOP 1 pr1.Pris FROM PriceReportData AS pr1 WHERE pr1.Produkt=p.Produkt AND pr1.Type="SYS" ORDER BY pr1.Dato DESC) AS SYS,
(SELECT TOP 1 pr2.Pris FROM PriceReportData AS pr2 WHERE pr2.Produkt=p.Produkt AND pr2.Type="DK1" ORDER BY pr2.Dato DESC) AS DK1,
(SELECT TOP 1 pr3.Pris FROM PriceReportData AS pr3 WHERE pr3.Produkt=p.Produkt AND pr3.Type="DK2" ORDER BY pr3.Dato DESC) AS DK2,
(SELECT TOP 1 pr4.Dato FROM PriceReportData AS pr4 WHERE pr4.Produkt=p.Produkt AND pr4.Type="SYS" ORDER BY pr4.Dato DESC) AS [SYS Tid],
(SELECT TOP 1 pr5.Dato FROM PriceReportData AS pr5 WHERE pr5.Produkt=p.Produkt AND pr5.Type="DK1" ORDER BY pr5.Dato DESC) AS [DK1 Tid],
(SELECT TOP 1 pr6.Dato FROM PriceReportData AS pr6 WHERE pr6.Produkt=p.Produkt AND pr6.Type="DK2" ORDER BY pr6.Dato DESC) AS [DK2 Tid]
FROM PriceReportData AS p
GROUP BY p.Produkt

It work fine in access, but when i'm in Excel try to connect to this Query it doesn't work as in Access.
Is there something wrong with the Query?

 

[noboffinme]

Hi Chaper

You'll need to write the VBA in Excel as the Objects are different in Access compared to Excel.

What do you mean by "Connecting to this query in Excel"?

You can access the results of the query to do something like export to Excel, can you explain what you're trying to do? Thanks

 

[Chaper]

I'm just jusing the build in function in Excel to connect to the Access database.
It is the one under det data tab..

I have aslo tried with msQuery and with VBA, but no matter which way i try to retrieve the data from the query, i'm getting no values for SYS, DK1 and DK2.
It shows me the correct values for Produkt, [Sys Tid], [DK1 Tid] and [DK2 Tid]

 

[Chaper]

I have attached an example of my database.

It is the results of the Query "PriceReport" i want in excel. But it must be linked, so when i update "PriceReportData" in access, the new data will show up in my excel file.

 

[noboffinme]

Hi Chaper,

Try changing your Query type from a 'Select' Query to a 'Make Table' Query instead.

Connect to the 'Make Table' table that is created from the query to Excel & try again.

HTH