Access Problem

[Toll Booth Willie]

Need to write a query in MS Access to accomplish the following:

As many of you know, I am an automation engineer at a Dairy distribution warehouse. We have two main tables, a completions table with past orders that have been picked, and an orders table with orders that are currently being processed. To gauge system efficiency, we need to be able to look at previous days' orders for order lines where a single case was picked, vs 2 cases picked, vs 3 cases picked (more cases picked simultaneously = higher efficiency) and so on. If I pick more than one case at once, all the cases will have the same orders_id. I need a query that shows me, for a given route, how many picks were single picks, how many were double picks and so forth. Table looks like:

ORDERS_ID ROUTE STOP SKU QTY
868544337 233 1 200111 1
868544337 233 1 200111 1
868544337 233 1 200111 1
868544338 233 1 201259 1
868544338 233 1 201259 1
868544339 233 2 200003 1

So the desired output of the query would tell me for route 233 I had 1 instance of a 3 case pick (for SKU 200111), one instance of a 2 case pick (for 201259) and 1 instance of a single case pick (for 200003). Basically I want to count the number of instances of each unique orders_id (I see 868544337 3 times, 868544338 2 times and so on). Hope I'm not confusing the crap out of everyone. Any suggestions?

 

[Franchise]

:huh:

 

[The Brain]

Originally posted by Toll Booth Willie
Need to write a query in MS Access to accomplish the following:

As many of you know, I am an automation engineer at a Dairy distribution warehouse. We have two main tables, a completions table with past orders that have been picked, and an orders table with orders that are currently being processed. To gauge system efficiency, we need to be able to look at previous days' orders for order lines where a single case was picked, vs 2 cases picked, vs 3 cases picked (more cases picked simultaneously = higher efficiency) and so on. If I pick more than one case at once, all the cases will have the same orders_id. I need a query that shows me, for a given route, how many picks were single picks, how many were double picks and so forth. Table looks like:

ORDERS_ID ROUTE STOP SKU QTY
868544337 233 1 200111 1
868544337 233 1 200111 1
868544337 233 1 200111 1
868544338 233 1 201259 1
868544338 233 1 201259 1
868544339 233 2 200003 1

So the desired output of the query would tell me for route 233 I had 1 instance of a 3 case pick (for SKU 200111), one instance of a 2 case pick (for 201259) and 1 instance of a single case pick (for 200003). Basically I want to count the number of instances of each unique orders_id (I see 868544337 3 times, 868544338 2 times and so on). Hope I'm not confusing the crap out of everyone. Any suggestions?

I'll be honest with ya... if I could understand the other stuff you're talking about I could probably help ya with the access, but at this point the other stuff confused the shit outta me... sorry

 

[Toll Booth Willie]

Re: Re: Access Problem

Originally posted by The Brain
I'll be honest with ya... if I could understand the other stuff you're talking about I could probably help ya with the access, but at this point the other stuff confused the shit outta me... sorry

Kind of thought that might happen. Heres the meat and taters: Need a query to count the number of instances of each unique orders_id. I can figure the rest out I think.

 

[Toll Booth Willie]

Originally posted by CharlotteDude
I understand what you are trying to do, but I'm not well versed on Access. I think you can do what you want in Excel using the Data / Subtotals feature if you want to try that route.

Excel doesn't work as well for this kind of thing, due to the fact my data is based off of tables that are constantly updated via SQL. I could do what I want in SQL easily enough, but the database is huge and complex as is. Don't want to add to it.

 

[mathmajors]

No way I'm doing your job for you. Freeloader.

 

[Franchise]

dork

 

[Toll Booth Willie]

Originally posted by mathmajors
No way I'm doing your job for you. Freeloader.

I see my quandary is too much for you...Dats ok.

 

[mathmajors]

Originally posted by Toll Booth Willie


I see my quandary is too much for you...Dats ok.

Thanks for letting me off the hook.:xyzthumbs

 

[Bootay]

Excel using the Data Analysis tie-ins for SQL is the way to go. It can easily do dynamic queries against SQL to pull the data, which should be fast if you're just looking for yesterdays and todays orders...