Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Location
    MN
    Posts
    2

    Unanswered: Query Help: Sort/count multiple categories

    Hello all.. Hopefully I can get some direction from the folks of dBForums.

    I have a single table in MS Access 2003 with the following structure:
    Table: HIST
    Columns: TranDate(Number), ItemNbr(Text), LastLoc(Text), NewLoc(Text), TranID(Number)

    The way the system works is: When an item (identified by ItemNbr) is moved from one location (LastLoc) to a new location (NewLoc), a 'transaction' is created and the date (TranDate), ItemNbr, LastLoc, and NewLoc are entered into the database as a new row (uniquely identified by TranID). Multiple transactions may be tied to a single ItemNbr within a day - think "Items in a warehouse being distributed throughout the factory. Each time an item is pulled from the warehouse, a transaction is created with its current warehouse location and its new location in the factory. A single type of item (ItemNbr) may be pulled from the warehouse multiple times in a day."

    What I am trying to do is figure out how many times an item is pulled from the warehouse in a day. I can get the total number of transactions in a day (SELECT DISTINCT TranDate, Count(*) FROM HIST WHERE LastLoc = xxx AND NewLoc = yyy GROUP BY TranDate), and I can get the total number of transactions for a particular item, but I can't figure out how to get total number of transactions for a particular item within a day.

    Something like:
    TranDate(Jun1)
    ItemNbr(0001): Number of Trans that day
    ItemNbr(0002): Number of Trans that day
    TranDate(Jun2)
    ItemNbr(0001): Number of Trans that day
    ItemNbr(0002): Number of Trans that day

    Not necessarily in that format, but that's the type of info I'm trying to get.

    Any ideas!?

  2. #2
    Join Date
    Jun 2009
    Location
    MN
    Posts
    2
    Nevermind. Figured it out:

    SELECT TranDate, ItemNbr, Count(*) as Num
    FROM HIST
    GROUP BY TranDate, ItemNbr;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •