A hint in the right direction... please :)

[howardgrigg]

We have a small business selling computer products and now we have a
website to sell the products on. We have 3 main suppliers each with a
few thousand products and currently I am using a macro (many infact)
in excel to combine the current data files we get from each supplier
into one file that gets uploaded to the website for import. However we
would like to move the whole thing over to access to hopefully speed
up the daily process of creating the file for upload. I get similar
data from each supplier although obviously the columns arn't in the
same order etc and one supplier has info that the others do not.

But what I was wondering was what is the best way to set something up
so that it pulls all the products in from each supplier and rearranges
the data to fit what is wanted.

I haven't used access before but I have time to put into this to try
and get it to work well.

 

[Allen Browne]

Obviously we can't talk you through the entire process, and it will take you
some time to achieve this as you learn Access as well as build this
application.

The crucial concept is the one-to-many relation:
One supplier can supply many products.
One product can come from multiple supppliers.
You therefore have a many-to-many relation between products and suppliers.
So, you need 3 tables to resolve that into a pair of one-to-many relations.

The structure will be something like this:
Product table, with fields:
ProductID AutoNumber primary key
ProductName Text
PriceEachEx Currency How much you sell it for

Supplier table, with fields:
SupplierID AutoNumber, primary key
SupplierName Text
...

ProductSupplier table, with fields:
ProductID Number. Relates to Product.ProductID
SupplierID Number. Relates to Supplier.SupplierID
SupplierCode Text. Supplier's order code
PriceEachEx Currency How much supplier charges you.

There will be other fields as well, but that's the idea.

To interface it, you will have a main form bound the the Supplier table,
with a subform bound to ProductSupplier. The subform shows the products
sourced from the supplier in the main form.

You can also create a form bound to the Product table, with a subform bound
to ProductSupplier so it shows the suppliers of that product.

Ultimately, you want to export the Product table to your website. You can
use TransferText to export in HTML format. Or, if that doesn't look the way
you want it, you can code your own export like this:
http://allenbrowne.com/AppOutputHtml.html

Hope that's enough to get you started. Be patient with yourself: Access is a
completely different kind of beast, so it will take a while to become
familiar with it.