Table problems

[honey2wood]

Hi to all
Can someone help or direct me in the right direction please ?
I have a large table linked to an Excel spreadsheet with 30 or so columns. The Spreadsheet changes on a daily basis so the Access table also changes. No problems there.
Where I am having problems is trying to get other tables in the database to link to it.
For instance, part of the table has a Purchase Order number for which there are part nos. part values, and quantities. From this I can get a total PO cost.
What I now want to do is link other tables. For example I can have a table which contained hours worked to give me an hourly rate. I have linked a table using the record number of each table so that each purchase order has a rate.
Unfortunately when the main table is updated the record numbers increase the other table does not grow.
One solution would be to just add other column to the existing table but that seems to defeat the object of a database.
Is there any way I can get this to happen.
I am probably ignoring something simple but this is holding me up.
Many thanks
Graham

 

[David Eagar]

Can you attach your database? It would make it easier to see solution(s)

 

[honey2wood]

Hi David
Many thanks for looking at this for me.
I have attached a cut down version as there is so much data.
You will see that I have tried a Linked table as well as an 'import and append' but when they are updated the other 'Costings' table remains the same size until manually increased.
I am not sure this makes sence but I dont think I can explain it any other way.
Regards
Graham
Doesn't look as if the attachment came over.

 

[honey2wood]

With Attachment ???

 

[David Eagar]

Looking at your data, a Production Order can have more than 1 item, each with its own Production Rate, so I think you need a 1 to many join (see attached)

 

[honey2wood]

Hi David
Many thanks for looking at this for me.
That will link the tables thanks. But I need a record on the costings table for each of the part numbers so that I can enter the hours.
There are 61 PO records but the Form 'Table1' only has 21 records.
Each day when new records are appended to the main table the costings table needs to grow as well even though initally there will be no hours.
As I said earlier I could just add the extra columns to the main table but if I do that I could use an Excel spreadsheet.
Costings is just a part of original requirements. There are invoices, training records etc. all that need info from the table. If I can do this for one then I can do similar for the others.
I do apreciate your time and sorry if I seem thick but I just cant get my head round it.
Graham

 

[David Eagar]

This is rapidly developing into something more complex (as most Access applications do) than was first described. A scaled down ERP system is an extemely complex difficult assignement without knowing the answers before you ask the question - BUT, if you get it right, the world is your oyster!

Think thorough and describe accurately, what is your final objective?