RE: Create report with three rows of data representing years

The months should generate column headings so sorting should not be an issue.
Add every month name to the Column Headings property to ensure you get all
the months generated in your crosstab.
--
Duane Hookom
Microsoft Access MVP


"ChuckW" wrote:

Duane,

Thanks. This has been very helpful. One problem though is with sorting my
columns by month. In my SQL code I have the field Format(SalesDate,"mmmm")
AS [Month] which correctly assigns the month based on the SalesDate field. I
have chosen this field as my column header but it is placing the months in
alphabetical rather than chronological order. So it goes April, August,
December rather than January, February, March. Is there a way to have it
format chronologically?

Thanks,
--
Chuck W


"ChuckW" wrote:

Duane,

I have a customer table called Customer with fields that include
CustomerName, CustomerID and CustomerType. I then have created a table for
every month since January 2005 that includes CustomerID and Monthly Sales.
The Monthly Sale field name is different for every table (ie Jan05, Feb05,
Mar05...Feb07). I then create a long series of queries that join all of the
tables based on CustomerID using Left Joins. The final query so far called
Feb07query has CustomerName, CustomerId and the every month as a field. But
I want to format things as described below. I a a novice user. I know a
little about cross tabs queries but could use some help.

Thanks,
--
Chuck W


"Duane Hookom" wrote:

If you actually have field names that are months then you can normalize the
table with a union query and then create a crosstab based on your union query.

It would help if you told us exactly how your data is stored.

--
Duane Hookom
Microsoft Access MVP


"ChuckW" wrote:

Hi,

Thanks for your help. I have not but could use some help. Based on my
query which has CustomerName, Jan05, Feb05, Mar05.....Feb07, how would you do
a cross tab query? Right now I don't have the field Year for 2005, 2006 and
2007.

Thanks,
--
Chuck W


"Özcan ELMALI" wrote:

Hi,
Did you try CROSS-TAB QUERY?

"ChuckW" wrote:

Hi,

I have a fairly complex report that I am trying to design. I have a query
called SalesByMonth which lists the CustomerName and the fields for all
months since January 2005. I want to create a report that has CustomerName
and Year as row headers and the months as column headers. The report would
look like the following"

CustomerName Year January February March April May June................
ABC Corp. 2005 $100 $200 $300 $400 $500 $600
2006 $150 $250 $350 $450 $550 $650
2007 $225 $325 $425 $525 $625 $725

Can anyone help me with this?

Thanks,


--
Chuck W
.

Relevant Pages

  • Re: Using Start Date and End Date in Query
    ... ActualRevenue is the actual revenue for the customer in the period? ... it must not be stored in the PotentialRevenue table. ... Use a query to calculate the actual revenue. ... >>>>> boxes for the StartDate, EndDate and CustomerName, ...
    (microsoft.public.access.queries)
  • Re: Query not populating form
    ... I have tried putting my query as the record source of the form to no avail. ... first row from the CustomerName tbl. ... selection of a different customer. ...
    (microsoft.public.access.formscoding)
  • RE: Create report with three rows of data representing years
    ... Chuck W ... Feb07query has CustomerName, CustomerId and the every month as a field. ... table with a union query and then create a crosstab based on your union query. ... I have a fairly complex report that I am trying to design. ...
    (microsoft.public.access.reports)
  • Re: Query not populating form
    ... City, State, and Zip Code from the CustomerName table. ... I thought if I created a query on a single table then ... If you made the query the RecordSource of the form then it sounds like you want ... If so the ComboBox should be unbound and it ...
    (microsoft.public.access.formscoding)
  • Re: Query not populating form
    ... the last choice in the wizard. ... the process is a user selects a CustomerName ... I know that I need to create a query of some sort to relate the ... If so the ComboBox should be unbound and it ...
    (microsoft.public.access.formscoding)