444,027 Members | 1,313 Online
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   SELECT Tbl_Rincian.IdSPD, Sum([jmlpsn]*[hargasat]/(1-[PPN]*0.1)*((100-[diskon])/100)) AS Netto FROM Tbl_Rincian GROUP BY Tbl_Rincian.IdSPD;   Query2 Expand|Select|Wrap|Line Numbers   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 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 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;   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
5 Replies

 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 SELECT Tbl_Rincian.IdSPD, Sum([jmlpsn]*[hargasat]/(1-[PPN]*0.1)*((100-[diskon])/100)) AS Netto FROM Tbl_Rincian GROUP BY Tbl_Rincian.IdSPD; Second Query : Query2 Expand|Select|Wrap|Line Numbers 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 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 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

 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

 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