How do I change an Access Database to do the same thing but with another imported file?

[SueB]

I have a couple tables, about 30 queries, and a couple reports. I built this
using an imported Excel file as the input. Now I need to create my reports
using about 6 months worth of different input files. Ideally, I'd just open
my database and somehow say - do all this same stuff but using this file as
input. How can I make that work? Please tell me I can make it work. Please
please tell me it'll work......

 

[Tony]

Sue,

Are your new input files that say structure (i.e. same
amount of fields and same field names) as your current
tables? If so, I would import the new data and append it
to the existing tables.

The key in this is that the data set has to match
identically for the append to work, or, you will need to
massage the new data input so that it does match your
existing tables.

Does this make sense?

Tony

 

[SueB]

It's exactly the same. But - I'd then have to add a date column and somehow
do it by date. What I'm doing now is deleting the old data and copying the
other days' data into the original tables. Is there an easier way?
Thanks

 

[Tony]

I think the best way to handle this would be to add a date
field to your table(s). That is, if you would like to
report this historically. Before importing more data, I
would add your date field to the table, run an update
query that assigns the date you want to assign for that
data, then import your new data and apply new dates that
that particular data. Are you familiar with running
parameter queries (i.e. between [Start Date] and [End
Date]?

If not, you can create a procedure (i.e. Macro & query)
that will delete the existing data within your tables and
another procdure to import your Excel files.

 

[SueB]

The Macro & query thing sounds good. Anybody have somethign like this?

I think the best way to handle this would be to add a date
field to your table(s). That is, if you would like to
report this historically. Before importing more data, I
would add your date field to the table, run an update
query that assigns the date you want to assign for that
data, then import your new data and apply new dates that
that particular data. Are you familiar with running
parameter queries (i.e. between [Start Date] and [End
Date]?

If not, you can create a procedure (i.e. Macro & query)
that will delete the existing data within your tables and
another procdure to import your Excel files.

-----Original Message-----
It's exactly the same. But - I'd then have to add a date column and somehow
do it by date. What I'm doing now is deleting the old data and copying the
other days' data into the original tables. Is there an easier way?
Thanks




.

 

[Tony]

Are you familiar with queries and macros?

Step 1 - Create a delete query that includes the table
with the data you want to delete.

Step 2 - Create a macro that uses the following:

Action:
TransferSpreadsheet

Transfer Type = Import
Spreadsheet Type = Microsoft Excel 8-10
Table Name = YourTableName
File Name = C:\FileLocation\YourSpreadsheet.xls
Has Field Name = Yes

Does this make sense?

-----Original Message-----
The Macro & query thing sounds good. Anybody have somethign like this?

I think the best way to handle this would be to add a date
field to your table(s). That is, if you would like to
report this historically. Before importing more data, I
would add your date field to the table, run an update
query that assigns the date you want to assign for that
data, then import your new data and apply new dates that
that particular data. Are you familiar with running
parameter queries (i.e. between [Start Date] and [End
Date]?

If not, you can create a procedure (i.e. Macro & query)
that will delete the existing data within your tables and
another procdure to import your Excel files.

-----Original Message-----
It's exactly the same. But - I'd then have to add a

date
column and somehow

do it by date. What I'm doing now is deleting the old data and copying the
other days' data into the original tables. Is there an easier way?
Thanks

Sue,

Are your new input files that say structure (i.e. same
amount of fields and same field names) as your current
tables? If so, I would import the new data and

append
it

to the existing tables.

The key in this is that the data set has to match
identically for the append to work, or, you will need to
massage the new data input so that it does match your
existing tables.

Does this make sense?

Tony



-----Original Message-----
I have a couple tables, about 30 queries, and a couple
reports. I built this
using an imported Excel file as the input. Now I

need
to

create my reports
using about 6 months worth of different input files.
Ideally, I'd just open
my database and somehow say - do all this same stuff but
using this file as
input. How can I make that work? Please tell me I can
make it work. Please
please tell me it'll work......


.



.

.

 

[John Vinson]

I have a couple tables, about 30 queries, and a couple reports. I built this
using an imported Excel file as the input. Now I need to create my reports
using about 6 months worth of different input files. Ideally, I'd just open
my database and somehow say - do all this same stuff but using this file as
input. How can I make that work? Please tell me I can make it work. Please
please tell me it'll work......

You should be able to do this; I'm not certain of the structure of
your database, but if you have six months' worth of spreadsheets, you
should be able to use File... Get External Data... Link to link to
each of them in turn. This linked "table" can then be used as the
source for your reports, or (probably better) added to a larger table
by running Append queries.