Make Table Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any way to prompt the user for a new table name in a make table
query? I want to be able to save the table data each time the query is run
and not have the user have to go back and rename the table prior to running
the new query.

Thanks!
 
Dear Karen:

Nope!

May I suggest you place all the new rows in the same table instead. Use one
column to differentiate between the various sessions. The data can be
divided within the table this way, and the user can be prompted for the
value of this column. This would not then be a make table query, but an
append query to an existing table. You can subsequently separate just those
rows added during a specific session from all the other rows.

Tom Ellison
 
Is there any way to prompt the user for a new table name in a make table
query? I want to be able to save the table data each time the query is run
and not have the user have to go back and rename the table prior to running
the new query.

Thanks!
Click to expand...

I just want to agree with Tom. Your approach would cause your database
to explode with badly-named tables, all of identical structure;
storing data in a tablename is NEVER a good idea, and it will be hard
for the user to find the query ten minutes later, not to mention ten
days.

Given that almost anything - a Report, a Form, another Query, an
Export - can be based on a select query, I don't think there's any
justification for storing the data redundantly in a table; just rerun
the query as needed.

If I'm misunderstanding the circumstances, please explain why this is
needed.

John W. Vinson[MVP]
 
Here's what I'm trying to do. I have an Excel spreadsheet containing items,
pricing, quantities, etc. The Access database is linked to the spreadsheet.
The user enters in quantities for items then creates three different forms in
Access based on the linked spreadsheet where quantity is greater than zero.
Which this works fine. The only problem I see is if the a form needs to be
changed and the spreadsheet has been updated with new quantities for a new
form. All quantities would have to re-entered to recreate the first set of
forms. We could save the excel spreadsheet each time, but would then have to
copy that info back over the sheet that's linked to Access. User is not
familiar at all with Access and I want to keep it as easy as possible.

Thanks!

Karen
 
This would work. I replied to John with more detail as to what I'm trying to
accomplish. The user would have to enter in the unique column identifier
(order number for example) on all rows of the spreadsheet, at least copy it
to all rows after entering. I will check this out. Thanks!
 
Here's what I'm trying to do. I have an Excel spreadsheet containing items,
pricing, quantities, etc. The Access database is linked to the spreadsheet.
The user enters in quantities for items then creates three different forms in
Access based on the linked spreadsheet where quantity is greater than zero.
Click to expand...

Creates *THREE NEW FORMS*? Surely not! A Form is just a window on a
table or query: you don't need a new *form* when you have new data!
Which this works fine. The only problem I see is if the a form needs to be
changed and the spreadsheet has been updated with new quantities for a new
form. All quantities would have to re-entered to recreate the first set of
forms. We could save the excel spreadsheet each time, but would then have to
copy that info back over the sheet that's linked to Access. User is not
familiar at all with Access and I want to keep it as easy as possible.
Click to expand...

Perhaps you could explain how you are using Access Forms in this
context - or how you are using the term "form" in a non-Access sense.
Is this "form" a sheet of paper? or some data viewed on a screen?

If it's a sheet of paper, what you need is an Access Report - which
should be based on the "live" Excel spreadsheet. I don't see why you
would want to have two spreadsheets, one being updated and the other
needing to be reentered; can you not just link to the real
spreadsheet?

John W. Vinson[MVP]
 
It's three Access "reports" that are being filled in. The real spreadsheet
is linked. In talking with the user, the reports don't change that often
once created. It would be just as easy to re-enter the quantity amounts in
the spreadsheet and re-create the whole report. Putting the forms, aka
reports, into Access is saving so much time, re-entering quantity amounts is
nothing. Thanks for your help! Sorry for any confusion.
 
Back
Top