asked on

Subquery in FROM clause towards ms access from VB5

Hi,

I get an error message syntax error in FROM clause when i try to execute this query towards ms access. I am using VB 5 as dev environment.

The interesting thing is that i can execute this query successfully when i run it with a tool named AxBase (This tool is to write and run queries towards acess mdb)

SELECT T.VAT_PERCENT AS VAT_PERCENTAGE,SUM(T.GROSS) AS GROSS_TOTAL, ROUND(SUM(T.TOTAL),2) AS VAT_AMOUNT,GROSS_TOTAL-VAT_AMOUNT AS NET_AMOUNT FROM (SELECT SUM(TRANSACTION_VALUE) AS GROSS, DOC_PTR,VAT_PERCENT,ROUND(SUM((TRANSACTION_VALUE * VAT_PERCENT) / (100 + VAT_PERCENT)),2) AS TOTAL FROM POS_TRAN_LINES GROUP BY VAT_PERCENT,DOC_PTR,POS_ID) AS T GROUP BY T.VAT_PERCENT

pivar

Hi,

What is your error message?

/peter

reinhardtdjango

ASKER

syntax error in FROM clause
error code is 3131

pivar

Does this work?

SELECT T.VAT_PERCENT AS VAT_PERCENTAGE,SUM(T.GROSS) AS GROSS_TOTAL, ROUND(SUM(T.TOTAL),2) AS VAT_AMOUNT,GROSS_TOTAL-VAT_AMOUNT AS NET_AMOUNT FROM (SELECT SUM(TRANSACTION_VALUE) AS GROSS, DOC_PTR,VAT_PERCENT,ROUND(SUM((TRANSACTION_VALUE * VAT_PERCENT) / (100 + VAT_PERCENT)),2) AS TOTAL FROM POS_TRAN_LINES GROUP BY VAT_PERCENT,DOC_PTR,POS_ID) AS T GROUP BY T.VAT_PERCENT,GROSS_TOTAL-VAT_AMOUNT

reinhardtdjango

ASKER

Hi,

unfortunately not :(

pivar

Same error? I added GROSS_TOTAL-VAT_AMOUNT to the group by as this was missing.

pivar

By the way, what Access version do you have?

reinhardtdjango

ASKER

Access 97.

No not the same error this time i get "Parameter GROSS_TOTAL has no default value"

jparul

Try this:

Select VAT_PERCENTAGE, GROSS_TOTAL, VAT_AMOUNT, (Gross_total - Vat_Amount) from 
(SELECT  T.VAT_PERCENT AS VAT_PERCENTAGE,SUM(T.GROSS) AS GROSS_TOTAL, ROUND(SUM(T.TOTAL),2) AS VAT_AMOUNT,GROSS_TOTAL-VAT_AMOUNT AS NET_AMOUNT FROM 
(SELECT SUM(TRANSACTION_VALUE) AS GROSS, DOC_PTR,VAT_PERCENT,ROUND(SUM((TRANSACTION_VALUE * VAT_PERCENT) / (100 + VAT_PERCENT)),2) AS TOTAL FROM POS_TRAN_LINES GROUP BY VAT_PERCENT,DOC_PTR,POS_ID)  AS T  GROUP BY T.VAT_PERCENT)

Open in new window

reinhardtdjango

ASKER

Hi pivar,

No this one either doesent work.

But i think that i 've found the solution

you should use FROM clause subqueries like the example below for Access 97

SELECT X.POS_ID FROM [SELECT POS_ID FROM POS_TRAN_LINES]. AS X

you should use "[" these brackets and a dot "." after the subquery

The info is there

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=82&Number=1553500&Zf=&Zw=&Zg=10&Zl=b&Main=1544535&Search=true&where=&Zu=67878&Zd=l&Zn=&Zt=3e8&Zs=&Zy=

Thanks for your attention anyway

ASKER CERTIFIED SOLUTION

pivar

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer