CJones
Final Approach
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)
Any ideas!?
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!?