Year 12s: what will be the first way you research your future options? >>

Access help

This seems more complex than it should be in my opinion but I can't work out how to do this. In my opinion the way my database has been designed is wrong but I'm not sure.

I want to calculate the amount due but the problem is I have two tables which have the following fields:

Order table:

OrderID
CustomerID
Date amount due

Order details table:

OrderID
ProductID
Quantity

I want to work out the amount due by multiplying the quantity with the price of the product which is in the products table. The only thing I've been able to calculate using a query is the total amount due from all the customers.

If I can calculate the amount due in a query for each specific customer then I could remove the amount due field for the order table, I think :confused:

Reply 1

butterflyz88

One of the big things when designing your database and doing your normalistion, which conveniently noone seems to tell you, but I'm kind so I will :smile:

, is that you should NEVER store calculated data. By the sounds of it, you have an amount due field in one of your tables. DON'T!! You are right in saying that you should get rid of it and use a query instead.

You should be able to design a query with the Order Details table and the Product table. Include the Quantity field from Order Details and the Product Price field from the Product table. Then create a field for Amount Due, which should look like:

Amount Due:[Quantity]*[Product Price] (or equivalent names that you have used)

When run, this should come up with a whole list of quantities, prices and amounts due. Problem is you dont know which customer each is for. So include the customer ID field in the query and tick show.

Et voila! This should work. Hope it helped! :smile:

Reply 2

TwoDot

I've now deleted the amount due field and you're right it's not needed at all although worryingly it was my teacher that said i needed it :confused:

I finally worked it out today and basically what I did was as you said put that sum in to a query then when making a report I could select to have the sum of the total amounts worked out so now the grand total someone owes is below their other details in the report which is great and exactly what I needed, thanks!

:smile:

Reply 3

FatMax

Which is all very nice apart from the small fact that your query calculation is *always* going to use whatever the current price happens to be in the products table. Not good at all :eek:

Reply 4

TwoDot

I never thought of that actually but I'll just not change the prices or do you have to do that or something?

Reply 5

FatMax

*Personally* I'd have a SalesPrice field in the order details table and then update this with CurrentPrice (from the products table) as part of the order process. You could use either a macro/vba to update the price for each product added as part of the AfterUpdate property on the ProductID field or you could run an update query to update all prices when the order process is complete.

Either way you *should* take account of current vs. historical prices in any kind of ordering system. Never changing prices isn't really an option :wink:

FatMax

Reply 6

TwoDot

Is there a chance you could explain that more simply as I'm not really sure what you mean exactly.

I have now tried to use an update query but found another problem.

My grand total for a customer orders is worked out in a report so there is no way of updating a report to say someone has made a payment towards their total so it can't be deducted.

My table structure must be wrong as I'm sure no one else doing an invoice system has these problems.

Reply 7

FatMax

I'll knock together an example db and post it later tonight - should make more sense.

Reply 8

TwoDot

Thanks a lot that will be greatly appreciated!

Reply 9

FatMax

Quicker than I thought
www.fatmax.org/orders.zip

Have a look at the form frmOrders. The combo box has a macro on its AfterUpdate property that sets the SalesPrice (OrderDetails table) to the CurrentPrice (Product Table) - as you're adding items to the order the customer is being "quoted" the current price. If this price changes at some point in the future it doesn't matter 'cos there is now a permanent record of the price you quoted in tblOrderDetails. Use SalesPrice rather than CurrentPrice in any further calculations related to customers e.g. invoice totals, account balances etc.

Try thinking about your original problem like this - You send out an invoice to a customer on 11/01/06 and have calculated the total value at £5 based on prices that day. On the 25/01/06 the customer phones up and says "I've lost the invoice! Can you send it again?" A big problem using your original model if prices have changed since 11/01/06 as your invoice report always looked in the products table for prices.

Your second problem re: accounts/balances/payments should now be fairly easy to solve. Maybe an AmountDue field in tblOrders isn't such a bad idea after all... :wink:

HTH
FatMax

Reply 10

TwoDot

Well that database really did help but I'm still not sure about this amount due field. Will I add up the subtotals manually then enter them in to the amount due field? Then use a payment table with an update query to deduct payments from the amountdue field?

Then I would use a report which would include the query with the subtotals and the final amount due unless there is a better way?

Reply 11

FatMax

There's no *right* answer. If you have an AmountDue field in the Orders table you can add it to the query and form and a report for that matter. Why not put an unbound field on the subform footer to do the calculation for you (a text field containing something like =sum([SubTotal]) and then use that to set the value for AmountDue when the order record is saved - something like the SetValue macro in the example I gave earlier. You can then use AmountDue in the invoice report without the need for any further calculations.

How you deal with payments is a completely different kettle of fish - you could just add yes/no Paid field (default=No) to the Orders table which'd give you *some* flexibility for querying for unpaid invoices at a later date. You just change the status from no to yes when a bill is paid. It's not the only way of dealing payments but it's a start...

HTH
FatMax

Reply 12

TwoDot

I have the amount due sorted now thanks. I will use the yes/no suggestion for now until I can maybe find a better way especially as I need update queries.

Also about the macro how do you check if it's actually added to a form?

Thanks for all the help it's much appreciated :smile:

Reply 13

FatMax

Don't really understand your last question so...

I've uploaded a revised example at www.fatmax.org/orders2.zip
This one includes the calculation/macro I outlined above to set AmountDue stuck on a "Save and Print" button (note: it doesn't print!!!). You can check it's working by looking in the orders table after clicking the button.

Don't understand "especially as I *need* update queries" either. What does this mean?

FatMax

Reply 14

TwoDot

Thank you that's perfect as today I had to add and enter the amount due manually. When I tried to implement this in to my database, when I click on save it says "You can't assign a value to this object" and in the amount due box it says "#Name?". When i try to edit whats in the amountdue box it says you can't edit this box because it's bound to unknown field AmountDue. :confused:

My first question doesn't matter now as i worked that out. About the update queries I just mean you have to have some for coursework but I think I should be able to do that fine now.

Thanks for all the help! :smile:

Latest

Articles for you

Your guide to handling revision and exam stress

Can you get into university if you’re studying Btecs?

Powerful questions business students forget to ask their career advisers

Support at university: students describe where to find help

Access help

Related discussions

Latest

Trending

Trending

Articles for you