MS Access Help Required

[Supernova9]

Wasn't sure whether to post this here or in the P&Disc. Forum, but here goes.

I'm using Microsoft Access to make an orders database. I have an Order table, which stores details of each order (OrderID, CustomerID, Date of Order, Total Price, Staff Member creating).

Then I have an OrderLine Table, which contains the details of each line of the order (OrderLineID, OrderID - which links it to the original order, and Title (The primary Key of my Product Table)).

Finally I have a product table, which contains details for each product - Title/author/Publisher/Format/Price/Supplier ID.

Now what I want to do is somehow work out the total price of the order automatically. For each order, It would have to look at each Orderline entry linked to that order and use that product title to find the price from the product table, repeating this for each of the orderlines, and then adding them all together before adding it into the Order table as Total Price.

Any Idea how I'd do this? I'm guessing a VB module of some kind but I'm stumped as to how.

[Not_RIT]

Originally posted by Supernova9
Wasn't sure whether to post this here or in the P&Disc. Forum, but here goes.

I'm using Microsoft Access to make an orders database. I have an Order table, which stores details of each order (OrderID, CustomerID, Date of Order, Total Price, Staff Member creating).

Then I have an OrderLine Table, which contains the details of each line of the order (OrderLineID, OrderID - which links it to the original order, and Title (The primary Key of my Product Table)).

Finally I have a product table, which contains details for each product - Title/author/Publisher/Format/Price/Supplier ID.

Now what I want to do is somehow work out the total price of the order automatically. For each order, It would have to look at each Orderline entry linked to that order and use that product title to find the price from the product table, repeating this for each of the orderlines, and then adding them all together before adding it into the Order table as Total Price.

Any Idea how I'd do this? I'm guessing a VB module of some kind but I'm stumped as to how.

No idea how to do it with access, but I could probably do it with Excel. As a matter of fact Im currently working on an Excel/VBA stock/sales/user management program at the moment.

perhaps: tables("tablename").Cells(first numerical cell, second numerical cell).Value

If A and 1 are your First numerical cell and Second numerical cell then you would put in 1, 1

Maybe that will let your reference Access in VBA, check the msdn libary for the commands :/

[Structural Integrity]

You could use a custum SQL query to display this:

SELECT SUM(product.price) FROM orders,products WHERE product.id=order.productID AND order.id = 1

Basically what you want to use is the SUM() function of SQL. I have no idea how to implement this in access, since I've last done access a year or two ago.

[Raging.Retard]

Access supports 'an implementation' of SQL (its quite messed up though... it likes parenthesis around everything). The easist way to do it would be to use the query generator to select all the price rows, then just change to SQL view and append SUM( ) round the coloumn in question.

[Caesar2]

you use a form with a subform?