×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Save queries to a table.

Save queries to a table.

Save queries to a table.

(OP)
I created a form from a query pulling fields from several different tables. Now I want to save this data to a table. I've tried several different methods with no luck. Any suggestions?

RE: Save queries to a table.

Not any "pretty" ones.

You need a table to place the info into.  
Create it.  
Bind the form controls to the new table.  
Populate the controls from the query.

Of course, this last one is the hard (Not Pretty) part.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

RE: Save queries to a table.

(OP)
Thank you for your response.
I don't know how to "populate the controls" from the query
I created. Any ideas would be much appreciated. I'm stuck.

RE: Save queries to a table.

The population of the fields is (very approximatly):

In the appropiate event for the form:

Me!CtrlName_X = rst!Field name for the control

for each control which is on the form, where the items in italics are replaced by the appropiate names from your form and recordse (query).  Since the Form is/will be bound to the 'target' recordset, you will need to move the form to the 'empty' record used to add records, then position the query to the record you want to add to the table, populate each of tte controls witht the value from the query!field, save the and record.

It is not at all clear to me WHY you need to save the query results to a new table or why you need to use a FORM to do it.  BUT there may be a much easier process to accomplish your goal.

If, for instance, you are simply replacing the several tables with a single one you could just modify your query to be an append query.  Executing this would simply copy hte set of records from the several tables to the one.

If is some selection criteria - which can be included in the soiurce query, the same applies.  Incorporate the necessary where clause(s) directly in the source query and change it to an append query and run/execute it.

You can include (almost?) anything in the source query that you would do via the form (the exception is some manual data entry which is random/unpredictable) and just do the append query.  If you are doing something which requires the "Human Intervention", you could STILL do an append query for your multitable source, leaving out the info which requires the human, and then build your form to use the newly created table.  Then you could just page through hte recordset using the Form to edit the information.

In general, if the information form the multiple tables does not create the complete table you want, you should probably set up another table which does hold the information and include this in the source query.  Any 'real' recordset will have to many records for a human to reliably enter the information.

On a completly different tack, the trend in database systems is to 'normalize' data structures - while what you are doing is generally 'de-normalizing'.  So, one would ask WHY do you want to collapse the 'normalized' data into the single table?  What is the point of saving the data into a "table" when it (appears to be) readily available through the query?

Just use the query you have created whereever you would use this new table you are creating?



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

RE: Save queries to a table.

(OP)
Maybe I should give more info on what I'm trying to do.
I have 3 tables- Badgeprofile, FMPSCode, and Prepay.

Badge profile contains: BadgeID, FirstName, LastName, FMPSCOde, and FMPSDescription.

FMPSCode table contains: FMPSCode,FMPSDescription, BreakfastCost, and LunchCost.

Prepay table contains: BadgeID, DateofPrepayment, PrepaymentAmount, and Balance.

The form I want to create is for the user to record meal transactions. When the user enters a BadgeID, the FMPSCode,FMPSDescription, the balance for that BadgeID and the cost of the meal(breakfast or lunch) which it gets from the FMPSCode table, is autofilled. I also built an expression to subtract the cost of the meal from the Balance.
All this I would like to append to a table I call DailyActivity.

I hope this helps give a better idea of what I'm trying to do.

RE: Save queries to a table.

At least more complete.  I still don't see the reason to record EVERYTHING.

Going back to BASICS

Only save the "Unique Information"  Here, it would appear to me to only require the BadgeID, Date/Time Stamp and Cost to be saved.  All else may (easily?) be re-constructed from the other tables.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

RE: Save queries to a table.

(OP)
The reason I need to record everything is that another application will also be writing to the DailyActivity table
as well as getting values from the other tables.

Thanks for your help.

RE: Save queries to a table.

Then, the question is:

"Who's Database/Table is it?"

If it is yours, them just tell the other party how it is/will be.  Otherwise (discretely) send them this whole thread under the heading/title "Normalizing/Denormalizing the Database".



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

RE: Save queries to a table.

Hi, guys. I am a novice in developing access and doing a project for one of my courses. Its a human computer interaction course and I need to develop a user interface to feed valid queries to an access database supplied to us by our instructor.

I have no idea whatsoever as to where I could start. I think I can do the queries by themselves in access but the part of linking a query to a form is troubling me. I wish somebody could give me an hint for the same.

In case i am not clear in my description above please let me know.

satya33@hotmail.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close