|
Brandon,
You don't have to make a separate table. It should be a
straightforward case for crosstab query. Simply list all the months in proper
order, in Column Headings property of the query, in following style
"Jan","Feb","Mar", --- , "Dec"
It might be preferable to use
three character abbreviation for month names. While naming the fields
conflicts with reserved words are to be avoided. For example, you
can use SYear and SMonth instead of Year and month.
Apparently, you have not included all the fields in
your post. Typically, a field like SalesQuantity would serve as Value field in
the crosstab query, while SYear & Vendor will serve as Row Headings.
SMonth will need to be designated as Column Heading.
Best wishes,
A.D.Tejpal
----- Original Message -----
Sent: Tuesday, June 28, 2005 01:12
Subject: [AccessDevelopers] creating a
table of data based on other table
I am having difficulty preparing data for a report. Currently I
have a table with the following information:
Vendor, Year, Month
ABC
Company, 05, JA
ABC Company, 05, FE
ABC Company, 05, AP
ABC Company,
05, JL
DEF Company, 05, JA
I want to create a report that has the
vendor as the row data and the months as the column data. Where there is a
missing month there would be a blank (or some other kind of
indicator).
I tried to create another table to update and put all of
the months as a Y/n fields for each vendor, but have been unsuccessful. Now, I
think I could create queries for each month and then union the queries
together, but that seems to be a lot of work, suggesting that there is an
easier way.
I would appreciate any help you can
give.
TIA,
:Brandon:
Please zip all files prior to uploading to Files section.
YAHOO! GROUPS LINKS
|
