Append individual records from a form depending on current record

[djgeverson]

Hi there,

I want to have two tables in my database. One table will be a list of all raw materials used (called materials), and the other will be a list of all the raw materials which have been approved (called approved). Both tables have exactly the some column titles and data types (a copy and paste of the materials tabled, then renamed).

I have a form which contains a section for approval information to be added and then on the click on a button I want an append query to run which only copies the current record the user is viewing.

I have looked all over forums and dont understand some of the suggestions I have found regarding the INSERT, SELECT, FROM, WHERE terms. Do these terms get added to the criteria box of the query or elsewhere!

Can someone help, I am tearing my hair out over this!!!

Thank you! David

[Seth Schrock]

Here is a link to a website that explains the basics of SQL code including SELECT and INSERT queries along with the FROM and WHERE clauses: www.w3schools.com/sql/default.asp. It is real easy to follow.

If you are just looking to copy the data from one table to another, then your SQL code would be

INSERT Approved
SELECT * FROM Materials

If you only want certain rows to be copied over, then you need to add the WHERE clause at the end.

[zmbd]

Both tables have exactly the same column titles and data types (a copy and paste of the materials tabled, then renamed).

and this breaks the rules of normalization [*]> Database Normalization and Table Structures.

I would be better to simply have one table with a column that indicated the current status... I have a database that handles it this way (I've simplified it considerably here):

tbl_approval_states
[approval_states_pk] numeric(long)
[approval_states_text]text(15)
Starting with 1 for the first record
Approved, Pending, Unapproved

tbl_chemicals_supplier
[chemicals_pk] autonumber
[chemicals_fk_chemical] numeric(long) 1:m with chemical table
[chemicals_fk_supplier] numeric(long) 1:m with supplier table
[chemicals_fk_approval_states]numeric(long) 1:m with approval_states table

Now I use a form on tbl_chemicals_supplier with combo-boxes for the foreign-key fields with the row-source set to show the human-readable text

You can see from there how easy it is to write queries so that I can pull a list of approved suppliers or all available suppliers for a given chemical or I can pull all unapproved, pending, or approved chemical suppliers for every chemical etc...