Need Help to start

[FallenAngel]

I need to analyze supplier data from a third party internet purchasing site.
I am going to dowload the purchase data to access. I have then been tasked
to evaluate supplier compliance. The fields dowloaded are;

supplier name [10k active suppliers, 100 contracted suppliers]
ship to location [ 150 possible ship to locations each have unique ship such
as #CA001]
invoice id
invoice date
sku [sku is not a required field]
line detail
line total
gl code [50 gl codes]

The problem is the information I need to detail can be from multiples
selections.

Contracted Suppliers [ 100 +/-]
* each supplier is contracted by ship to location. (so I could have an
office supplier for all 150 locations or just 15 or a region ie. NorthEast,
Virgina, Southern California, or by a city)
* each supplier could have one or multiple gl codes.

Contracted Supplier/Location/Gl Code

Then I need to determine which of the 10K suppliers, not all reporting
monthly, bought in one of the contracted suppliers location with/without
indicated gl code for the contract type such as office supplies.

My overall goal is to provide supplier compliance reports and exception
reports for those vendors that should not be used. Would it be best to just
run queries.

I've been scratching my head on the best way to set this up. No luck. If you
could offer a suggestion or two that would be great.


THANKS

 

[Bruce Meneghin]

You'll need some base tables that contain information about "the way things
are supposed to be". From your description, it sounds like you need a
Suppliers/Locations table where the supplier has one record for each place
they are to ship to
Suppliers/Gl code table with similar setup - one record for each
supplier-valid Gl code pair.

The purchase data can be joined to these tables and non-compliance
identified where a lookup query yields NULL.