Please help got access query but can't get it to work on sql-server - what's the access specific par

  • Thread starter Thread starter Kim
  • Start date Start date
K

Kim

I've got this query that works just fine in access.

In it's query it makes use of some linked tables that it groups and sums,
taking the query into sql-server doesn't work and I need some help
understanding it

As I understood from reading access uses (can use) functions such as sum and
joins to return whole tables (resultsets from tables that it then goups ?),
I don't get it and I don't understand how to translate a query like the one
below to get it to work on sql-server.

If you can help me out with this I'd appreciate it

If you can't examplify the weid join for me using these tables, please use
others and help me out...
How can you do like this in sql-server ((PROJ INNER JOIN (TS INNER JOIN TASK
ON TS.task_id = TASK.task_id) ON PROJ.ws_id = TASK.ws_id) ?


SELECT PROJ.ws_id,
X_RS.RS_TT__rs_ca_sh_name,
X_RS.RS_AR__rs_ca_sh_name,
X_RS.RS_KT__rs_ca_sh_name,
T_SPR.start_date, Sum(T_SPR.act_qty) AS Sum_qty,
Sum(T_SPR.remain_qty) AS Sumremain_qty,
Sum(T_SPR.act_cost) AS Sumact_cost,
Sum(T_SPR.remain_cost) AS Sumremain_cost

FROM

((PROJ INNER JOIN (TS INNER JOIN TASK ON TS.task_id = TASK.task_id) ON
PROJ.ws_id = TASK.ws_id) INNER JOIN T_SPR ON TS.taskrsrc_id =
T_SPR.taskrsrc_id) INNER JOIN X_RS ON TS.rsrc_id = X_RS.rsrc_id GROUP BY
PROJ.ws_id, X_RS.RS_TT__rs_ca_sh_name, X_RS.RS_AR__rs_ca_sh_name,
X_RS.RS_KT__rs_ca_sh_name, T_SPR.start_date;
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This should work fine in SQL'r. Q: Are there tables/views in the
SQL'r db named: Proj, TS, Task, T_SPR, X_RS ?

An Access .mdb file can link to tables from many dbs. Are any of the
linked tables in other dbs than the one in which you put the above
query? If so, then you need to link to the other dbs (SQL'r) by
referring to the dbs. E.g.:

Syntax:
<database>.<owner>.<table>

E.g.:
AR..debits

to refer to the debits table in the AR database (dbo is default
owner).

To link to another SQL'r server: See the BOL article: "How to set up
a linked server."


MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQEmv5oechKqOuFEgEQJdowCgnVyNfAyXLDt9qAt9pCv6tvolL2gAn1q8
AruKL63GogQHo9gP5GomoHBu
=9ibm
-----END PGP SIGNATURE-----
 
Back
Top