MS Access query - critique my table relationships - Dolph?

[Participant]

Hi all

I am trying to design an Access database for work. It is a sales database that needs to be able to take sales orders and analyse them by business sector, salesman responsible, customer etc. and be able to keep tabs on the original currency of each order as well as each invoice that has been raised against each order. It's mainly these last two bits that I'm struggling with.

Here is the layout as it stands:

One problem I have is that one order may have many shipments, but at the moment the one-to-many goes from shipments to orders instead of the other way round, so that I'm telling Access that one shipment might have many orders instead of the other way round. I also cannot immediately see how to relate the currencies correctly.

I need to be able to change the exchange rates for each currency on the main menu form, and have that instantly update the Euro values for each record. I also need to be able to say this about any given order:

'Order #12345 was shipped in three separate stages on three separate dates and with three different invoice amounts.'

Very grateful if you can point me in the right direction. I should probably say that I'm aware that I haven't normalised all data correctly, for example the customers table could be split into say customer details and customer address, but any helpful criticism will help me learn and is appreciated.

EDIT: I've had a play and come up with this. Should work I think? Can anyone spot any problems / bad database technique?

 

[Sin_Chase]

Would it not be prudent to be storing the exchange rate the order was placed with?

I'm not sure how dynamically updating Euro values of backdated orders would provide useful information?

 

[Participant]

Ok I'll explain the exchange rate thing a bit better.

There isn't really a relevant exchange rate at the time of order (or not one that needs to be analysed by us anyway). The customer either places an order in Euros, USD, pound sterling, etc...

The relevance of the exchange rate is: what was the exchange rate when the order was shipped, and what was the exchange rate at the end of the month when I produce an order backlog report.

All I need to be able to do at the end of the month is enter the month end exchange rates so that I can produce a report that will produce sales and order backlog figures for head office, in Euros. Currently the person sticks it in at whatever exchange rate they fancy and it stays at that Euro equivalent value irrespective of the fluctuations in the money market rates. So it's inaccurate as once an order has been entered for example that was originally in USD, it stays at the Euro equivalent forever rather than automatically reflecting current rates from a central exchange rate table (which is what I want).

I don't want to have to calculate values within the table, only to be able to produce Euro values on queries / reports.

 

[hideki]

The second diagram looks better normalized, although its hard to really tell if it could be further refined without knowing exactly what some of the data fields are. But good job.

Looking at the diagram, the only thing that looks strange is the relationship between invoice and shipment. If it is a many to many relationship, you may need another table between those two.