compare dates in a report

[Guest]

I am trying to create a report that compares year 2005 information with year
2006 information directly above each other. Here is a very small exampleof
the format I want to accomplish with similar data-->

Event Money Generated
January
Wk 1 2005 Regular Sale $400
2006 Super Sale $300

January
Wk 2 2005 Extra Sale $600
2006 Extra Sale $650

February
Wk 1 2005 Super Sale $900
2006 Gift Card Promotion $400


Total 2005 $1700
2006 $1350

My first issue is that I dont know how to format the report to have 2005 and
2006 above each other. Everytime I try all of 2005 information is grouped
together with all of January 2005 data then all of February 2005 data then
all March 2005 data etc., and all of 2006 information is grouped underneath
it in the same way.


All of the information, the dates, events, sales, money generated, etc, is
on one table only.

I have no idea how to format any of this. I havent even tried to do the
calculations or even format it because I don't know how to do it.

I need any help, or information on how to get help.

 

[Ron2006]

To do the group by with the breakdown you are looking for you will
need:

1 column with just the Year of the dates you are grouping by
Year([tablename].[yourdatefield])

1 column with just the Month of the datees you are grouping by
month(([tablename].[yourdatefield])

1 column with the data that you are saying is week1 and week 2 (maybe
it is
datepart("ww",[tablename].[yourdatefield])

The the group by on
Month, week, event, year and have then in that sequence and sorted
ascending.


Getting a total by Year may be tricky since it is not the hightest
level of the grouping.

 

[Ron2006]

If it is a true report, then a sumif may work on a group footer.

 

[Larry Daugherty]

Hi Karen,

First, the bad If all of your data is in one table then you
need to bone up on Relational Database fundamentals. It sounds like
what you have is very much like an Excel spreadsheet. The user
interfaces of Access and Excel are superficially similar. The two
products are not the same at all.

In Access (an RDBMS - Relational Database Management System) proper
table design requires that only kind of entity be represented in a
single table. Further, every entity of that type in your database
belongs in that table. It is up to you to analyze your application
and to identify the entities in play. An entity can be a real world
object, an event, an idea, a person, etc.

Once the entities are defined then you define the relationships
between them. The most common relationship by far is the
"one-to-many" relationship. One automobile has many doors. One
company has many people as employees.

In the above examples tblAutomobile would be the one to the tblDoor's
many. tblCompany would be the one to tblPeople's many.

You won't be ready to do much fine work on the Reports until your data
is better organize into separate tables and is also normalized.

The Good There is a lot of help available in the form of
books - try Barnes & Noble, Amazon or Borders to start for books on
Access. If any of the major bookstores are local to you, go to their
computer sections and browse the likely candidates there. There are
even some free tutorials online that you can try. Do a google search
on "Access tutorial" and you'll get some hits. Books are usually best
unless you get sample download applications with the tutorials you
try.

Keep lurking this newsgroup. See what others post. When you've
learned a great deal the answers given will make sense to you.
Another good group for people getting started is
microsoft.public.access.tablesdesign In tablesdesign you might post
for help with your data design by telling the folks there what it is
that you are doing (the whole application, not just the report), how
your data is organized. People will come back with questions and
advice.

HTH