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

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.

0
howardgrigg
11/11/2007 11:52:55 PM
access 16762 articles. 3 followers. Follow

1 Replies
543 Views

Similar Articles

[PageSpeed] 15

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.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"howardgrigg" <howardgrigg@gmail.com> wrote in message
news:1194825175.177797.105650@z24g2000prh.googlegroups.com...
> 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.
> 

0
Allen
11/12/2007 12:25:47 AM
Reply:

Similar Artilces:

Excel formula help Please
I am trying to create a checklist. As an example, A2:A21 will be marked with "N" if conditions are met. B2:B21 will also be marked this way. I want to count the number of times that both A1 and B1(and so on) are marked with "N". Can anyone help me please?? --- Message posted from http://www.ExcelForum.com/ > I am trying to create a checklist. As an example, A2:A21 will be > marked with "N" if conditions are met. B2:B21 will also be marked > this way. > I want to count the number of times that both A1 and B1(and so on) are > marked with &...

Help with spam please
We are receiving massive amounts of spam and it is only getting worse. We use an outlook spam filter but I would like to be able to use a blacklist or something to block all the spam companies out there. We also recieve hardcore porn adds constantly and I know the employee's are upset about it. Can someone point me in the right direction here? I dont have a lot of experience with exchange2000 but if someone could give me a url or make any helpful suggestions on how to stop this I would very much appreciate it thank you. depending on your hardware, etc you can do this on the sam...

Help me please with MS Pocket PCS and Outlook
Into Outlook 2003 I sync between my work computer and my home computer using and HP iPaq w/Pocket PC 2003. I have the same Outlook version on both computers. I have the MS Pocket PCS addin software on my work computer and thought I had it on my home computer (I did). Today I changed around some contact folders at work, my PPC sync(ed) and I went home. I got home and tried to sync with my home computer and to my supprise all (more than 300) of my work contacts are gone! I looked for the PCS app but could not find it. So I downloaded, When I attempted to install it it wanted to uninstall i...

Please help to recover missing file #2
I am faced with a strange problem. I was working with worksheet in Excel when there was a power shutdown. The worksheet was in saved condition. When power resumed my worksheet simply vanished. Now whenever I try to access the worksheet I get the message that worksheet could not be found. As my pc is window xp pro. the worksheet should have saved itself as it does in word program. I have cheeked Recycle Bin and ran search program without any success. System Restore is of no help as it was off. Is there any way to retrieve the file. TIA. ...

left/right or center ?
I have days off in the P column in this format: Sat/Sun I'm using them in another column in this format SatSun, so I'm using this formula: =LEFT(P21,3)&RIGHT(P21,3), which produces SatSun However, I have some days off that just have Sun, which produces SunSun. How can the formula be writen to produce SatSun when two days are showing, and only Sun when one day is showing ? Thanks, Steve Use Substitute, as in: =Substitute(P21,"/","") Regards, Fred "Steve" <Steve@discussions.microsoft.com> wrote in message news:D1729A01-06...

VBA Worksheet Event Change or Selection Question, please assist me
Two fantastic people were kind enough to provide me these two codes below, and now I'm trying to see if I can expand it to include other columns and data in a worksheet. Code 1: Private Sub Worksheet_Change(ByVal Target As Range) Dim myC As Range If Intersect(Target, Range("P:P")) Is Nothing Then Exit Sub Application.EnableEvents =3D False For Each myC In Intersect(Target, Range("P:P")) Range("Q" & myC.Row).Value =3D Date - Target.Value Next myC Application.EnableEvents =3D True End Sub Code 2: Private Sub Worksheet_Change(ByVal Tar...

Send direct e-mail to customers
When I try to send direct e-mail (by clicking on the send direct email on the actions bar) I can get as far as choosing my template, but when I click send i get the following error message: "The Bulk E-Mail Service is not running" How do I start this service? Hi James, Check the services. Right-click on My computer -> manage -> Services and Applications -> Services. Search for Mcirosoft CRM Bulk email service -> start. Hope this helps! Sam _______________ Inogic Innovative Logic Innovative solutions for your SME ERP/CRM products E-mail: crm@inogic.com Web: www....

Please help! Designing database
I would like to be able to pull one word from the drop down box in a form or report and it pull up that person's information. Is this possible? Not sure how this is a "table design" question ... Not sure what you mean by "pull one word from the drop down box"... Not sure from where you are expecting to "pull up that person's information"... Not sure what "information" you're expecting... Can't offer ideas on "how" unless you provide a bit more specific description of "what". -- Jeff Boyce &...

crystal report logon error - please check you have enough licenses
We are evaluating MS CRM and have installed CRM1.2 on Win 2K3 with AD, SQL Server, Exchange, IIS running on the same box. Everything seems fine except that when I try to access the CRM reports, I get an error "crystal report logon error.please check you have enough licenses". I've researched a good number of posting on the net before writing this but it seems that nothing is able to get reports to work. I would appreciate any suggestions or refferences. - Igor There are lots of causes of this error, and most of them dont pertain to licenses. Usually it means that the Crystal...

No right click action in runtime
Hi, I have developed an application distributed as runtime where users can update value list in combo boxes. This is normally done by right clicking and choosing 'edit'. The problem is that right-click menus seem to be disabled in runtime. Any fix for that? Any suggestion? thanks Marc In the runtime, you need to create your own right click menus using macros. Check help on how to do that. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Sacapuce" <Sacapuce@discussions.microsoft.com> wrote in message news:6F355CFC-F8B2-4D6C-B19F-...

illegal name? [noobie, please be gentle]
re: xl2k I am attempting to name a column as "fy2007" xl refuses to accept this. Why? Note that "fy2007" appears to satisfy all the naming guidelines, as listed in the help file. Also note, "fy_2007" is acceptable. cheers, jw FY2007 looks like a cell in column FY in row 2007. You could name it FY_2007 or _FY2007 Or most anything that doesn't look like an address. mr_unreliable wrote: > > re: xl2k > > I am attempting to name a column as "fy2007" > > xl refuses to accept this. Why? > > Note that "fy2007"...

Lost workgroup rights
How do I handle the lost workgroup rights. When data was copied from one folder to another, not everything got copied and the folder has since then been deleted. When reports on one of the access .mdb needs to be modifed we have problems such as not able to link tables without that workgroup. ...

How to access I/O port directly in VC6.0?
Hello! I'm new to VC,and I think this is a very simple qustion to MVPs. And there must be a FAQ on it. Can you please tell me or help me? ----------------------------------- An "An" <bzmcayk@163.com> wrote in message news:%23vx7b8kKIHA.5116@TK2MSFTNGP03.phx.gbl... > Hello! > I'm new to VC,and I think this is a very > simple qustion to MVPs. And there must be > a FAQ on it. Can you please tell me or help > me? > ----------------------------------- Search for "WinIO", it's a library that allows you to do this. -- David Walter E...

Function help requested please
Hello to all I have worked with functions in other programs, but I am totally new to Excel. Here's my situation - I have a table like the following that extends for 10 rows. A B C 1 0 50 700 2 50 100 1600 3 100 150 2500 4 150 200 3400 etc... F G 20 174 =??? I want a function in cell G20 that takes the value in F20 (in this example, 174), then determines what range that number falls in in columns A & B (in this ...

Can't Change Text direction in office 2007
Hi, i have installed office 2007 ( Pro Plus Edition) on a machine. i have faced a strange thing that i have never seen. the option of changing text direction from right to left or left to right does not exist. it come by default with ltr direction. What's the Problem ? Hi, You need to enable a right-to-left language, such as Hebrew or Arabic. To do this, perform the following steps: 1. Close Word. 2. Click Start, click All Programs, click Microsoft Office, click Microsoft Office Tools, and then click Microsoft Office 2007 Language Settings. 3. In the "Available...

Routing Engine not starting, please help ASAP
Exchange and Windows 2003 I cannot get the Exchange Routing engine to start up. Upon startup I get an event ID 7023 telling me that the path is not found. Everything else works and starts up just fine, people can open mailboxes, but cannot send/receive emails. I read online that it sounds like the metabase.bin file is corrupted. I uninstalled/reinstalled IIS, reinstalled Exchange 2003, and have reinstalled Exchange 2003 SP1, everything works except that I still cannot get the routing engine to kick back on. Any suggestions! Thanks! Was this an OEM preinstall? I recently bought a ser...

IE 8 toolbar
I'm using IE 8 with Windows xp. Yesterday the toolbar background turned black, and on the right hand side ofthe screen (where print, home etc icons are) it doesn't fully stretch to fit the full bar, so I see parts of my desktop in it. The whole bar doesn't seem to be properly placed from one end of the screen to another. Is this a bug or known issue that will be corrected, or is there something I can do to fix it? Thanks for any help. ...

Auto-fill To : field not working right
When I begin a new message, some names that put in the To: field that are in my contact list will automatically fill with the e-mail address, but some will not. I have it set to do that in the options. There are names in my contacts with the e-mail addresses that I have used before in e-mails and they auto fill when I start the first name. For testing purposes I created a new contact with a fake e-mail address and then started a new message with that person's name and it filled in the address. What gives? Yes, I do have it set to look in contacts and not the address book. Do...

Counting only the "right" activities
I'm making a timeregistration-sheet for work. I have set up the shee very simply - each row is a date in a month and for each date I hav set up three sets of cols with "time" and "activity" so that when registrate my time I choose X hours in col b and the activity I hav used X hours on (defined from a list with 9 different task types) i col c. This works allright - simple but whatever. Now I want to sum up how many hours I for the whole month have spent o one of the 9 task types and run into problems finding the prope formula. Basically I tried with "=SUMIF(C3;...

Query to delete characters to the right of a decimal point
I need to write a query to help me clean up some letter codes in my database. The codes could have any of the following formats... XY001.01 AP002 SDFJ003.01 JUOI005 I would like the query to return everything to the left of the decimal point, so the data returned would look like this... XY001 AP002 SDFJ003 JUOI005 Thanks in advance for your help. -- Leslie M But didn't you give an example that has NO period in it? One way to approach this would be to use an IIF() statement that checks for a ".", perhaps something like: NewField: IIF(InStr([YourCode],".")...

Please Help: Additional TSL Questions
Ok so I need to deploy Exch2003 so that it can communicate to one domain via TSL. I've never done this before so please bare with me. 1) I know I need to acquire a cert so I've visited Verisigns site and it needs me to "Generate a Private Key Pair". The instructions an Verisign site refer to web servers but in my case I need the cert for my mail server. Should I generate this Key from my SMTP Virtual Server? 2) The IIS Certificate Wizard also request "Name" Can this be anything or does it have to be some identifying name such as the FQDN. If it needs to ...

Right Click Menu
Is there anyway to customize the right click button in excel and add things that you do often or can you only add them to the toolbars??? Brent Bortnick Hi Brent This menu is the "Cell" menu and you can add things to it like the other commandbars. Sub Test_Cell_Menu() ' Add Paste Values to the menu Application.CommandBars("cell").Controls. _ Add Type:=msoControlButton, ID:=370, before:=1 End Sub Sub Reset_Cell_menu() Application.CommandBars("cell").Reset End Sub See also http://support.microsoft.com/default.aspx?scid=kb;en-us;830502&Pr...

please wait while microsoft outlook exits
In Outlook 2000, keep getting the above message when trying to exit from Outlook 2k. Re-installed, but no difference. Any assistance would be appreciated. ...

HtmlHelp doesn't shoe the right page
I have a CDialog based application and i called HtmlHelp(this->m_hWnd, helpFilePath, HH_HELP_CONTEXT, dwData); in my WinHelp method. I checked the helpFilePath contains the right path, "c:\myapp\hlp\help.chm" and the dwData contains a right Help ID. I checked ..ali file, it contains correct information, like HID_CALL = Directory\Call.htm and in my hlp directory, it has a folder called Directory and has Call.htm. However, the HtmlHelp always return NULL. Would you please give me any ideas why the help doesn...

No right mouse click
I hav no save picture as when I publish my site, please help. The Publisher html coding engine in 2003 and 2007 uses VML which turns off the right click to save the picture option by default in IE. If you view the page in FireFox you can copy the image. If you have more questions about Publisher webs, then post in the web group and we will try to help you there: microsoft.public.publisher.webdesign DavidF "Nightrider" <Nightrider@discussions.microsoft.com> wrote in message news:6FACDE18-6546-45E9-84A3-021A6D5BFDAC@microsoft.com... >I hav no save picture as when I pub...