Need help writing report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK...I have a report in Excel that management likes...I've moved the data into Access and can make queries, forms, and some reports out of it so that it can be removed from Excel. There is still one (1) report that I have to be able to duplicate before we can quit doing entry in Excel. Is there somewhere I can post the Excel report file and the Access report (or at least pictures of them) so that people can see the differences and can help me figure out what to change in Access to make a match? Part of the problem I know are the month (Jan, Feb, etc.) column headings. I can't group by just the headings, only each individual month. Any help on this would be appreciated, as it would let us eliminate some very annoying double-data entry.

Thanks!
 
Rawley

First off, I understand your pain..

I'm not sure if I understand your problem completely, but I'll give it my best shot

I'm assuming that you have an Excel file like this

Name Jan Feb Mar Ap
******* **** **** **** ***
Joe 18.5 12.3 52.3 8.
Sam 21.5 86.5 45.2 1.
Kay 50.5 6.5 37.5 4.

And you want to create an Access Report similar to this. If I'm wrong, just ignore the rest of the reply

Still with me? Good

Create an Access Table that looks like this

Name Month Valu
***** ***** ****
Joe Jan 18.
Joe Feb 12.
Joe Mar 52.
Joe Apr 8.
Sam Jan 21.
etc..

Now, what you then have to do is make a cross-tab query. Microsoft does a pretty good job of explaining the whole process, so I won't bother. A cross-tab query will give you the ability to make the report that you need, but without the million columns

I hope I've helped a little,

Jim
 
Rawley

Here is what I would suggest

Make a query like this
SELECT [State/City/Goal],Month([date]) As Month,[Carrier] & Space(5) & [Cost] As Data FROM ..

Then, do a cross-tab query wizard.
- Make [State/City/Goal] your Row Headings.
- Make [Month] your Column Heading
- Make [Cost] your Calculation (Var() as your function

Because you can only have one field as your calculation, the query combines any fields you want to show in your cross-tab

If all works out correctly, you should get what you want

Hope this helps

Jim
 
Back
Top