Returning Query data to Tables

[ukdane]

I have four tables.
tbl1, tbl2, tbl3, tbl4
tbl1 contains: ordernr (primary key), town
tbl2 contains: ordernr (primary key), country
the two primary keys here are joined in a relationship.

tbl3 contains: id (primary key), town, and countryid.
tbl4 contains: countryid (primary key), country.

I've created a query that lists:
tbl1:ordernr, tbl3:town, and tbl4:country

The relationship for the query is:
tbl1.destination=tbl3.town and tbl3.countryid=tbl4.countryid

And it all works beautifully :-)

Now, the observant amongst you will also have notices that on tbl2 there is a field called country.
Every time the user adds an ordernr to tbl1, the same nr is added to tbl2.

I want the query (or some code/macro) to automatically add the country to tbl2 based on town and ordernr on tbl1.

Can someone tell me how to achieve this please.

Thank you.

[CreganTur]

Ummm... any reason why you aren't using a single table that contains: ordernr(PK), town, country, countryid?

It look sliek you're basically splitting every field into a different table, and I can't understand why.

[ukdane]

tbl1 is data imported via an external program.
tbl2 is data that the user enters, or in this instance "generated".

So in this instance the order number is imported into tbl1 and tbl2.
A town is also imported into tbl1.

The country is not imported, and I want the database to find it automatically. Hence why there is a table of towns and countries.

The actual country could be placed into tbl1. But it still needs to be an automated process.

Does that make sense?

[ukdane]

To be honest, I think I'll also need to use this function for another purpose to-

I'll want to apply an "order status" based on the contents of certain fields.

I'll be using tbl1 one and tbl2 again, and a query.
The query looks at individual items in the table, and returns a "status" based on the records contents.

(For example: If a "booking" field is empty the status might be "POST ORDER" and so on).

I can create the various Status without a problem (Although one of the status IS based on the country in the problem above).

But I need to "save" the order status with the order nr so I can report it on a datasheet form. (On a regular form, I can use a subform to show the status, can I use a subform on a datasheet???)

Cheers

[OBP]

ukdane, you would normally do this with a Combo on the Form that displays Towns and Countries, so that when you select a Town it populates the Country if needed, but if you have a Table of all towns and Countries why store the data in a table, the Town (ID) should be used to reference both the actual Town Name and the Country for any kind of "Display" purposes.