By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

How To using Query to calculate from many Table

P: 6
Hi..

Im newbie and try to learn ms access, i use these syntax in query to calculate from different table.

Qry_NettoPerSPD

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Tbl_Rincian.IdSPD, Sum([jmlpsn]*[hargasat]/(1-[PPN]*0.1)*((100-[diskon])/100)) AS Netto
  3. FROM Tbl_Rincian
  4. GROUP BY Tbl_Rincian.IdSPD;
  5.  
Query2

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Tbl_Customer.IdCust, Tbl_SPD.TglSPD, Tbl_SPD.NoSPD, Tbl_PO.TglPO, Tbl_PO.NoPO, Qry_NettoPerSPD.Netto, Tbl_Customer.Contact, Tbl_Customer.Prus
  3. FROM Tbl_SuratJalan RIGHT JOIN (((Tbl_Customer INNER JOIN Tbl_PO ON Tbl_Customer.IdCust = Tbl_PO.idCust) INNER JOIN ((Tbl_SPD LEFT JOIN Tbl_SJdanSPD ON Tbl_SPD.NoSPD = Tbl_SJdanSPD.NoSPD) INNER JOIN Qry_NettoPerSPD ON Tbl_SPD.NoSPD = Qry_NettoPerSPD.IdSPD) ON Tbl_PO.NoPO = Tbl_SPD.idPO) INNER JOIN Tbl_Rincian ON Tbl_SPD.NoSPD = Tbl_Rincian.IdSPD) ON Tbl_SuratJalan.NoSj = Tbl_SJdanSPD.NoSJ
  4. GROUP BY Tbl_Customer.IdCust, Tbl_SPD.TglSPD, Tbl_SPD.NoSPD, Tbl_PO.TglPO, Tbl_PO.NoPO, Qry_NettoPerSPD.Netto, Tbl_Customer.Contact, Tbl_Customer.Prus;
  5.  
First time i making the Qry_NettoPerSPD and than Query2, is that correct ?
Anybody have different way to making more simple Query than above?


thank you for all advise...
bahenol
Aug 31 '07 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,615
If you can explain what it is you're hoping to achieve we could look at it for you. If you expect someone to reverse engineer your existing code so they can even find out what the question means then perhaps you need to reconsider. I await your response.
Sep 2 '07 #2

P: 6
I' m sorry about my question before, the question is... how to creating the result value from calculating field who related with the other table in just one query, i'm try to using 2 queries to have the calculation result between the relation table

First Query:

Qry_NettoPerSPD
Expand|Select|Wrap|Line Numbers
  1. SELECT Tbl_Rincian.IdSPD, Sum([jmlpsn]*[hargasat]/(1-[PPN]*0.1)*((100-[diskon])/100)) AS Netto
  2. FROM Tbl_Rincian
  3. GROUP BY Tbl_Rincian.IdSPD;
Second Query :

Query2

Expand|Select|Wrap|Line Numbers
  1. SELECT Tbl_Customer.IdCust, Tbl_SPD.TglSPD, Tbl_SPD.NoSPD, Tbl_PO.TglPO, Tbl_PO.NoPO, Qry_NettoPerSPD.Netto, Tbl_Customer.Contact, Tbl_Customer.Prus
  2. FROM Tbl_SuratJalan RIGHT JOIN (((Tbl_Customer INNER JOIN Tbl_PO ON Tbl_Customer.IdCust = Tbl_PO.idCust) INNER JOIN ((Tbl_SPD LEFT JOIN Tbl_SJdanSPD ON Tbl_SPD.NoSPD = Tbl_SJdanSPD.NoSPD) INNER JOIN Qry_NettoPerSPD ON Tbl_SPD.NoSPD = Qry_NettoPerSPD.IdSPD) ON Tbl_PO.NoPO = Tbl_SPD.idPO) INNER JOIN Tbl_Rincian ON Tbl_SPD.NoSPD = Tbl_Rincian.IdSPD) ON Tbl_SuratJalan.NoSj = Tbl_SJdanSPD.NoSJ
  3. GROUP BY Tbl_Customer.IdCust, Tbl_SPD.TglSPD, Tbl_SPD.NoSPD, Tbl_PO.TglPO, Tbl_PO.NoPO, Qry_NettoPerSPD.Netto, Tbl_Customer.Contact, Tbl_Customer.Prus;
How to calculate relation table with just one query (without Qry_NettoPerSPD) ?

Thank you for all advice, suggest and helpfull comment..
Sep 4 '07 #3

Jim Doherty
Expert 100+
P: 897
I' m sorry about my question before, the question is... how to creating the result value from calculating field who related with the other table in just one query, i'm try to using 2 queries to have the calculation result between the relation table

First Query:

Qry_NettoPerSPD

Code: ( sql )

1.
SELECT Tbl_Rincian.IdSPD, Sum([jmlpsn]*[hargasat]/(1-[PPN]*0.1)*((100-[diskon])/100)) AS Netto
2.
FROM Tbl_Rincian
3.
GROUP BY Tbl_Rincian.IdSPD;


Second Query :

Query2

Code: ( sql )

1.
SELECT Tbl_Customer.IdCust, Tbl_SPD.TglSPD, Tbl_SPD.NoSPD, Tbl_PO.TglPO, Tbl_PO.NoPO, Qry_NettoPerSPD.Netto, Tbl_Customer.Contact, Tbl_Customer.Prus
2.
FROM Tbl_SuratJalan RIGHT JOIN (((Tbl_Customer INNER JOIN Tbl_PO ON Tbl_Customer.IdCust = Tbl_PO.idCust) INNER JOIN ((Tbl_SPD LEFT JOIN Tbl_SJdanSPD ON Tbl_SPD.NoSPD = Tbl_SJdanSPD.NoSPD) INNER JOIN Qry_NettoPerSPD ON Tbl_SPD.NoSPD = Qry_NettoPerSPD.IdSPD) ON Tbl_PO.NoPO = Tbl_SPD.idPO) INNER JOIN Tbl_Rincian ON Tbl_SPD.NoSPD = Tbl_Rincian.IdSPD) ON Tbl_SuratJalan.NoSj = Tbl_SJdanSPD.NoSJ
3.
GROUP BY Tbl_Customer.IdCust, Tbl_SPD.TglSPD, Tbl_SPD.NoSPD, Tbl_PO.TglPO, Tbl_PO.NoPO, Qry_NettoPerSPD.Netto, Tbl_Customer.Contact, Tbl_Customer.Prus;

How to calculate relation table with just one query (without Qry_NettoPerSPD) ?

Thank you for all advice, suggest and helpfull comment..

A pointer for you....have a look at the concept of 'derived tables' and 'sub correlated' queries. There are a multitude of references on the web and online ready made simple examples that you can copy down, learn from and apply to your access environment.

Jim
Sep 4 '07 #4

P: 6
Ok.. Jim... thank you
Sep 4 '07 #5

NeoPa
Expert Mod 15k+
P: 31,615
Nice one Jim.
I'm afraid I was no better able to understand the question after the second version than after the first. The OP obviously made the effort so no criticism there, but I was still lost.
Anyway, I will try to ensure your status is updated by the end of the weekend at least.
Sep 4 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.