Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Update query to add information to another table

Discussion in 'Information Technology' started by John In Palmer, Jul 28, 2006.

  1. John In Palmer

    John In Palmer
    Expand Collapse
    Guest

    Hi all,
    I have 2 linked tables that I am pulling information from; PRODUCT and USER.
    I have created a query that pulls the user information from the USER table,
    and the product information that the user has previously used is displayed in
    a subForm using a subQuery. I would like to be able to create an update query
    that can be added to the subForm that creates a new record in a different
    table that adds the user, product info and date/time the record was added.
    Any suggestions would be helpful. Thank you in advance.
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    This is not a direct answer to your question, but.
    It seems to me your Db is not properly normalized
    You should normaly have three tables in the first Place

    USER
    ------
    UserID
    UserName
    ...

    PRODUCT
    ------------
    ProductID
    ProductName
    .....

    USERPRODUCT
    ------------------
    UserProductID
    UserID
    ProductID

    If a Product is only Used Once for a User the Date Field could then be added
    to USERPRODUCT else you would need an additional Table USERPRODUCTLOG
    to store that info

    USERPRODUCTLOG
    -----------------------
    UserProductLogID
    UserProductID
    UsedDate

    You can offcourse use the unique Keys From User & Product (UserName,
    ProductName) instead of using an Autonumber, but linking is faster with
    numbers (long ints)
    & it may be easier to Add the UserName, ProductName combination to
    USERPRODUCT to enable cleanup of PRODUCT

    Pieter


    "John In Palmer" <JohnInPalmer@discussions.microsoft.com> wrote in message
    news:667FB3D3-3DA7-4E4C-90B6-7365483F1BDC@microsoft.com...
    > Hi all,
    > I have 2 linked tables that I am pulling information from; PRODUCT and
    > USER.
    > I have created a query that pulls the user information from the USER
    > table,
    > and the product information that the user has previously used is displayed
    > in
    > a subForm using a subQuery. I would like to be able to create an update
    > query
    > that can be added to the subForm that creates a new record in a different
    > table that adds the user, product info and date/time the record was added.
    > Any suggestions would be helpful. Thank you in advance.




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  4. John In Palmer

    John In Palmer
    Expand Collapse
    Guest

    Sorry, but that is the structure; USER table, PRODUCT table and finally
    UPDATE table (not USERPRODUCT). Basically, I would query a user that would
    pull up a list of products that this person has used in the past. From here,
    I would need to build an update query to add to the UPDATE table the user
    name, product information (nomenclature, description), and date/time product
    was checked in/out. I hope this makes more sense - I really appreciate your
    answer though, it made me realize I wasn't asking the correct question!
    :eek:)
    Thank you in advance - John

    "Pieter Wijnen" wrote:

    > This is not a direct answer to your question, but.
    > It seems to me your Db is not properly normalized
    > You should normaly have three tables in the first Place
    >
    > USER
    > ------
    > UserID
    > UserName
    > ...
    >
    > PRODUCT
    > ------------
    > ProductID
    > ProductName
    > .....
    >
    > USERPRODUCT
    > ------------------
    > UserProductID
    > UserID
    > ProductID
    >
    > If a Product is only Used Once for a User the Date Field could then be added
    > to USERPRODUCT else you would need an additional Table USERPRODUCTLOG
    > to store that info
    >
    > USERPRODUCTLOG
    > -----------------------
    > UserProductLogID
    > UserProductID
    > UsedDate
    >
    > You can offcourse use the unique Keys From User & Product (UserName,
    > ProductName) instead of using an Autonumber, but linking is faster with
    > numbers (long ints)
    > & it may be easier to Add the UserName, ProductName combination to
    > USERPRODUCT to enable cleanup of PRODUCT
    >
    > Pieter
    >
    >
    > "John In Palmer" <JohnInPalmer@discussions.microsoft.com> wrote in message
    > news:667FB3D3-3DA7-4E4C-90B6-7365483F1BDC@microsoft.com...
    > > Hi all,
    > > I have 2 linked tables that I am pulling information from; PRODUCT and
    > > USER.
    > > I have created a query that pulls the user information from the USER
    > > table,
    > > and the product information that the user has previously used is displayed
    > > in
    > > a subForm using a subQuery. I would like to be able to create an update
    > > query
    > > that can be added to the subForm that creates a new record in a different
    > > table that adds the user, product info and date/time the record was added.
    > > Any suggestions would be helpful. Thank you in advance.

    >
    >
    >
    > --
    > ----------------------------------------
    > I am using the free version of SPAMfighter for private users.
    > It has removed 4367 spam emails to date.
    > Paying users do not have this message in their emails.
    > Get the free SPAMfighter here: http://www.spamfighter.com/len
    >
    >
    >
     

Share This Page