Making multiple sheets and linking back to the first page for data

[JGF]

We just got Access at my workplace, and none of us know very much about it. I apologize if the answers to this turn out to be very obvious.



I've been working in Excel for the past week manually copying some inventory sheets for my company. The main sheet is formatted with columns representing the item name and description, unit type specifications, and some price measurements. You can take a look at it here: http://i.imgur.com/VUpiw.jpg

What I was doing with this was creating additional worksheets for individual sales that use the items listed, and linking each unit in these additional pages back to the first page's price for each corresponding item to make sure the costs are accurate. We are planning on editing that first list quarterly to update prices and add and remove items, and have already found that that process screws up the equations in the other sheets. We purchased Access as a result, hoping to save that first page in a database and more efficiently streamline the process.

What I'm hoping to find out is how to essentially "link" to cells in the first table that lists the items. In Excel, it was done by pressing the = button and clicking a cell in the worksheet I wanted. Here, I'm not sure how it works. I'm also hoping to be able to do basic math formulas in these cells, such as adding, multiplying, and dividing certain totals. A coworker heard that it's possible to create a simple drop-down menu for you to select from a list of items instead of manually searching for the cell you want. Is this true?

Thank you to anyone who can answer this.

[June7]

Review these tutorial threads. They should give you a basic understanding of relational database concepts and Access capabilities.

http://forums.aspfree.com/microsoft-...el-349267.html
http://forums.aspfree.com/microsoft-...es-208217.html
http://forums.aspfree.com/microsoft-...os-197463.html
http://www.developerbarn.com/communi...-database.435/

Unless you get Access 2010, cannot do calcs in tables. Tables hold raw data and queries are used for data manipulation.

Your friend described a combobox control.

[JGF]

Thanks. I'll give those a look.

I actually made a mistake in my description; it turns out I do have Access 2010. How does the calc function work in that?

[June7]

Look into Calculated datatype field.

[JGF]

Is it possible to make pre-existing fields that are imported from Excel files calculated, or will I need to redo those entirely? When I right-click the ones that already have text or numbers in them, the only options are sorting and text filters.