SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: linking tables

  1. #1
    SitePoint Member
    Join Date
    Jun 2003
    0 Post(s)
    0 Thread(s)

    linking tables

    Hi there,
    I'm a newish to Access databases and projects and am currently setting up a database to record all sales from a factory, amongst other things. My order table records all sales that leave the factory, the customer, date of dispatch and whether they were promotional products or not.

    I want to automatically update my sales table. At present, I obtain all the sales info from someone in the sales office. He carries out a make table query (AM Sales table) on his sales table to include all sales from 01/01/2003, this is the data I want.

    What I would like to do is use this to automatically update my order table.
    At present, I import his table into my database, (I use project so cannot link) I then have to carry out a view on the data to add certain data from my database (my product id, change the dates etc.) and arrange it so that it fits my sales table. I then copy and paste from the view, this is obviously an extremely tedious way of doing it.

    Can u think of a way I can automatically update my order table with this data without having to do all this copying and pasting?

    As I am working in a project I cannot link to other tables, although have opened a database in mdb form and have linked tables from the project into it.

    I have an order table like this:

    OrderId CustId ProdId casesSold estPromoCases dateOfOrder

    1 11232 34 222 300 01/01/2003
    2 11221 21 32 29/06/2003
    3 20032 44 56 29/06/2003
    4 50000 54 24 50 01/01/2003
    5 65444 76 121 01/01/2003

    I enter a line of information when I know that a product bought by a customer is on promotion (estPromocases) on a particular day, this is estimated by the customer and filled in a few weeks in advance(2, 3). Then when the date has passed and I know how many cases were actually sold on that day, I need the casesSold column to be filled by being updated from the AM Sales table(1, 4). For any orders where the product is not on promotion I need all of the details to be added to the table (except estPromoCases as not on promo), this is done on the day of sale (5).

    Hope Iíve explained this properly, how can this be done?

    Please help.

  2. #2
    SitePoint Addict
    Join Date
    Mar 2002
    0 Post(s)
    0 Thread(s)
    I think you need to JOIN your tables.

    All4YourPC for all your PC needs. Custom software.
    Websites. Web marketing. Training.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts