I would like the report to display in the following manner:
Product Oct-07
Nov-07
[Product] [Quantity10] [Monthly Revenue 10] [Quantity11] [Monthly Revenue
11]
and so forth so that that date range would be from whatever the earliest
date in the [Production Pending-adjusted] table is to the latest date.
The query I designed, utilizing the instructions you provided, transforms
and combines the date fields with the records in the [prod pending 2] table
(which are Quantity and Monthly Revenue). the results is fields named
[Quantity 1], [Quantity 9], [Quantity 10], [Quantity 11], [Quantity 12],
[Monthly Revenue 1], [Monthly Revenue 9], [Monthly Revenue 10], [Monthly
Revenue 11], [Monthly Revenue 12] & [Product].
:
You do have a date field in [Production Pending-adjusted]. You don't need a
date on a form. This could be any date (if you need a sliding window of
months) or just use the Month([Date]).
Questions:
Do you need to display a range of months/dates in your report or just simple
use the Month() of the date field?
What columns does your query display and what do you want to display in your
report?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm
:
Well, I am assuming that you are referring to the article on creating Dynamic
Monthly Crosstab Reports. There are a few reasons I dont see how this can
apply to my situation.
This article seems to be referring to a FormA which I do not have (I don't
have any forms for this table). It also refers to a [sale date] field which
I do not have (there are no date fields on the query I built utilizing the
instructions in the article you showed me). There are only fields entitled
[Product], [Monthly Revenue1], [Monthly Revenue9], [Monthly Revenue10],
[Monthly Revenue 11], [Monthly Revenue12], [Quantity1], [Quantity9],
[Quantity10], [Quantity11] & [Quantity12].
Unless I am to utilize my production pending table for thsi query, in which
case I guess I would'nt need the query I built from your instructions...
This is my confusion....
:
Don't you want to show the month/quantity month/cost values in your report?
The solution I suggested allows you to define month values using the month
difference between a reference date (could be current date or the value on a
form control) and your [Date] field.
The solution I suggested would allow you to create a report very much like
your first posting.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm
:
Yes but I ddin't see how I could use that info for this report. Maybe I am
missing something.
:
Did you check out my other link to creating crosstab reports with dynamic
monthly headings?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm
:
Well, I'm not sure that you saw my second post, there. The query seemd to be
running fine. I need to create a report to present the date now. But all I
have is The fields of Product, Monthly Revenue 1, 9, 10,11 & 12 & Quantity 1,
9,10,11 & 12. I believe that I just want Product, Monthly Revenue and
Quantity to appear on the report.
the fields I setup in the [fldname] are Quantity and Monthly Revenue
:
What are the values in [Prod Pend Table 2].FldName?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm
:
I followed the instructions best I could and came up with a crosstab query
with two values. However, it only shows the Monthly Revenue value and not
the quantity value. Actually, it shows the monthly revenue value twice, in
bothe the monthly renenue and quantity columns.
Here is the SQL:
TRANSFORM Sum(IIf([FldName]="quantity",[Quantity],[Quantity]*[Recurring
Charge])) AS test
SELECT [Production Pending-adjusted].Product
FROM [Prod Pend Table 2], [Production Pending-adjusted]
GROUP BY [Production Pending-adjusted].Product
PIVOT [FldName] & Month([Date]);
:
I don't think you need to worry about different product "fields" since your
desired output has products as row headings and months as column headings. To
sort out your column headings, check out this link
http://www.tek-tips.com/faqs.cfm?fid=5466.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm
:
Ok, thanks for the link. That may work. However, how do I make a report
out of this as the query will identify each product individually and I cannot
manually make fields for each product as they will change daily? Also, the
information must show as monthly totals, not daily entries.
Thanks for your help.
:
Consider applying the multi-value solution found at
http://www.tek-tips.com/faqs.cfm?fid=4524.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm
:
Because you can specify only one value in a crosstab query. I need two
values ([quantity] & [value])
:
Is there a reason you don't use a crosstab query?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm
:
Hello,
I am trying to create a report with columns without repeating certain text
boxes. Here is an example of what I would like to create:
[Date] "Month1" [Date] "Month2"
[Date] "Month3"
[Product] "Product1": [quantity] [value] [quantity] [value] [quantity]
[value]
[Product] "Product2": [quantity] [value] [quantity] [value] [quantity]
[value]
[Product] "Product3": [quantity] [value] [quantity] [value] [quantity]
[value]
[Product] "Product4": [quantity] [value] [quantity] [value] [quantity]
[value]
etc...
Unfortunately, when I utilize columns, what I get is...
[Date] "Month1"
[Date] "Month2"
[Product] "Product1": [quantity] [value] [Product] "Product1":[quantity]
[value]
[Product] "Product2": [quantity] [value] [Product] "Product2":[quantity]
[value]
[Product] "Product3": [quantity] [value] [Product] "Product3":[quantity]
[value]
[Product] "Product4": [quantity] [value] [Product] "Product4":[quantity]
[value]
Since the products will change on a daily basis, I need the columns to be
driven by month with all of the products on the report to be listed only in
the first column (much like in a crosstab query).
any ideas would be greatly appreciated.
Thanks
