Combining (Monthly) Tables with Identical Column Names

[jhowland]

Hi All,



First post, so please move mod if in the wrong spot.

I'm trying to get my head around access databases that one of our suppliers sends to us on a monthly basis with statistics about our business.

Every month we can download an MDB with information about particular users (SubName - primary key) (SubNames do come and go, but not frequently) as well as the volume of each product they sold, and the overall value of sales of that product. They also include a 'period' field which shows the month the data is for, and have an overall total volume and total value field. This is shown below:

Period	SubName	Product1Volume	Product1TotalValue	Product2Volume	Product2TotalValue	Product3Volume	Product3TotalValue	Total Volume	Total Value
201303	12235	100	100	100	100	100	100	300	300
201303	18426	155	150	155	150	155	150	465	450
201303	14647	55	500	55	500	55	500	165	1500
201303	12348	54864	25	54864	25	54864	25	164592	75
201303	12349	584	60	584	60	584	60	1752	180
201303	98765	1542	500	1542	500	1542	500	4626	1500
201303	98764	8451	500	8451	500	8451	500	25353	1500
201303	98763	5548	500	5548	500	5548	500	16644	1500
201303	98762	100	500	100	500	100	500	300	1500
201303	98761	209	448	209	448	209	448	627	1344
201303	56789	115	843	115	843	115	843	345	2529
201303	56780	15845	10	15845	10	15845	10	47535	30
201303	56781	51	854	51	854	51	854	153	2562

So I have an MDB for each month with this table (and a few others that we don't use)

So basically, for each and every month, the SubName will be repeated but with a different Period. (so Period 201303 will have a SubName 12235, and table 201302 will have a SubName 12235, but period 201202 may not have a SubName 12235 because it wasn't used until period 201208)

In addition, they also provide a separate table which ties SubName with some user-specific information. Importantly, SubName is not actually the user name, but is a generated ID of the user (and completely meaningless to us until tied with the CompanyName). We also have some other fields about them, so for example:

CompanyName	SubName	ExtraCategory
ABC	12345	1
ABC	12346	1
ABC	12347	1
ABC	12348	1
ABC	12349	2
DEF	98765	1
DEF	98764	1
DEF	98763	1
DEF	98762	1
DEF	98761	1
GHI	56789	2
GHI	56780	2
GHI	56781	1

What I would then do is to filter by CompanyName in a query to show what purchasing habits of company ABC were for a particular month, and I also want ExtraCategory1 to be shown. I can do this quite easily for a single month through a query.

However, I would like to be able to run a filter for CompanyName so that it runs against all the different tables to give the results in a single query so that I can do a trend (as opposed to having to run it against each month individually).

The final spanner in the works is that I need to be able to easily modify any build so that each month I can add in the next period (and hopefully, remove the trailing period to give a 24 month view)

Any suggestions about how I would even begin to solve this problem? It is so totally outside my experience that I just have absolutely no idea . Thanks!

[orange]

Your post sends up a number of flags.

Your supplier sends your company the data??? Does your company ask for it? Does your company know what it is? What it means? Where it fits in their business processes? Based on your comments it seems you have some idea of what you might do with it, but unfortunately

It is so totally outside my experience that I just have absolutely no idea

and you may be the only one interested in it.

There is no mention that management has suggested, or management needs.... so it's hard to put the underlying business need/opportunity into context.

Your mdb file appears to be some sort of summary report for some time period. It also appears to me that there is significant intelligence built into the Periods and SubName identifiers.

My suggestion to you, is to document who needs what you seem intent on doing. Get some support or direction that you (or somebody) should even proceed.

You will need to know WHAT this data (mdb) represents. Where it fits in terms of your business rules/processes.
If a Supplier sends this data, what do other Suppliers do?

You may want to start by describing to us in plain English WHAT your company is about, and to the best of your knowledge WHAT this data represents, and WHY it would be good for you and your company for you to proceed.

If you are doing some sort of market research, customer preferences, sales by product volumes by time periods etc, then maybe Access isn't the proper tool, but maybe if we knew more, it is.

[jhowland]

OK, I'll try to be as specific as possible.

We provide back office services for a large number of contractors, which includes organising credit card terminals for when they are billing customers.

When contractors bill a customer, our terminals are now programmed to include item codes for them to select (so they don't/can't key in random amounts). They have realised that our hardware provider gives us this information, and now want it so they know what services they provide most often (hence the need for this to be as automated as possible - we can't have 100 requests each month that require 100 manual lookups).

There are also other obvious things we can do with the data - such as mapping which are the most productive/unproductive contractors, as well as what services are most popular during different seasons (e.g. to offer discounts etc)

Our provider gives us the unit ID of the terminal, and the quantity (and total value) for each item code that was entered. A separate table provides a list mapping each unit ID to a particular contractor. Each month is a new MDB with this information. If a contractor joins/leaves, they get given a new unit ID.

Does this help?

[June7]

If I understand, Period and Subname together constitute a unique identifier.

Seems merging records to a single dataset should not be an issue. This can be done by appending records to table. Probably also want to populate a field that identifies the source of the appended records.