How To using Query to calculate from many Table

[bahenol]

Hi..

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

Qry_NettoPerSPD

 
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

 
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

[NeoPa]

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.

[bahenol]

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

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

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..

[Jim Doherty]

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

[bahenol]

Ok.. Jim... thank you

[NeoPa]

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.