MS Access Query Help!!

[CJones]

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!?

 

[JeffDG]

In SQL Server (sorry, I don't really do Access unless someone holds a gun to my head), I would do:

SELECT TranDate, Count(*)

FROM HIST
WHERE LastLoc = xxx AND NewLoc = yyy
GROUP BY TranDate WITH ROLLUP

 

[EdFred]

Use Between xxxxx AND yyyyy for your trandate instead of groupBy

Or shoot me the table in an e-mail, and I can mess with it.

 

[CJones]

In SQL Server (sorry, I don't really do Access unless someone holds a gun to my head), I would do:

SELECT TranDate, Count(*)

FROM HIST
WHERE LastLoc = xxx AND NewLoc = yyy
GROUP BY TranDate WITH ROLLUP

Thanks for the try. Unfortunately, Access doesn't play well with 'real' SQL languages - ROLLUP is not supported.

 

[CJones]

Use Between xxxxx AND yyyyy for your trandate instead of groupBy

Or shoot me the table in an e-mail, and I can mess with it.

But I'm trying to find number of transactions per item per day for ALL days, not just one particular day.

 

[EdFred]

SELECT HIST.ItemNbr, Count(HIST.ItemNbr) AS CountOfItemNbr
FROM HIST
WHERE (((HIST.TranDate) Between #6/1/2009# And #6/4/2009#))
GROUP BY HIST.ItemNbr;


Wait....

What are you using for a date? a number or a date?


And to reclarify:

You want results to show something like:

ItemA Jun1 6
ItemB Jun1 12
ItemC Jun1 15
ItemA Jun2 7
ItemB Jun2 5
ItemC Jun2 23

Yex?

 

[JeffDG]

Thanks for the try. Unfortunately, Access doesn't play well with 'real' SQL languages - ROLLUP is not supported.

Can you do a UNION query then?

SELECT TranDate, Count(*)

FROM HIST
WHERE LastLoc = xxx AND NewLoc = yyy
GROUP BY TranDate

UNION

SELECT TranDate = 'ALL', Count(*)

FROM HIST
WHERE LastLoc = xxx AND NewLoc = yyy

 

[TangoWhiskey]

I tested this in Access 2007, Chris. What you want is this:

SELECT HIST.TranDate, HIST.ItemNbr, Count(*) AS NumTransThatDay
FROM HIST
GROUP BY HIST.TranDate, HIST.ItemNbr;

Given this input data:

You get these results:

 

[EdFred]

SELECT HIST.ItemNbr, HIST.TranDate, Count(HIST.ItemNbr) AS CountOfItemNbr
FROM HIST
GROUP BY HIST.ItemNbr, HIST.TranDate;


Looks like TW and I agree

 

[CJones]

SELECT HIST.ItemNbr, Count(HIST.ItemNbr) AS CountOfItemNbr
FROM HIST
WHERE (((HIST.TranDate) Between #6/1/2009# And #6/4/2009#))
GROUP BY HIST.ItemNbr;


Wait....

What are you using for a date? a number or a date?


And to reclarify:

You want results to show something like:

ItemA Jun1 6
ItemB Jun1 12
ItemC Jun1 15
ItemA Jun2 7
ItemB Jun2 5
ItemC Jun2 23

Yex?

You'll laugh when you hear how they have the date formatted: 1090601, 1090602, ... It is a number, but they threw a '1' in front to signify that it is post Y2K. Isn't a big deal until you export to Excel, then you have play with string formatting there to have it look presentable....

ANYWHO.. What I am looking for is things broken down into a couple of categories - 1st by date, then by item number:

Jun1: ItemA: Num Trans. that day for ItemA
Jun1: ItemB: Num Trans. that day for ItemB
Jun2: ItemA: Num Trans. that day for ItemA
Jun2: ItemB: Num Trans. that day for ItemB

Actually.. looking closer at what you had for output, you are heading down the correct path.

 

[schmookeeg]

I think expanding the group is the way to go, as I believe has been mentioned... unless I'm missing something subtle?

SELECT Count(itemnbr), itemnbr, date
FROM hist
GROUP BY date, itemnbr;

 

[CJones]

I tested this in Access 2007, Chris. What you want is this:

SELECT HIST.TranDate, HIST.ItemNbr, Count(*) AS NumTransThatDay
FROM HIST
GROUP BY HIST.TranDate, HIST.ItemNbr;

Given this input data:

You get these results:

THAT is flippin awesome!! Works like a charm. I was sooooo close, yet so far away.

Thanks a ton, guys! I posted this on dBForums.com and haven't even had anyone read it yet. PoA rocks!

Oh and BTW, would you believe I took a 400-level Database class this past semester and couldn't figure this out on my own!? Yet another reason the Dean of the College will be getting a very lengthy disgruntled letter after I graduate. What a joke of a MIS program.

 

[EdFred]

You'll laugh when you hear how they have the date formatted: 1090601, 1090602, ... It is a number, but they threw a '1' in front to signify that it is post Y2K. Isn't a big deal until you export to Excel, then you have play with string formatting there to have it look presentable....
Actually.. looking closer at what you had for output, you are heading down the correct path.

I have a customer that imports dates like that to me in a spreadsheet for scheduled releases, I have to do this with code to get it to work:

For i = 1 To rs.RecordCount
    rs.Edit
    strDate = Right(rs![PO Due Date], 4) & Left(rs![PO Due Date], 4)
    strDate = Left(strDate, 2) & "/" & Right(strDate, 6)
    strDate = Left(strDate, 5) & "/" & Right(strDate, 4)
    rs![PO Due Date] = strDate
    rs.Update
    rs.MoveNext
 
Next i

 

[CJones]

I have a customer that imports dates like that to me in a spreadsheet for scheduled releases, I have to do this with code to get it to work:

For i = 1 To rs.RecordCount
    rs.Edit
    strDate = Right(rs![PO Due Date], 4) & Left(rs![PO Due Date], 4)
    strDate = Left(strDate, 2) & "/" & Right(strDate, 6)
    strDate = Left(strDate, 5) & "/" & Right(strDate, 4)
    rs![PO Due Date] = strDate
    rs.Update
    rs.MoveNext
 
Next i

Wow.. Mine isn't that bad. Mine is just:

=DATE(CONCATENATE("20",MID({weird formatted date cell},2,2)), MID({weird formatted date cell},4,2), RIGHT({weird formatted date cell}, 2))

Then I can format the cell to show the date what I want it to be.

 

[flyingcheesehead]

Yet another reason the Dean of the College will be getting a very lengthy disgruntled letter after I graduate. What a joke of a MIS program.

IMHO, most if not all MIS programs are a joke. Jesse will run circles around anyone with a PhD in MIS (if there is even such a thing - I don't remember ever hearing of anyone with even a Master's in MIS?)

 

[tonycondon]

does PoA get a chunk of your paycheck? or at least an honorary degree?