<div>
Hi,<br />
<br />
What is your error message?<br />
<br />
/peter<br />
<br />

</div>


<div>
syntax error in FROM clause<br />
error code is 3131<br />
<br />

</div>


<div>
Does this work?<br />
<br />
SELECT &nbsp;T.VAT_PERCENT AS VAT_PERCENTAGE,SUM(T.GROSS<wbr />) AS GROSS_TOTAL, ROUND(SUM(T.TOTAL),2) AS VAT_AMOUNT,GROSS_TOTAL-VAT<wbr />_AMOUNT AS NET_AMOUNT FROM (SELECT SUM(TRANSACTION_VALUE) AS GROSS, DOC_PTR,VAT_PERCENT,ROUND(<wbr />SUM((TRANS<wbr />ACTION_VAL<wbr />UE * VAT_PERCENT) / (100 + VAT_PERCENT)),2) AS TOTAL FROM POS_TRAN_LINES GROUP BY VAT_PERCENT,DOC_PTR,POS_ID<wbr />) &nbsp;AS T &nbsp;GROUP BY T.VAT_PERCENT,GROSS_TOTAL-<wbr />VAT_AMOUNT<wbr /><br />

</div>


<div>
Hi,<br />
<br />
unfortunately not :(
</div>


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


<div>
By the way, what Access version do you have?
</div>


<div>
Access 97.<br />
<br />
No not the same error this time i get &nbsp; &quot;Parameter GROSS_TOTAL has no default value&quot;
</div>


<div>
Try this:<br />
&nbsp;<br />
<br />

<pre><code class="code-1 nolinks" id="code-260208"><span>Select VAT_PERCENTAGE, GROSS_TOTAL, VAT_AMOUNT, (Gross_total - Vat_Amount) from </span>
<span>(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 </span>
<span>(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) </span>
</code></pre>
</div>


<div>
Hi pivar,<br />
<br />
No this one either doesent work.<br />
<br />
But i think that i 've found the solution <br />
<br />
you should use FROM clause subqueries like the example below for Access 97<br />
<br />
SELECT X.POS_ID FROM [SELECT POS_ID FROM POS_TRAN_LINES]. AS X<br />
<br />
you should use &quot;[&quot; &nbsp;these brackets and a dot &quot;.&quot; after the subquery<br />
<br />
The info is there<br />
<br />
<a href="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=" rel="ugc">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=</a><br />
<br />
Thanks for your attention anyway<br />

</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
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.<br />
<br />
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)<br />
<br />
SELECT &nbsp;T.VAT_PERCENT AS VAT_PERCENTAGE,SUM(T.GROSS<wbr />) AS GROSS_TOTAL, ROUND(SUM(T.TOTAL),2) AS VAT_AMOUNT,GROSS_TOTAL-VAT<wbr />_AMOUNT AS NET_AMOUNT FROM (SELECT SUM(TRANSACTION_VALUE) AS GROSS, DOC_PTR,VAT_PERCENT,ROUND(<wbr />SUM((TRANS<wbr />ACTION_VAL<wbr />UE * VAT_PERCENT) / (100 + VAT_PERCENT)),2) AS TOTAL FROM POS_TRAN_LINES GROUP BY VAT_PERCENT,DOC_PTR,POS_ID<wbr />) &nbsp;AS T &nbsp;GROUP BY T.VAT_PERCENT<br />
<br />

</div>
</div>


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



Subquery in FROM clause towards ms access from VB5

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.