Indirect() in Access?

[Guest]

Hi,

I am trying to do something similar to the Excel function Indirect() in MS
Access. Apparently, there is no such function in Access. (I was trying to
search in the forum, but I didn't find any results.)

Therefore, how do I make a similar function in a Access Form-PivotTable? Is
it possible to do it through Modules(VBA)? Or, is there any other method to
get around with this?

What I am doing:

I have a table similar to the following:
Category1 C2 C3 C4 Quarter1 Q2 Q3
A C E G 123 24 29
B D F H 34 36 3
A C E H 283 23 29
B D E G 13 4 93
B C F G 323 22 30
(It will have Q4, Q5, Q6, .... in the future)

Now, I want a pivottable that summarizes the above table as follows:
C2 All
C4 All
Q1 Q2 Q3 %Change(Q3/Q1 - 1)
C1 C3
A E 306 47 58 -81.05%
A F 0 0 0 N/A

However, the problem is that there will be more quarters to be updated and
%Change formula should therefore be adjusted accordingly. My first thought
was to create a "reference table" as follows:
Quarter
Q3
so that I can use dlookup() in the Calculation (under
Forms>PivotTable>Calculated Detail Field>Properties). However, dlookup()
returns a text instead of the reference field. Can someone help me to get
around with this?

(PS. My supervisor doesn't want me to use MS Excel...)
(PPS. I am only somewhat familiar with the tables and queries...and very new
to the other sections in Access...)

Thanks so much!

- Drawde

 

[Douglas J Steele]

Hate to seem harsh, but the reason there's no Indirect function is probably
because it shouldn't be required in a properly normalized database.

You appear to have repeating groups in your table. I'm not sure what C2, C3
and C4 are, but you should have one row for each quarter, not a row with
multiple fields.

Jeff Conrad has a lot of good references on the topic of database design at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

 

[Guest]

Access crosstab queries are somewhat similar to Excel pivot tables. Check the
online help.

Barry