Help organising customer reports arrghhhhh!!!

[Hanna]

Hi, we have an issue which we think Access can help dig us out of :banghead: …. arrgghhhhh!!! Sorry, had to get that off my chest
Effectively, we sell washers & we have to run through a dozen or so monthly reports (that our customers send us) to work out a) forecasts for the production department & b) a report for the sales team identifying which in-house part numbers are being used for our parts.
The customer reports are up to 140K lines each & are structured like this:
Customer1;Field 1;Field 2;Field 3; Item Notes; Sales
The trouble is that the customers...
- enter our order codes into any one of up to the 3 different fields (or even in the items notes field).
- sometimes add their own suffixes or prefixes onto our order codes
- use the other fields for their own in-house reference numbers
- give the different fields different names
We have had suggested to us:
-getting the customers to clean up the data before sending (tried, they aren't interested!!!)
-cleaning the data into separate columns ourselves (tried establishing various rules, filters, lookups etc, but every month a customer does something that we don't expect & our rules etc just can't keep up with them!)
-appending all of the reports into one table, but that is soon going to be a huge table with a lot of duplication going on (even messier than we are at the moment!!!)
Our goal is to provide:
A) a single report which lists our part numbers & the total of what has been sold across all the customers
B) a single report showing all of the different possible in-house part numbers given for our parts.
What we have in mind is:
Creating 12 tables in Access, named Customer 1 through to Customer 12 which hold the sales figures from the different customers. Done this via linked tables, tick, phew!
Creating a table of our own order codes. Phew, Tick!
Creating a query & adding the 13 tables into it. So far so good, Tick!
Establishing how best to get Access to include sales figures for any field which contains (as opposed to equals) our order code - totally lost!
Establishing a report which shows (without any duplication) which different in-house part numbers have been used for our order code (regardless of customer!)
At the moment we're using all sorts of pivot tables & lookups in excel ... and it's getting messy. We could be picking up 3 new customers next month & our current "system" is sure to break nooooooooooooooooooooo!!!!
Could you help out?
Or maybe point us in which direction we should be looking eg we;ve been researching union queries as a way of coping with this?
Many thanks for reading all the way to here, hope to speak soon!

 

[Hanna]

oops I meant to add in an example of our data!
(and say we're using Access 2003)
Manty thanks (again!!!)

 

[plog]

Your issue isn't really a database issue. There's really no reason talking about how to structure your database to accomodate the mess of source data you get, because no matter what structure you choose its not going to really help.

The trouble is that the customers...
- enter our order codes into any one of up to the 3 different fields (or even in the items notes field).
- sometimes add their own suffixes or prefixes onto our order codes
- use the other fields for their own in-house reference numbers
- give the different fields different names

You are right, those things are the real issue and table structure and queries aren't going to help much. Your problems start even before you think about putting data into a database.

You've laid out your options: have your customers to give you good data or clean it up yourselves. My guess is the first is way less likely to happen, however one way to do this is to provide them with tools to give you the data like you want: either provide them with template spreadsheets to fill out and return to you or provide an online interface that allows them to easily input data into a database for you.

Again, I understand how business works, so most likely the second option of you guys cleaning up data will be the solution. In that instance I would structure the main database properly without regard for how I get the source data. Then, you just have to suck it up and make each source file fit into the proper structure.

 

[Hanna]

Thanks for coming back - you're spot on about how our customers feel about this: we'll leave that up to you to sort!!! We did suggest giving them template spreadsheets....and these were politely (???) declined!!!!

Never mind, all's fair in love & war and trying to organise chaotic data it seems!!!!

... Just to elaborate on what you said about:
"structure the main database properly without regard for how I get the source data"
We've tried looking into joining the fields together in the most likely fields that they are to include a part number.
This is looking pretty messy, but may be worth investgating??!