M
MedicalDoctorProgrammer
Guest
so i have two tables
one called orders where i write the name of all the companies i am ordering from in addition to some other info
the another table called shipments where i add any shipment i ordered from and those table are related to each other on id=rel
both tables have column CT refer to number of containers where in the parent table it calculate automatically the number of all the shipments containers under it in the child table
while in the child table each shipment has its own container
so i made one report using this code
"SELECT ORDERS.ID,ORDERS.CONTENTS, orders.ct AS [Total Shipments],orders.[Date],SUM(eta.ct * IIF(recd IS NULL, 0,1)) AS [Arrived],SUM(eta.ct * IIF(recd IS NULL, 1,0)) AS [Not Arrived] FROM ORDERS INNER JOIN ETA ON ORDERS.ID = ETA.REL GROUP BY orders.id,ORDERS.CONTENTS, ORDERS.DATE,orders.ct ORDER BY ORDERS.DATE"
to make report that show all shipments data of certain company in one report
now i want to make another report uses the same idea but only show me
company name(aka contents) ---- total shipments----arrived------not arrived
which what the previous code does but this time with date influance
like i want to know from 1/1/2018 to 30/6/2018 how much i ordered from each company
the date column is in the shipment table(child table)
i tried this code but it didnt work
"SELECT ORDERS.ID,ORDERS.CONTENTS, orders.ct AS [Total Shipments],orders.[Date],SUM(eta.ct * IIF(recd IS NULL, 0,1)) AS [Arrived],SUM(eta.ct * IIF(recd IS NULL, 1,0)) AS [Not Arrived] FROM ORDERS INNER JOIN ETA ON ORDERS.ID = ETA.REL where eta.[date]>#" & dtpFrom.Text & "# and eta.[date]<#" & dtpTo.Text & "# GROUP BY orders.id,ORDERS.CONTENTS, ORDERS.DATE,orders.ct ORDER BY ORDERS.DATE"
I am A Medical Doctor
Continue reading...
one called orders where i write the name of all the companies i am ordering from in addition to some other info
the another table called shipments where i add any shipment i ordered from and those table are related to each other on id=rel
both tables have column CT refer to number of containers where in the parent table it calculate automatically the number of all the shipments containers under it in the child table
while in the child table each shipment has its own container
so i made one report using this code
"SELECT ORDERS.ID,ORDERS.CONTENTS, orders.ct AS [Total Shipments],orders.[Date],SUM(eta.ct * IIF(recd IS NULL, 0,1)) AS [Arrived],SUM(eta.ct * IIF(recd IS NULL, 1,0)) AS [Not Arrived] FROM ORDERS INNER JOIN ETA ON ORDERS.ID = ETA.REL GROUP BY orders.id,ORDERS.CONTENTS, ORDERS.DATE,orders.ct ORDER BY ORDERS.DATE"
to make report that show all shipments data of certain company in one report
now i want to make another report uses the same idea but only show me
company name(aka contents) ---- total shipments----arrived------not arrived
which what the previous code does but this time with date influance
like i want to know from 1/1/2018 to 30/6/2018 how much i ordered from each company
the date column is in the shipment table(child table)
i tried this code but it didnt work
"SELECT ORDERS.ID,ORDERS.CONTENTS, orders.ct AS [Total Shipments],orders.[Date],SUM(eta.ct * IIF(recd IS NULL, 0,1)) AS [Arrived],SUM(eta.ct * IIF(recd IS NULL, 1,0)) AS [Not Arrived] FROM ORDERS INNER JOIN ETA ON ORDERS.ID = ETA.REL where eta.[date]>#" & dtpFrom.Text & "# and eta.[date]<#" & dtpTo.Text & "# GROUP BY orders.id,ORDERS.CONTENTS, ORDERS.DATE,orders.ct ORDER BY ORDERS.DATE"
I am A Medical Doctor
Continue reading...
