Hi, we have an issue which we think Access can help dig us out of …. 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
Many thanks for reading all the way to here, hope to speak soon!