Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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
    >
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page