Write calculated value to a table

[Kasghost]

It seems like this should have been easy to do but a solution is just eluding me. Here is my situation, I am working on an Access DB for a class project, my instructor wanted me to create a form based on a table. In this table I have two fields, Computer_Price and Total_Price. These are calculated off of data from multiple tables that are used for lookups and contain the pricing for the individual components. The problem I have is that I can get the calculated values on the form but I cannot get them to write to the table. I've been thinking that I may need to do the calculations in VB. Would this be the easiest way to go about this?

Thanks
Jayson

[Delerna]

I've been thinking that I may need to do the calculations in VB.

That would be one way, but definitely not the only way.

Would this be the easiest way to go about this?

There are too many variables and not enough info in your question to say.
What is easiest also depends on the knowledge and skill of the developer.

Might I suggest that quite often, in assignments, there are many clues as to how you should go about the problem, embedded in the assignments documentation.
So try re-reading it with that thought in mind. Also, the answer is likely to cover topics recently covered in class lecture notes. Try going over those also.

Good luck

[nico5038]

I would start with reading the article about normalization:

http://bytes.com/forum/thread585228.html

What you are trying to achieve is called "storing redundant information"...

Nic;o)

[missinglinq]

As delerna said, I think maybe you should re-read your assignment carefully. Is there actually a requirement that states that these calculations need to be stored in a table?
As a general rule doing so is considered a cardinal sin, although, like most rules, there are exceptions to it. The usual approach is that you simply do the calculation each time you need the results, whether it be in a form for display or in a report.

Linq ;0)>

[Kasghost]

Thanks for the quick responses. As for re-reading the assignment, it wouldn't be much help as it is very open ended. And this class does not have lectures, two students and the teacher, and other than the project work I have done we have not covered anything in Access yet. I'm relying on my limited skills from my first Access class. The premis is to create an online computer store where customers can purchase computers based on selected components, ie. hard drive, ram, processor, monitor. I have seven tables in my DB. The first is a customers table, has normal information ie. name, address, zip code ect. I ten have five tables taht store infomation on the different components, each table has two fields Name and Price. These tables are just used to provide lookups for the Orders table. The final table is the Orders table. My fields in this table are as follows:

Field Name: Data Type:
Order ID Auto Number PK
Customer ID Number FK
Hard Drive Text
Ram Text
Monitor Text
(A few more for the rest of the lookups)
Computer Price Currency
Quantity Number
Total Price Currency
Order Date Date/Time
Ship Date Date/Time
Received Date Date/Time

I do understand that setting it up this way limits each order to only one computer type but this is by design for simplicity, or so my instructor says. I do need the two Price fields as I am going to need to have a record of what the cost of the purchase was so that I can bill for payment. I know at some point I am going to have to create a shipper so I was kinda trying to prepare for this. The form I created is going to be linked to a website, I'm not doing that part, and will be the only source of data entry. Any help would be greatly appriciated.

[nico5038]

Perhaps worthwhile to check the free Northwind.mdb sample database that comes with Access and can be downloaded fromthe MS site.
It's clear example of an ordering application.

Nic;o)

[ADezii]

It seems like this should have been easy to do but a solution is just eluding me. Here is my situation, I am working on an Access DB for a class project, my instructor wanted me to create a form based on a table. In this table I have two fields, Computer_Price and Total_Price. These are calculated off of data from multiple tables that are used for lookups and contain the pricing for the individual components. The problem I have is that I can get the calculated values on the form but I cannot get them to write to the table. I've been thinking that I may need to do the calculations in VB. Would this be the easiest way to go about this?

Thanks
Jayson

I'm with missinglinq on this one, since derived values (resulting from calculations) should rarely be stored in a Table. The usual logic in circumstances like this is to perform the calculations within a Query, then set the Record Source of a Form/Report to this Query. If you insist on this approach, you can always pro-grammatically calculate the values then write them to a Table using one of several Methods, or even make the previously mentioned Query a Make Table Query to generate the appropriate data.

[Stewart Ross]

Hi. In addition to the comments from fellow contributors to the forum, can I add that it is not in any way normal practice to have separate tables for components such as hard disk, monitor, RAM etc - not even in a teaching assignment. It tends to break the rules for database normalisation to do so...

Although all forms of table decomposition are to some extent arbitrary it is unnecessarily complicating your design to have to consider separate unit costs and quantities for each of these item types (hard disks, RAM etc.).

In any ordering application the items ordered can be cataloged and distinguished in many different ways. It seems to me that you are using separate tables for types of hardware, when the hardware type is just an attribute of the item offered for sale. The item type can be stored as a reference table containing some form of item type code and what it means.

Similarly, in any real world application of this nature there would be other classifications applied as categories and sub-categories - (application software, operating system, hardware component, etc). Look at any commercial web site selling computer components to see how it is done.

If you think through in words what you are trying to achieve you will often identify some of the main tables you need, and some of their attributes become clear in doing so:

"Each COMPUTER is one of several types - including Desktop systems, Servers, Laptops. Each Desktop computer has a Base Unit, a Keyboard, and a Monitor. Each Base Unit has a Case, a Motherboard, one or more Hard Disks, a Power Supply, one or more RAM cards, a Processor... All these items are COMPONENTS available for purchase to make up a SYSTEM of a particular SYSTEM TYPE.

Each COMPONENT has a Catalog Number, Current Price, a Quantity in Stock, a Tax code ...."

This may gives you some idea of how you can help yourself to organise what you are trying to do if you are struggling to do what texts on normalisation expect you to do: to identify the individual tables and the attributes of them, whilst decomposing out repeated groups and so on.

As suggested in earlier posts, Northwind is a good example to look at. Basic texts on database design also usually cover identifying tables and basic normalisation as well.

Good luck with your project.

-Stewart

[Delerna]

Here here Stewart. Well put.

With the 1 table for each computer component approach, what happens when they bring out the next supersonic high performace hardware item. Like a holographic crystal drive. :)

Well a new table would need to be created and then linked into all the queries somehow. Maybe a redesign of some forms...who knows what else.

In a properly designed database you should be able to just add the new component to a components table and away you go, you can begin selling it.