Update query to add information to another table

[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.

 

[Pieter Wijnen]

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

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.

--

 

[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!
)
Thank you in advance - John