Generating totals from another table

[Guest]

Hi

Wondering if someone could point me in the right direction

I'm proficient in excel, but am relatively new to access. I can set up tables, queries and reports (basic)

Here's my problem

I have a table of contract notes with despatch and delivery zones, I also have the size and cost for each transaction
I have another table with rates attached for a number of suppliers

How do generate in a column a proposed new cost per transaction

Table
Field 1 field 2 field 3 field
From To KGS cos
Mel Syd 5 $10.0

Table
Field 1 field 2 field 3 field
supplier From to Cos
ABC Mel Syd $9.0

So in a column I have proposed new supplier and in another a proposed new cos

Hope this is easily understood

Reg

Acke

 

[Guest]

It seems as though if you add both tables into a query and like the To fields and From fields together, you can pull in the new supplier cost. Do you want to change the existing cost? If so, you would need to do an update query where you have the tables linked and the [Table1]![Field4] would be updated to [Table2]![Field4]. A word of concern however since I do not know your table structure, where you may have the To and From in Table 1 as key fields, the table 2 may not have one record with the value. You may need to determine the unique (cheapest) record from the second table before updating


----- Acker wrote: ----

Hi

Wondering if someone could point me in the right direction

I'm proficient in excel, but am relatively new to access. I can set up tables, queries and reports (basic)

Here's my problem

I have a table of contract notes with despatch and delivery zones, I also have the size and cost for each transaction
I have another table with rates attached for a number of suppliers

How do generate in a column a proposed new cost per transaction

Table
Field 1 field 2 field 3 field
From To KGS cos
Mel Syd 5 $10.0


Table
Field 1 field 2 field 3 field
supplier From to Cos
ABC Mel Syd $9.0

So in a column I have proposed new supplier and in another a proposed new cos

Hope this is easily understood

Reg

Acke