How do I create a from that can up date data linked to multiple tables.

[mljohn]

I am new to access.



How do I create a from that can up date data linked to multiple tables.

I'm using Access and Excel 2007

I know how to import an Excel spreadsheet as a table.

I have several supplier price lists in Excel.

I want to keep my vendor price lists up to date.

When one of my vendors tell me that a price has changed on a particular item, I figure that I could have a form that I could use to enter the changes.

I believe the form would look like:

Field: "Vendor" (drop down list to choose from. Name of the Supplier price lists) Required.
Field: "OEM" (Key Field found in each table) Required.
Field: "Brand" (Field found in each table) Not required.
Field: "Price" (Field found in each table) Required.

OEM would be the unique key field.

If I enter the Vendor name and then the OEM number it would show if there is already that number in the Vendor price list and I could make changes.
Or I could enter new data in that vendor price list.

Thanks for your help.

Matt

[Rawb]

Honestly, this isn't a project for a novice.

My suggestion, unfortunately, is that you do one of two things: Either find a programmer that can put this together for you (you'll probably end up paying for it, but this is a relatively small project so it won't be crazy-expensive), or you can go snag an Access book, take a couple of weeks, and learn to do it yourself.

Either way, what you want is beyond the scope of this Forum: You're asking to turn a collection of Excel Spreadsheets into a fully functional database application.

If you should decide to learn Access and run into issues putting your solution together, I'm sure many people here - myself included - would be more than happy to help out. As it stands though, what you're asking is simply far too broad for anyone here to be able to provide you with a solution.

[mljohn]

Why is it so difficult? Is it because the form is linked to multiple tables and special programming would be required to choose which vendor?

Would it be easier if the form was linked to only one table?

Matt

[Rawb]

There are two reasons for it, and yes, that is one of them. As long as you're only dealing with linking to and updating a single Table, creating Forms through the Wizard is easy enough. Once you start adding additional Lists, each dependent on the other and each from separate Tables, you have to use VBA programming to link it all together, which the Wizards can't do for you.

The other reason is that, ideally, the Tables within a database are designed along principles that make translating things from a flat spreadsheet difficult to do without first understanding them. For example, in your Excel spreadsheets, you probably just have a "Vendor" Column. In a database, this will be it's own Table that you'd have to then link to the data.

[ssanfu]

Correct me if I am wrong, but it sounds like you have a separate price table for each of your venders. Is this right?

[mljohn]

Yes, that is correct. I am assuming that is why it would be difficult.

Would this be easier if there were only on price list table?

Matt

[John_G]

One question -

Are you actually importing the data from the Excel spreadsheets into MS Access tables, or are you just creating links to the Excel files?

If you are using links, then updating the Excel files cannot be done easily. MS lost a court case a number of years ago, in which they were ordered to make linked Excel files un-updatable from Access.

If you are importing to Access tables, what you want to do can be done, but will require VBA code.

HTH

John

[mljohn]

I want to import my data from Excel to Access. Once the data is there I want to use the form or forms to keep the data updated. This will be only occasionally since it won't be needed very often.

Then when I need to (rarely) I will export the tables to Excel.

I would be willing to have a form for each table if need be. I am guessing that I could start that way and then later have someone do the programming to make one form do it all.


Matt

[ssanfu]

Yes, that is correct. I am assuming that is why it would be difficult.

Would this be easier if there were only on price list table?

Yes, in a normalized database, there would be one table for the prices.

Like John said, it could be done with (a lot of) VBA.
But lets say you finished your dB, then you acquired 2 more vendors. You would have to add two more tables, create/modify queries, forms and reports.
If there was only one table for vendor prices, the queries, forms & reports wouldn't have to be modified. This is what normalization is all about.

[mljohn]

I see.

I have one table that has all the info on each item.

All of my vendors tables only have 2 fields. Part # and Price.

They would all be linked by the part # (OEM) field.

Matt

[ssanfu]

This is how I would set up the tables:

Attachment 8648