Auto completing a field from another table-possible?

[BillyDo]

Hi everyone. My first post and a very newbie question to ask. I have attached a database (extremely simple) to help explain what I mean.

I have a purchases table which I want to use to record purchases from our suppliers. I also have a suppliers table which appears like a matrix. It lists all our suppliers and the prices we pay for different types of material with each one. There is a finite number of materials and each supplier charges us differently for each one.

What I want is to for the cost field in the sales table to look for the relevant price in the suppliers table based on the Supplier Id and the Material ID. Hopefully the atatched will make it clearer what I mean!

I suspect I need to add other tables. The idea is complicated by the fact each supplier charges differently for each of the 4 materials. I ideally want to be able to retain the price matrix, so that it can be updated as and when prices change.

Many many thanks to anyone who takes the time to look at this.

 

[PaulO]

You will reach a wider audience if you can save a copy of the database into A2003 format (.mdb)

What you want is achievable, though I think you may wish to consider something less complicated and rigid than a matrix (assuming I've understood fully what that means). I would consider the following tables:-

1. Purchases table will still record individual buys
   • Supplier
   • Invoice No
   • Purchase Order Number
   • Material
   • Unit Price
   • etc,etc,etc
2. Master Materials table
   • Code
   • Description
   • UOM
   • Product Group
   • etc
3. Prices table will store multiple prices for the same Material from unlimited suppliers and also store all historic and/or future prices
   • Material
   • Supplier
   • Current/List Price
   • Price Effective date (from)
     • Current prices can always be obtained by writing a query on this table limiting results to the most recent effective date

 

[BillyDo]

Thanks for your response Paul.

I have attached it in.MDB now, good tip

Assuming I organise it as you suggest (which looks pretty sensible). How do I then ask the Purchases table to reference the prices table to automatically find the price?

Many thanks

 

[PaulO]

Forgot you'll also need a Suppliers table (code, name, address, etc)

You'll end up writing a suite of Queries linking all these tables together, for example in creating a purchase order Form where you first select a Material then create a drop-down list linked to your Prices table to enable you to see which suppliers have this material and at what price, etc.

 

[BillyDo]

Thanks a lot for your help, I do agree I need to restructure my tables.

I should have explained that my sales table will have quite a lot of data imported from a spreadsheet (I know how to do this) however the cost of the purchases will not be imported. Therefore I am looking for a way of automatically calculating the cost, based on the prices in the prices table. The queries I can build later.

Is anyone able to suggest anything?

Many thanks

 

[BillyDo]

Thanks to anyone who has helped with this. I dont feel like I explained properly what I want to do and dont want to keep bumping the thread here.

I have therefore created a new thread over at another forum which I want to put a link to here out of courtesty to anyone who might be looking at this. However it seems I am unable to post links with my post count.

the new thread exists at Utter Access on the Tables forum under the username redj.

Many thanks