Design Strategy - Using interchange numbers to show prices for primary key numbers

[krystalr]

Hi there,

I'm new to access so bear with me, I appreciate any help I can get!



I've been tasked with creating a database that would be able to combine all of the price sheets that my company receives from vendors into one database where we could see a list of the part numbers we use, along with the prices that each vendor provides for that product (if any - not all vendors will sell us each product.)

Each product has a part number that we use that for these purposes I'll call its "Product Key." Sometimes when vendors give us price sheets they list the parts along with their "Product Key" number, but sometimes they just use their own sales number or the original manufacturer's part number. This is fine, as we have an interchange spreadsheet that lists all of the manufacturer sales and part numbers along with their appropriate "Product Key" number. Each sales number is linked only to one product key number, but one product key may be linked to multiple sales/manufacturer numbers.

I have the database set up with all of the information on different sheets, with a separate sheet just listing our full inventory of items listed by "produt Key," with this being the primary key and the product keys on all of the interchange/sales sheets being the foreign key. I guess I'm wondering, broadly, what relationships and queries I should have set up so that I can create a sheet with all of our inventory, listed by product key, along with any price that a vendor might provide us with for that part, even if they don't give us the product key right on the sheet and we have to use our interchange data. Again, sorry, like I said, I'm brand new to database design, I'm actually a graphic designer who has been asked by my employer to figure this out and have so far just been Google-ing Access tutorials, so I'd be grateful for any assistance.

Krystal

[June7]

Need more info about data structure. Not sure what you mean by 'information on different sheets'.

Are you importing the vendor price sheets into Access table?

Provide the table structures by diagram or listing in post or attaching db.

[krystalr]

Yes, sorry, when I said sheets I meant tables. So, for example, the tables I have consist of data that was imported from excel spreadsheets and the relevant data in them would be...

Table Inventory_List (roughly 13,000 records, this is every product that we offer coded with our part number. Each number is unique, so I've got this as the primary key for everything else)
OUR_PART#

Table: Interchange (roughly 60,000 records - this contains every manufacturer or sales part# that we have interchange data for to switch to our part number)
OUR_PART#
MANUFACTURER_OR_SALES#


Table: Manufacturer1_PriceList (provided to us by Manufacturer1)
Manufacturer1_Part#
Part_description

Table: Manufacturer2_Pricelist
Manufacturer2_Part#
Our_Part# (some manufacturers are actually already aware of the numbering system we use and will provide us with that number as well as their own)
Price
Description

Table: Manufacture3_pricelist
etc. etc.

We will have a whole bunch of manufacturers providing us with lists like these, and what I'd like to do is have a database set up that allows us to easily set up a datasheet that includes records of our entire inventory (listed by our part#) along with fields for each vendor/manufacturer and what their price (if any was provided) for this part# would be. Sometimes the price sheets they give us already have our part# on it, and sometimes we would have to use the interchange table to convert them. Does that make sense?

[June7]

I suggest one Manufacturer_PriceList table instead of multiple tables. Import the price sheets into a single table with another field for ManufacturerID.

This would mean multiple records for product keys.

If you keep separate price table for each manufacturer, combing them could be by 2 query methods:

1. Join each price table to the master Interchange table which should result in one record for each product key.

2. UNION the manufacturer price tables to emulate the all-in-one dataset I described earlier.

[krystalr]

Not all of the pricesheets provided by manufacturers contain the product key though. Is there a way to automate this process (converting a manufacturerID to our part#) using the interchange table?

[krystalr]

Additionally, the manufacturers structure their pricesheets differently from one another.

[June7]

Differences between manufacturer price sheet structures can be dealt with as long as each is consistent in what they send you.

Anything can be automated with enough code. Just have to learn how to code.

[krystalr]

Would Macros work for it?

[June7]

Macros can run action query objects and run ImportExport commands. So maybe. I don't use macros, only VBA.