Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Microsoft Access 2007 question

  1. Sep 22, 2008 #1
    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?
  2. jcsd
  3. Sep 22, 2008 #2


    User Avatar
    Science Advisor
    Homework Helper

    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.
  4. Sep 23, 2008 #3
    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:

    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.
  5. Sep 23, 2008 #4
  6. Sep 23, 2008 #5


    User Avatar
    Science Advisor
    Homework Helper

    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.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook