SQL help

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • R.Chan
    #1

    SQL help

    Hi,
    I'm stuck on the following query. Any help would be greatly appreciated!

    table1: Customers
    uid, name
    1, bob
    2, jane
    3, john

    table2: Purchases
    uid, custID, datepurchase, item
    1, 1, 1/1/2004, mouse
    2, 1, 1/2/2004, keyboard
    3, 1, 1/3/2004, usb key
    4, 2, 1/1/2004, mouse
    5, 2, 6/19/2004, keyboard
    6, 3, 1/1/2004, printer

    I want a query to show what customers' last purchase is.
    i.e. results:
    bob, 1/3/2004, usbkeyboard
    jane, 9/19/2004, keyboard
    john, 1/1/2004, printer

    Does anyone know how to do this?

    /r
    Tags: None
    • ByteMyzer
      #2
      Re: SQL help

      Try the following query:

      SELECT Customers.name, P1.datepurchase , P1.item
      FROM Customers INNER JOIN Purchases AS P1 ON Customers.uid = P1.custID
      WHERE P1.datepurchase = (
      SELECT Max(P2.datepurc hase)
      FROM Purchases AS P2
      WHERE P2.custID=P1.cu stID;
      );

      "R.Chan" <raychan_uoft@y ahoo.com> wrote in message
      news:5460359e.0 407281600.70efe b22@posting.goo gle.com...[color=blue]
      > Hi,
      > I'm stuck on the following query. Any help would be greatly appreciated!
      >
      > table1: Customers
      > uid, name
      > 1, bob
      > 2, jane
      > 3, john
      >
      > table2: Purchases
      > uid, custID, datepurchase, item
      > 1, 1, 1/1/2004, mouse
      > 2, 1, 1/2/2004, keyboard
      > 3, 1, 1/3/2004, usb key
      > 4, 2, 1/1/2004, mouse
      > 5, 2, 6/19/2004, keyboard
      > 6, 3, 1/1/2004, printer
      >
      > I want a query to show what customers' last purchase is.
      > i.e. results:
      > bob, 1/3/2004, usbkeyboard
      > jane, 9/19/2004, keyboard
      > john, 1/1/2004, printer
      >
      > Does anyone know how to do this?
      >
      > /r[/color]


        Comment

        • Sherwood Wang
          #3
          Re: SQL help


          "R.Chan" <raychan_uoft@y ahoo.com> wrote in message
          news:5460359e.0 407281600.70efe b22@posting.goo gle.com...[color=blue]
          > Hi,
          > I'm stuck on the following query. Any help would be greatly appreciated!
          >
          > table1: Customers
          > uid, name
          > 1, bob
          > 2, jane
          > 3, john
          >
          > table2: Purchases
          > uid, custID, datepurchase, item
          > 1, 1, 1/1/2004, mouse
          > 2, 1, 1/2/2004, keyboard
          > 3, 1, 1/3/2004, usb key
          > 4, 2, 1/1/2004, mouse
          > 5, 2, 6/19/2004, keyboard
          > 6, 3, 1/1/2004, printer
          >
          > I want a query to show what customers' last purchase is.
          > i.e. results:
          > bob, 1/3/2004, usbkeyboard
          > jane, 9/19/2004, keyboard
          > john, 1/1/2004, printer
          >
          > Does anyone know how to do this?
          >
          > /r[/color]

          SELECT tblGroup.GroupI D, tblGroup.GroupN ame, tblRegion.RegID ,
          tblRegion.RegNa me, tblLocation.LID , tblLocation.Loc Name,
          tblIncident.Inc dntDate, IIf((Not IsNull([RemAct1]) And IsNull([Done1])),1,0)
          AS Open1, IIf((Not IsNull([RemAct1]) And Not IsNull([Done1])),1,0) AS
          Closed1, IIf((Not IsNull([RemAct2]) And IsNull([Done2])),1,0) AS Open2,
          IIf((Not IsNull([RemAct2]) And Not IsNull([Done2])),1,0) AS Closed2,
          IIf((Not IsNull([RemAct3]) And IsNull([Done3])),1,0) AS Open3, IIf((Not
          IsNull([RemAct3]) And Not IsNull([Done3])),1,0) AS Closed3, IIf((Not
          IsNull([RemAct4]) And IsNull([Done4])),1,0) AS Open4, IIf((Not
          IsNull([RemAct4]) And Not IsNull([Done4])),1,0) AS Closed4, IIf((Not
          IsNull([RemAct5]) And IsNull([Done5])),1,0) AS Open5, IIf((Not
          IsNull([RemAct5]) And Not IsNull([Done5])),1,0) AS Closed5
          FROM ((tblRegion INNER JOIN tblLocation ON tblRegion.RegNa me =
          tblLocation.Reg Name) INNER JOIN (tblIncident INNER JOIN tblFormal ON
          tblIncident.GUI ncdntID = tblFormal.GUInc dntID) ON tblLocation.LID =
          tblIncident.LID ) INNER JOIN tblGroup ON tblRegion.Group ID = tblGroup.GroupI D
          WHERE (((tblFormal.Wh o1EID)=[Forms]![frmRptDesign]![cmbEID])) OR
          (((tblFormal.Wh o2EID)=[Forms]![frmRptDesign]![cmbEID])) OR
          (((tblFormal.Wh o3EID)=[Forms]![frmRptDesign]![cmbEID])) OR
          (((tblFormal.Wh o4EID)=[Forms]![frmRptDesign]![cmbEID])) OR
          (((tblFormal.Wh o5EID)=[Forms]![frmRptDesign]![cmbEID]));

          *Sherwood Wang MVP*


            Comment

            • Larry Linson
              #4
              Re: SQL help

              Things must be real slow in Ennis, MT these days for you to spend all the
              effort to create that silly SQL statement that doesn't apply to the
              question. Don, you aren't fooling anybody -- nobody believes such a
              ridiculous post would be from a Microsoft Access MVP.

              "Sherwood Wang" <shwang@waynes. net> wrote in message
              news:9QYNc.1827 9$m63.14764@ani mal.nntpserver. com...



                Comment

                Previous template Next
                Working...