Introduction & Question

[Nate74]

I recently took a Marketing Manager position at a local aerospace company here in Southern CA. While I was working on my MBA, my various study groups used Access alot for marketing research projects and now I'm trying my hand at similar uses.

I've made some simple programs that the inside sales group I supervise is now using, and I realize much of those apps wouldn't have been possible had I not found this board to "borrow" ideas from. So I owe you all a thanks for that.

That being said, I am in no way what so ever a programmer. Nor will my company let me hire a dedicated programmer just for my Marketing Dept. I have to wait in line like everybody else for IT resources... I struggled through a C++ and a Fortran 77 class about a million years ago in engineering school, but that's about it.

I guess this is all just a long winded warning that I will have next to nothing to contribute to your online community and rest assured my questions will be simple and quite possible show my total ignorance.

OK, let's get the first dumb question out of the way:

My goal is to look at our manufacturing backlog and put together data on which customers are ordering what. The goal will be to find out which 20% of our parts account for 80% of our business (80/20 rule, etc.)

In the table I extracted from our MRP system, the fields I care about are:

Customer Name, Part Number Ordered, Order Quantity

The problem is there are multiple lines for the same part number from each customer, representing different orders for different quantities.

If I'm thinking about this correctly, I think I need to somehow make a query that will output something like this:

Customer Name, Unique Part Number, Total of that Part on Order.

3 Questions:
Is this possible?
How do I do it?
Am I woefully out of my depths trying to learn enough about Access to survive?

 

[pbaldy]

Try this query, off the top of my head:

SELECT [Customer Name], [Part Number Ordered], Sum([Order Quantity])
FROM TableName
GROUP BY [Customer Name], [Part Number Ordered]

BTW, I'd recommend against the spaces in your names. They're more trouble than they're worth in the long run.

 

[Moniker]

PBaldy nailed it and even wrote the query (and even warned about field names with spaces -- Use Customer_Name for Customer Name, and so on).

For a quick lesson in what he's describing, it's an aggregate query (use the Sigma in the Query Design view to turn these on). In there is a "Total" row for each field. The Customer Name and Part Ordered have "Group By" in their Total row, and the Order Quantity has a Sum in the Total row. The result is exactly what you described as wanting.

 

[Nate74]

Wow, I was in a meeting for no more than 20 minutes and my questino is already answered. Thank you guys so much. I just ran it on last week's data and all seem perfect!

I'll be getting this weeks data shortly but man am I gonna look like a Hero. Thanks to you guys that is!!!

 

[Nate74]

He He He. I'm jammin' now. I was able to use a similar query to figure out where 80% of our dollar value in bookings comes from (about 22% of our product line). They think I'm a wiz... uh oh...

Seriously though, thanks to you all! If anybody ever comes to LA, I owe you a beer

 

[pbaldy]

Be careful what you offer. I've got family in LA, and I go there every now and then.

 

[Moniker]

I used to go for E3 until it got changed last year (guess what industry I might be in on the side), but you're safe from me unless you can email me that beer.

 

[Ron_dK]

....E3......guess what industry I might be in on the side...

I didn't know they're building trains (E3) in Dallas ?