Using a form to create an Append query?

[Access_Novice]

Is it possible to do the following:

1. Use a form to do a SELECT query and display the results in the form
2. Take those SELECT query results in the form and add a field with a value, then turn all of that into an APPEND query so that a table can be appended.


Is this possible?

[June7]

Item 1 is easy don't really understand what you are attempting with item 2.

Why would you need to copy all that data to another table? That is duplication of data, not something desired in a relational database.

[Access_Novice]

Is it possible to do the following:

1. Use a form to do a SELECT query and display the results in the form
2. Take those SELECT query results in the form and add a field with a value, then turn all of that into an APPEND query so that a table can be appended.


Is this possible?

Here is what I had in mind. One table called Roster with a list of Customers. Another table called Membership. Query the Roster table to get customer names, then add a column with the type of membership they are purchasing. Then take all that data, and append it to the Membership table. When the memberships expire, repeat the process and query the Roster table. Then add a column with the new membership they are purchasing. Then append that to the Membership table.

That is what I had in mind. Is there a better way to do that?

[June7]

Can't 'create' a field in query that allows data entry/edit. Where will the type of membership for each customer come from?

What you can do is use query to create records in the membership table then go fill in the membership type for each.

[Access_Novice]

Can't 'create' a field in query that allows data entry/edit. Where will the type of membership for each customer come from?

What you can do is use query to create records in the membership table then go fill in the membership type for each.

That might be the best solution. Thank you.

[KathyL]

“Can't 'create' a field in query that allows data entry/edit.”

June, yes you can, I do it all the time. Any form can be based on a query. However, I think you were trying to say you can’t modify the query design to create a field, on the fly.


“Here is what I had in mind. One table called Roster with a list of Customers. Another table called Membership. Query the Roster table to get customer names, then add a column with the type of membership they are purchasing. Then take all that data, and append it to the Membership table. When the memberships expire, repeat the process and query the Roster table. Then add a column with the new membership they are purchasing. Then append that to the Membership table.
That is what I had in mind. Is there a better way to do that?”

Yes, you can have a query where the selection criteria gets a list of Customers based on membership expiration dates. And then this same query can be an append query to add this subset of Customer records to a different table. And that different table can already be predefined with a field/column for ‘Membership type’. In your append query, you have a calculated field like “Membershiptype:’January Registration’”, and this field you append into your other tables ‘Membership type’. This calculated field is just an expression to set a default text value which you can append. And you can get more complicated, by having this calculated field pull a value from a form, I do this all the time. E.G. “Membershiptype: [Forms]![MainForm]![MembershipType]

[June7]

Guess I didn't give the best wording in my statement or I didn't understand what OP described.

Yes, could use the current membership type to create records with membership already filled in. Then would have to edit records for those who are choosing a different membership. And this is what I was trying say, couldn't make those edits 'on-the-fly' in the update action.