Results 1 to 4 of 4
  1. #1
    marcjrcaron is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2019
    Posts
    2

    Post Insert value in field based on another field

    Hi, (first post be gentle, very new at Access)


    I have 2 tables. tblBus and tblGoods. tblBus has 3 fields (BusID, Date, Destination). BusID is primary key and autonumber. We ship goods on buses. Everytime we ship on a bus, I add the bus in this table.

    tblGoods has 4 fields (GoodsID, BusID, Date, Destination). GoodsID is primary key and autonumber. When I print my packing slip, the report writes on which bus the goods left. I don't know how exactly I did it, but it works. If the goods left on bus 5, the number 5 is written in the field BusID in tblGoods.

    I would like now to have a query or something similar, that would get the infos from tblBus (Date,Destination) and write them in tblGoods (Date, Destination).

    Thanks in advance
    Marc

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I don't know how exactly I did it, but it works.
    Really?

    Date is a reserved word in Access-- you should avoid using reserved words.

    Also, we normally think in terms of
    -We/our company ships/delivers Goods to a Customer by some vehicle(Bus)

    which might represent
    Customer--> Order-->OrderItems<-- Item/Goods
    Order-->Shipment<--Bus

    Are these purchased goods as in Customer ordered these Goods?
    Can a shipment (set of Goods) be split an shipped on more than 1 bus?

    You may get some ideas from this free generic data model from Barry Williams' site.

    Good luck with your project.

  3. #3
    marcjrcaron is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2019
    Posts
    2
    I have started working with Access 2 weeks ago, because my boss told me so. I have been reading on forums, watching tutoriuals and trying to make something with the orders I've been told.

    I can change the names of the fields to DateDeparture. Basically, I would need a function that would get the infos from one table and insert them into another table based on the BusID. If the row would says:

    BusID DateDeparture Destination
    5 2019-03-21 Boston

    I would need the tblGoods to update the fields DateDeparture and Destination with the value of the other table.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I understand you are new to Access which probably means you are also new to database. We all had to start, so many here can identify with your situation. I'm providing a link to several articles on Database Planning and Design with an Access theme. I strongly suggest you work through 1 or 2 of the tutorials from RogersAccessLibarary mentioned in the link. The other materials will help put some concepts and pieces into context. But, by working through the tutorials, you will learn a procedure/process that can be used with any database.
    Good luck and be positive.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-15-2018, 09:48 AM
  2. Replies: 2
    Last Post: 05-17-2017, 06:17 AM
  3. Replies: 1
    Last Post: 12-04-2016, 05:43 PM
  4. Replies: 11
    Last Post: 06-15-2016, 06:20 PM
  5. Replies: 3
    Last Post: 01-09-2015, 05:48 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums