By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,934 Members | 1,441 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,934 IT Pros & Developers. It's quick & easy.

Append individual records from a form depending on current record

P: 10
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
Mar 18 '14 #1
Share this Question
Share on Google+
2 Replies

Seth Schrock
Expert 2.5K+
P: 2,951
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: 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
Expand|Select|Wrap|Line Numbers
  1. INSERT Approved
  2. SELECT * FROM Materials
If you only want certain rows to be copied over, then you need to add the WHERE clause at the end.
Mar 18 '14 #2

Expert Mod 5K+
P: 5,397
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):

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

[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...
Mar 19 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.