APPEND QUERY RESULTS TO A TABLE IN ACCESS 2010

I have a select query run based on a field input using [] in the criteria. The results are then displayed in a form. I want to append the values displayed in the form to another table via a command. Please help.
thao-nhiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
You would use an append query:

Insert into [DestinationTableName] (Field1, Field2, Field3, ..., FieldN)
SELECT Field1, Field2, Field3, ..., FieldN
FROM yourQuery

When you use this technique, the field sequence must align and the data types in the fields in your query must match the data types of the fields in your destination table.  If you are using the NZ( ) function in your query, you should probably wrap that reference in a data conversion function (cInt, cLng, cDbl, cDate, ...) to explicitly type the resulting value because the NZ( ) returns a string value when run in a query.
Hamed NasrRetired IT ProfessionalCommented:
I go with Dale's comment.

I may add: you may create the table structure the first time specifying field types as in this example an integer, double and text fields and a double field.
     
 DoCmd.RunSQL "Select cint(1) as fint, cdbl(1) as fdbl, 'abc' as fstr  into a_test from a where false "

Open in new window


Or run the SQL as a make table query.
thao-nhiAuthor Commented:
My Select query is like this:

SELECT XXXX.[Part ID], XXXX.Description, XXXX.[Retail Price], XXXX.[Authorized Dealer Price],XXXX.[Stock Availability], XXXX.NOTES
FROM XXXX
WHERE (((XXXX.[Part ID])=[please enter product id]));

INSERT INTO [product inquiry] ( [Part ID], [Retail Price], [Authorized Dealer Price], [Stock Availability] )
SELECT [Component Price Availability].[Part ID], [Component Price Availability].[Retail Price], [Component Price Availability].[Authorized Dealer Price], [Component Price Availability].[Stock Availability]
FROM [Component Price Availability];

When I run the append query as above, it asks me to enter the product id again which I do not want.

The results from the select query are populated onto a form that I have a few additional check boxes, textboxes clicked/filled after the select query was run. What I want to do is to save the data from the form to another table, not only from the select query. Also I want to append the date (not available either on the form or the select query)  the select query was run into the new table.

Here are the steps of what I want to do:
1. Run the select query
2. Populate the query results to a form
3. Edit the form manually (checkboxes, comboboxes ect..)
4. Save all the values from the form to a second table  (already created with appropriate fields to accommodate  values from the form.
5. Insert a date (now) it was done to a field in the 2nd table.

Thanks
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Dale FyeOwner, Developing Solutions LLCCommented:
You need to define your parameter, I prefer to do this in both queries, in order for it to only ask for the parameter once:

Parameters  [please enter product id] long;
SELECT XXXX.[Part ID], XXXX.Description, XXXX.[Retail Price], XXXX.[Authorized Dealer Price],XXXX.[Stock Availability], XXXX.NOTES
FROM XXXX
WHERE (((XXXX.[Part ID])=[please enter product id]));

And assuming that the previous query is saved as: [Component Price Availability]

Parameters  [please enter product id] long;
INSERT INTO [product inquiry] ([Inquiry Date], [Part ID], [Retail Price], [Authorized Dealer Price], [Stock Availability] )
SELECT Date(), [Component Price Availability].[Part ID], [Component Price Availability].[Retail Price], [Component Price Availability].[Authorized Dealer Price], [Component Price Availability].[Stock Availability]
FROM [Component Price Availability];

Or you could make this all into a single query:

Parameters  [please enter product id] long;
INSERT INTO [product inquiry] ([inquiry date], [Part ID], [Retail Price], [Authorized Dealer Price], [Stock Availability] )
SELECT Date(), XXXX.[Part ID], XXXX.[Retail Price], XXXX.[Authorized Dealer Price], XXXX.[Stock Availability]
FROM XXXX
WHERE (XXXX.[Part ID]=[please enter product id]);
thao-nhiAuthor Commented:
when I add PARAMETERS [please enter product id] long ; to the select query, there is a run time error "The value you entered is not valid for the field".
Try to change it to Parameters  [please enter product id] text;
Access automatically changes it to Parameters  [please enter product id] text (255). But it runs.

The append query with the same definition added for the parameter as text still asks for the parameter.

Thank you for the comments but it does not help me with with what I want to achieve: Saving from the form - not directly from the query.
Hamed NasrRetired IT ProfessionalCommented:
Try:
1. From MainForm, create the select SQL.
2. Open the theForm, and in Open event, set the theForm's record source to the SQL.
3. Edit the theForm.
4. DoCmd.RunSQL "Insert into tbl(Field1, Field2) Values (" & Forms![theForm]!Field1 & "," & Forms![theForm]!Field2 & ")"
5. Can be added to step 4.

 Close theForm, and repeat with other field input.

Try to upload a demo database demonstrating the issue.
thao-nhiAuthor Commented:
please see attached
INv---Copy.accdb
Hamed NasrRetired IT ProfessionalCommented:
Try this copy. Add other required fields in the click event of the save to table button.

Select the required Part ID from the combo in form header. The corresponding info is displayed.

DoCmd.RunSQL "INSERT INTO [product inquiry](Brand,[Part ID],[Retail Price]) Values ('" _
       & Me!BRAND & "','" & Me![Part ID] & "','" & Me.[Retail Price] & "')"

Open in new window

Before uploading an access database, try compact and repair to reduce size.
INv---Copy-2.accdb
PatHartmanCommented:
Two separate queries are running and that is what causes the second prompt.  Rather than prompt for the value, have the user enter it in an unbound control on the form.  Then have both queries reference the form field.
thao-nhiAuthor Commented:
We are very close hnasr. Thank you. However, how do I insert into a 2nd table the value of the checkboxes if they are checked?

thanks
Hamed NasrRetired IT ProfessionalCommented:
Try this:

Modified check boxes names. Added relevant fields to product inquiry table.

The code for save to table button rewritten to avoid confusion:
Private Sub Command16_Click()
    DoCmd.RunSQL "INSERT INTO [product inquiry](Brand,[Part ID],[Retail Price], Public, [Authorized Dealer]) " _
    & "Values (" _
    & "'" & Me!BRAND & "'" _
    & "," & "'" & Me![Part ID] & "'" _
    & "," & Me![Retail Price] _
    & "," & Me!Public _
    & "," & Me![Authorized Dealer] _
    & ")"
End Sub

Open in new window

For values include
 "'" & Me!TextField & "'"
 "#" & Me!DateField & "#"
   Me!NumberField
INv---Copy-3.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thao-nhiAuthor Commented:
Thank you
Hamed NasrRetired IT ProfessionalCommented:
Welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.