Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Save queries to a table.
- Thread starter jeez
- Start date
MichaelRed
Programmer
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
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
- Thread starter
- #3
MichaelRed
Programmer
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
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
- Thread starter
- #5
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.
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.
MichaelRed
Programmer
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
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
- Thread starter
- #7
MichaelRed
Programmer
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
"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
Guest_imported
New member
- Jan 1, 1970
- 0
- 0
- 0
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
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
- Status
Similar threads
- Locked
- Question