none
sql command for annual summary of shipments RRS feed

  • Question

  • 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

    Monday, September 10, 2018 5:42 AM

Answers

  • Both MS-Access and SQL-Server have the BETWEEN operator, TSQL version.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, September 10, 2018 9:21 AM
    Moderator