Access Data Manipulation

[Aaron]

Hello folks,

I currently have a backend db table with data that I need
to manipulate in excel because of formulae and multiple
pivot table looks. Many people are entering data during
the day in the db. Is there a good/easy way to either:

1. Keep the data updated in excel in real time (so I
don't have to manually update)?

2. Export data to excel daily without having to manually
do this?

Also, I'm a newbie to this, but have the developer edition
(because I created runtimes for the other computers)...in
this edition it talks about being able to have different
db's for the same table. I guess what I'd like to do is
work out of a "copy" of my backend DB so I'm not screwing
something up in the backend DB. Any good way to do this?
Thanks so much,
Aaron

 

[Albert D. Kallal]

Hello folks,

I currently have a backend db table with data that I need
to manipulate in excel because of formulae and multiple
pivot table looks. Many people are entering data during
the day in the db. Is there a good/easy way to either:

1. Keep the data updated in excel in real time (so I
don't have to manually update)?

2. Export data to excel daily without having to manually
do this?

I would do neither of the above. I would not export the data daily, but give
the user an option to view/edit the Excel sheet right from the application.
That way, it is not done daily, but is done in real time. You can also use
pivot tables in ms-access anyway, and might even dump the use of Excel. In
fact, really, for most data there is only a few views needed, and supplying
a few reports in ms-access likely would be better then sending the data to
excel. This can result in large increase in ease of use, and less training
for your users to be able look at, or view the data.

If you take a look at the following screen shots they provide the user with
the options they need BEFORE the report is launched. The amount of training
this reduces is incredible, as the application is now very easy to use. My
users don't even have to know excel OR ms-access Check out:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

Also, I'm a newbie to this, but have the developer edition
(because I created runtimes for the other computers)...in
this edition it talks about being able to have different
db's for the same table.

There is no real difference in your application when run as run-time, or a
mde. Using the developer editing makes no difference in this regards. They
are exactly the same. Likely, some mention was made of developing and using
a split database, but that is a standard development approach anyway.

I guess what I'd like to do is
work out of a "copy" of my backend DB so I'm not screwing
something up in the backend DB. Any good way to do this?

Yes, as mentioned, a good sound development practice is to use a split
database arrangement. Check out:

http://www.granite.ab.ca/access/splitapp.htm

http://www.microsoft.com/accessdev/articles/bapp97/chapters/ba15_3.htm

 

[Guest]

HI and thanks much,

The reason I don't believe that I can dump excel is
because I need to perform some v-lookups for calculating
$$ based on which leather name is in the cell and based on
how much leather is used. I do like how access works with
time and dates in the pivot tables (by week, month, year,
minute, etc). I had to create use all the special date
formats in excel in extra columns to analyze info by
certain time frames. Does excel do a better job of that
now?

thanks for your help,
Aaron

 

[Albert D. Kallal]

HI and thanks much,

The reason I don't believe that I can dump excel is
because I need to perform some v-lookups for calculating
$$ based on which leather name is in the cell and based on
how much leather is used.

There is little, or no reason why you can't accomplish that lookup to a
price table in ms-access. In fact, ms-access is far better then excel at
looking up data from another table.

Does excel do a better job of that
now?

Gee, I really don't know that!

For sure, Excel is slick and really a nice rapid tool to mix, and munch and
crunch some data. However, for sorting, grouping and reporting on
totals...ms-access tends to be a lot better. Further, once you get the
reports done and setup, then they can be run/used by people with much less
training then what is needed with Excel. You can provide buttons to ussers
to launch/view the reports...at lot easer then using excel.