Access 2007 question

[shaunquest]

This may be hard to follow. I will try my best to explain.

I have two tables that I would like to link in a specific way. Both tables have a field name labelled "Price". Both also have a field name labelled "Item type".

In the first table, each of my stock items are listed in the "Item type" field, with the corresponding price in the "Price" field.

Now the problem....

In the second table, I have many sales receipt entries, with the item types entered (sorted by date), but no entries in the price field. How do I automatically transfer the price to the second table based on what is entered in the first table?

[NeoPa]

I'm not sure you should. Have a quick look at Normalisation and Table structures. OK. A quick look isn't really possible, but this is nevertheless highly recommended reading and as concise a description as you will find on this fundamentally important topic.

According to normalisation rules, the data should stay in table1 (a product reference table) and the transactions should simply display the linked values within a query (showing both tables linked). The only time you would store the prices in with the transactions is if you considered the prices subject to fluctuations over time. In this scenario the prices would be added on transaction entry and not retrospectively as you suggest.

[mshmyob]

If you could post all your field names for each table and then indicate your foreign and primary keys and then give a short explanation of what your application is supposed to do then I am sure we can give you an appropriate answer.

cheers,

[NeoPa]

That said, I will still provide a method to do as you request. I recommend you follow my earlier advice, but what you choose to do in your database is not my concern. However, as you have provided so little information about your database i can only do so in very general terms.

You would create an Update query based on the two tables linked together on the [Item Type] fields of both tables. You would probably need to filter on the records whose [Price] fields are blank (Null). Update the [Price] field of one table with the [Price] field of the other.

If you need more help then you will need to be clearer about what you're working with.

[mshmyob]

Sorry Neo I posted at the same time as your previous post - didn't see your original post. I agree it appears not to be normalised that is why I requested he give us the table structure along with PK's and FK's (I have a feeling they will need work). Also a description of what the application does will give us a better understanding of what exactly he is trying to accomplish.

Given the little data from the OP your reccomendation is more than I would have attempted to venture.

If I got in the way I am sorry.

cheers,

[NeoPa]

Of course not.

Yours was a perfectly sensible and appropriate post.

[shaunquest]

I found a solution to my problem:
** Link Removed as per site rules **
Thank you.

[NeoPa]

Thank you for posting that you have found a solution. I'm afraid I've had to remove the link (If you check out the rules you will see that links to competing sites are not allowed (Things that are generally unacceptable)). A solution can be posted in the thread - with attribution to the other site if required.

Although there is also a solution in this thread, we appreciate your telling us not to spend any more time worrying about it.

Welcome to Bytes!