Microsoft Access 2007 question

[Shaun Culver]

I have two tables that I would like to link in a specific way. Both tables have a field name labelled, for example, "Price". Both also have a field name labelled, for example, "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?

 

[NoTime]

Sounds like homework to me.
Try looking up the update and join SQL commands.
Post your attempt at the SQL for this it you still have problems, although the Update Wizard will probably do it for you.

Note: Replicating the price field probably isn't a real good idea and your stock table should have an effective date in it.

 

[Shaun Culver]

Nope, not homework.

I am new to Access and databases, so I do not understand how to implement SQL code. Are you referring to this:
http://www.sqlcommands.net/sql+update/

What is the "update wizard"?

Excel allows a "by hand" method for this. By typing a, for example, "Price" in block A1, and then dragging the bottom left square icon down, you may replicate the same entry n number of times. Is there something similar in Access? By filtering the records according to "Item", the same price can be applied to all records.

 

[Shaun Culver]

I found a solution to my problem:
http://www.techonthenet.com/access/queries/update2_2007.php

Thank you.

 

[NoTime]

While some concepts are shared between spreadsheets and database the implementation and terms used are different.
Even in Excel you don't have to do that by hand.
Multiple sheets and a macro will do it for you.

In general you need to know what the commands in the link you posted do.
The wizard is query tab -> new -> update.

For filtering in the case you mention you would join the two tables on item_type.
The command Update ... Set can the replicate the fields.

There are several ways of writing this in SQl, here is one approach

Update receipt
Set receipt.price = stock.price
From receipt
Join stock On receipt.item_type = stock.item_type;

This may not be entirely correct since access tends to be bracket happy.

You probably don't want to do this though.
You can join the tables anytime you want the associated data.

Edit: should have looked at the post again, I got called away for a while.
Good luck.