MS Access / Excell Gurus

[terbow]

Anyone with alot or more than intermediate experience? I have some things i need help with and i cant figure it out.

 

[jersey_emt]

Details?

 

[SuperStretch18]

Ask away...

 

[terbow]

okie.

i have right now excel sheets that calculate some formulas. Basically it has the customer name, acct number, amt financed, etc etc. the forumlas calculate due date, interest , principal,balance and next due. i have one sheet per person (all seperate files). we need to make reports from the data. for example, every 30 days (once a month) i need to run a report on interest collected, balances left, people that are late etc etc.

so i wanted to use access cause of its awesome db features but i cant seem to just cut and paste excel data over to the access db. i made all these nice queries and reports but i cant get the data to copy over, it jsut goes to one cell. im not sure what the best way to approach this system is. i dont think access can do formulas like excel can in a sheet so i think i have to leave it to excell?

if this doesnt make sense i can show u the excel sheet tommorow and explain it a little better.

 

[SuperStretch18]

Access will only paste the raw data. Example:

=B2*C2
(answer equals 4),
access will paste in "4"

You should be able to paste in all of the data you need though. Trick is to highlight all of the columns/rows that you want to paste into. Otherwise, it just assumes that you only want to paste a single cell.

A quick questions that hopefully lead to your answer:
- did you set-up a table in the same format as the information you are trying to paste?

If not, go to Create table in design view and make a line item for each column of data that you want to paste. Make sure that they are formatted correctly to accept the data that you are trying to put in (i.e. text, number, date, etc.; when in doubt, set it as text, and you can change it later).

Access cannot calculate within a table. Tables are for raw data, queries are for calculations and formulas.

Sorry if this is all too basic; not sure what skill level you are, but let me know if this helps.

 

[terbow]

i will try that. i would say im beginner, i can fiddle my way around (i figured out how to do my reports and queries), i just need a way to get the data in there.

there is one formula i will need help on but ill get back to that. thanks.

 

[fkmp5]

so i wanted to use access cause of its awesome db features

this is a joke, right?

okay, sorry to bust up your thread with my nonsense.. continue on and good luck, i am certainly no excel guru..

 

[illogik]

Try this in Access:
File-> Get External Data -> Import -> File type=.xls

 

[terbow]

well the thing is the data constantly changes, i basically need to copy the data over each time they make a payment to reflect the current balance, principal, interest, due date etc

 

[illogik]

I'm no Access expert but have you tried linking the table? (right click in the Tables workspace and choose "link tables")

 

[Mr Magnum]

Dude this all chinese to me.......Goodluck w/ it

and I took a excel course and got a "b" in college.......