Newbie in need of some basic help.

[Stiz]

Hi all, I have 12 Excel spreadsheets each one a month in Month To Date income. They share identical column headers and the first column is the name of the different clients, so this is also the same in all 12 spreadsheets.

My boss needs me to generate some charts showing the change of certain values from month to month.

I cant seem to figure out how to do this, I import all 12 spreadsheets and turn them into 12 tables, but from there I dont know what to do.

Any help is greatly appreciated.
Thank You

 

[lblastick]

Create a blank Access database.
Right click and choose Import.
Change the file extension to .xls for excel.
The Import wizard should appear and walk you through the rest of the way.

 

[Stiz]

Thanks for the response Iblastic, I have no trouble importing the tables, but I do not know how to make a report that compares the data from those 12 different tables.

Say if column #5 is amount owed, and row #7 is the customer John. How can I make a graph showing how much John owe's along the course of the year (each table is one month).

 

[Garridon]

Are you looking for some kind of graphical represenation, such as a bar chart? If so, you might not need to do it from Access--you can do it in Excel.

Highlight the data you want to be in the chart and click the Chart Wizard button. The wizard will guide you through the rest. Linda Adams
Visit my web site for writing and Microsoft Word tips:

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[Stiz]

yes, well I guess the reason why I'm trying Access is that I will need to run some sort of query to grab the data out of the mutiple tables, then take the data from the query and chart it.

So I guess my real question is how do I create that query.

Basically I need one number from each table, say its what customer number 123 owes. Each row is a customer, defined by the customer number in the first column. How do I run a query to get the cell in rowcust_num)"123" Column:"Owed".

I dont see anything in the query wizard that allowes multiple tables to be queried at once. Secondly I cant find how to refernce a row according to the value in the first column (cust_num), with a specified colum (owed).

Thanks again for any help

 

[67peshawar294]

Hi Stiz,
Try importing all of the spreadsheets to one table.
then create your query, including all fields needed to obtain the information.

In the criteria section of the "customer name": type"
Like [Enter Customer Name]
When you run the query a "Enter Parameter Value" box will appear. Enter the customer name, all of the data from the twelve tables will be listed.
Then create your report using the name of the query for the record source.
Jim

 

[Stiz]

Almost there.

Allright so I import all the sheets into 1 table, now they are stacked verticaly, so when I run a customer specfic query (using criteria to specfiy the customer) I get a verticle stack of the customer 12 times.

this verticle alignment of the changing values (colum next to the repeating customer name) doesnt seem to jive with the charts.

How dod I resolve this issue?

Many thanks for all your help.

 

[MoGryph]

Ouch- importing all the sheets into one table 'might' end up giving you more of a headache than you want. I could be the solution, but it might hurt more than help.

After importing all the sheets into seperate tables, did you try setting up the proper field relationships between the tables, under "Tools... Relationships"? After doing so, the Access Report Wizard can help you out alot more- it can make it's own inferences about how to display, key, and sub-report your data in each table, by the way it relates to each other.

Or maybe I misunderstood the question.