SQL help needed....... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL help needed.......

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2004-03-25 : 07:47:54

Access 97.................

I'm trying to construct the following SQL statement but am missing something somewhere.

I get a syntax error with the 1st FROM highlighted.

If i construct a query out of the bit that works and then construct a query on that query then i can get it to work no problem but how to mimic that in SQL ? I can't simply view the SQL of the queries because the query names replace the SQL statements.

I'm trying to SUM the 5 volumes that get returned by the TOP 5 SQL statement. The VotingUniverse_TotalVolumeByType query returns the TotalVolume which will enable me to divide the SUMmed 5 volumes by the TotalVolume to obtain a %age.


SELECT
Type,
Sum(Volume) AS Volume,
TotalVolume
FROM
>>>>>>>>>>this following bit works............
(SELECT TOP 5 
VotingUniverse_TopFirmsVolumeByType.Type, 
VotingUniverse_TopFirmsVolumeByType.Volume
FROM 
VotingUniverse_TopFirmsVolumeByType
WHERE 
(((VotingUniverse_TopFirmsVolumeByType.Volume) 
In (SELECT TOP 5 VotingUniverse_TopFirmsVolumeByType.Volume
FROM 
VotingUniverse_TopFirmsVolumeByType
ORDER BY Volume DESC)))
ORDER BY 
VotingUniverse_TopFirmsVolumeByType.Volume;)
>>>>>>>>>>>end of bit that works
INNER JOIN
VotingUniverse_TotalVolumeByType
ON
Type = VotingUniverse_TotalVolumeByType.Type
GROUP BY
Type,
TotalVolume;

hope this makes sense to someone anyway

thanks

====
Paul

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2004-03-29 : 09:31:25

Problem solved

====
Paul

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-30 : 01:06:04

Was it the missing alias on the derived table? I am not even sure if that works in Access...

OS

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2004-03-30 : 04:18:04

i did it a different way in the end so couldn't tell you i'm afraid.

i ended feeding in a variable to determine the TOP 1-5, 6-10, 11-15 up using the CreateQueryDef to physically create the query and then constructed another query using the newly created one and this worked fine. Not the fastest i'm sure but not a production level query so not a big problem........


    For L = 1 To 5          'the SUPER TYPEs
    intCol = 1              'reset the column count for the next row
        For j = 5 To 20 Step 5      'the TOP columns 1-5, 6-10, 11-15, 16-20
            intCol = intCol + 1     'next column reference
            SQL_STR = "SELECT TOP 5 Type, Volume FROM VotingUniverse_TopFirmsVolumeByType "
            SQL_STR = SQL_STR & "WHERE Volume in (SELECT TOP " & j & " VotingUniverse_TopFirmsVolumeByType.Volume "     'feed in a variable
            SQL_STR = SQL_STR & "FROM VotingUniverse_TopFirmsVolumeByType ORDER BY Volume DESC) "
            SQL_STR = SQL_STR & "ORDER BY Volume ASC;"
        
            Set SubRank_Q = dbs.CreateQueryDef("VotingUniverse_Select_Top", SQL_STR)    'create this query to use in another

        ''''open a query to obtain the Volumes and %ages........using the created query above......returns 1 row
            SQL_STR = "SELECT VotingUniverse_TopTotalVolume.Type, VotingUniverse_TopTotalVolume.Volume, "
            SQL_STR = SQL_STR & "VotingUniverse_TopTotalVolume.TotalVolume, [Volume]/[TotalVolume] AS Perc "
            SQL_STR = SQL_STR & "FROM VotingUniverse_TopTotalVolume;"

            Set qdfTop = dbs.CreateQueryDef("", SQL_STR)
            qdfTop("Print_Order") = L        'filter to a specific super type
            
            Set rstTop = qdfTop.OpenRecordset(dbOpenSnapshot)
            
        ''''append to array
            InstType(L, intCol) = rstTop!Type
            Volume(L, intCol) = rstTop!Volume
            Score(L, intCol) = rstTop!perc

            DoCmd.DeleteObject acQuery, "VotingUniverse_Select_Top"     'remove to enable modification and recreation next loop
            
            rstTop.Close
            Set rstTop = Nothing
        Next j
    Next L

====
Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources