Normalisation Related

[Hulm1]

This is a very interesting thread.

How about this for a question:

I have a quotation form with what could be 100 small components, labour, delivery etc.

If I get the order I want to pass the sum of the three categories (Components, labour and delivery) to an Order Confirmation Form. I don't want all 100 small parts etc. to go to the Order Confirmation form.

I do want a simple button to it though. I was planning to use a VBA append (INSERT INTO),

Is this a terrible idea (even assuming I could work out how to pass the calculated figure? Or is there a better way

[Stewart Ross]

Hi Hulm1. You may be confusing what you do with the order data once you've got it with how derived data is stored. There is simply no need to store totals separately if you wish to prepare a summary confirmation of an order.

As previously discussed, the individual order lines will contain a unit price, tax rate, quantity and so on. If the tables you use are stuctured correctly for your needs you already have all the detail needed to prepare an order confirmation at any level of detail that suits you.

A report based on a suitable order line query can be used for the purpose you describe - for example by leaving out the detail section and placing group totals in a section footer to aggregate the order lines, tax elements and so on. Or, you could prepare a totals query which sums the individual order lines for you. In any event, there is no need to use a redundant INSERT INTO approach to store such totals in another table, unless in your particular case you would need to record such details because of circumstances such as exchange rate variations etc.

What you would require on a user form somewhere is a command button whose on-click event starts a specific order confirmation report as I have suggested, filtered for the current order. The Access command button wizards will even do this last step for you.

There are many good examples of such approaches in books and elsewhere, including in the sample Northwind database supplied with Access.

-Stewart

[Hulm1]

I do have exchange rate issues. Euro and Dollar against our costs mainly but occasionally our sales. What would be the best approach? Mine or another

[nico5038]

If I get the order I want to pass the sum of the three categories (Components, labour and delivery) to an Order Confirmation Form. I don't want all 100 small parts etc. to go to the Order Confirmation form.

For this a Group By query can be used. Just make sure the [Category] field is in your detailed order line info. So no need to store these intermediate subtotals.

A different "problem" is the exchange rate. As they can differ, the actual order price needs to be calculated, or stored.
The real "normalized" solution is to have an exchangerate table with a date start/end of the rate. Using the Orderdate the exchangerate can be obtained and the price calculated.
The "not normalized" solution is to have one exchange rate field that's used at the moment the order is finalized and the foreign price is stored.

The choice is yours.

Nic;o)

[NeoPa]

@Hulm1
Indeed, but not yours, which makes this diversion a hijack. Please refrain from this in future. If you have a question, feel free to post it in it's own thread. If you feel it relates to another thread then by all means include a link to the other thread in your own question. What you may not do is hijack it.

For the interest of any other parties, this question was split away from (and may have some relevance to) Is it possible to append the result of a calculated text box on a form to a table.