I have feeling you all going to be laughing and feeling sorry for me in just
a minute!
We have an Excel workbook with 49 worksheets in it. Each sheet holds all the
information our supervisor needs to know about each cycle we verify. Each
workbook contains the worksheets for the MONTH. There will be 12 workbooks by
the end of the year.
So far, i have the db set up as a "tool". She can hit a button and all 49
sheets pull in, append to a table, exports to excel, then it is all cleared
out upon exit to do it again for the next month.
This morning she told me she wanted the monthly data to be appended to a
table in the database (named as the current year). If I can get it to prompt
her when she does this, it will ask her each month what table she wants to
append to. Her choices would be the tables already in there or "create new"
and she can name the new on 2010 and it will then save the 2009 table as is
and copy the 2009 (minus data) as 2010.
I am just beside myself with this request. Can it even be done?
The tabs I pull in have fields: CYCLE, MONTH, YEAR, TASK, ASIGNEE, VALUE,
HOURS, MIN, TOTAL. The total field is a calculation done within Excel. I
wrote an enormous union query to UNION ALL every one of the 49 linked tables:
SELECT Checklist.CYCLE, Checklist.MONTH, Checklist.YEAR, Checklist.TASK,
Checklist.ASSIGNEE, Checklist.VALUE, Checklist.HOURS, Checklist.MIN,
Checklist.TOTAL
FROM Checklist
UNION ALL
SELECT Checklist1.CYCLE, Checklist1.MONTH, Checklist1.YEAR, Checklist1.TASK,
Checklist1.ASSIGNEE, Checklist1.VALUE, Checklist1.HOURS, Checklist1.MIN,
Checklist1.TOTAL
FROM Checklist1
UNION ALL
etc...
Then I have the append table. But that is just not good enough. It has to
function and store...
Can you help??
Jeff Boyce said:
If you have a table named [2009], you have a spreadsheet, not a relational
database.
In a well-normalized relational database, you wouldn't embed date (e.g.,
"year") in a table name. Doing so implies that you'd need to have a
multitude of identical tables with different names. This creates a
maintenance nightmare, and ... oh wait, this sounds like your situation?!<g>
If you used a single table and added a date/time field, you could store a
date(/time), and use a query to find all of the records that fell in the
year 2009!
If you'll provide more detailed description of what you are working with,
folks here may be able to offer more detailed suggestions for how you can
get better use of Access' relationally-oriented features/functions.
.... or you could use a spreadsheet <G!>
Regards
Jeff Boyce
Microsoft Office/Access MVP
KC_Cheer_Coach said:
I am using Access 2003.
Is there a way to run an append query where it will prompt the user to
choose the table they want to append to OR create a new table? For
instance,
I have a table called 2009. When my supervisor runs her reporting, she
needs
to hit a button to append it to the 2009 table that is already out there
or
create a new table to append to.
Let me know if you need further information.
Thanks!!
