Create a name from a table

[NevilleT]

I have two tables.

- One lists values for a period (tblAllocation). The two relevant fields
are PeriodName and Allocation. e.g. PeriodName P1, Allocation 10%, PeriodName
P10, Allocation 5%
- The second lists the periods (tblPeriod), Relevant fields are PeriodNo
and PeriodTitle e.g. PeriodNo P1, PeriodTitle Jan 07, PeriodNo P10,
PeriodTitle Oct 07

I want to output to Excel and display the headers for each column as their
PeriodTitle (Oct 07) rather than their PeriodName (P10).

I know I could go in and manually set up the query e.g. Oct 07: P10 but
periods can be re-assigned over time. For example P1 may be Jan 07 now but
in a few years may be re-assigned to Jan 09 for example.

Is there a way to lookup another table and create an alias for the table
header?

 

[Carl Rapson]

Create a query and join the two tables on the PeriodName-PeriodID fields:

SELECT tblPeriod.PeriodTitle,tblAllocation.Allocation
FROM tblAllocation INNER JOIN tblPeriod ON
(tblAllocation.PeriodName=tblPeriod.PeriodNo);

Export the results of that query to Excel.

Carl Rapson

 

[NevilleT]

Not quite that simple Carl. tblAllocation has 60 period fields from P1 to
P60. If there is an allocation in Feb 07 for example, tblPeriod tells me Feb
07 is assigned P2. I put an allocation in tblAllocation field P2.

 

[Carl Rapson]

Are you saying there are actually 60 period fields (P1 - P60) in
tblAllocation? If so, you need to rethink your table design. There should be
a single Period field (which will contain a string such as "P1" or "P60"),
linked to the corresponding PeriodID field in tblPeriod. That way, if you
ever need to add additional periods you can do so without having to change
the table design.

If for some reason you can't change your table design, I can't offer any
other suggestions.

Carl Rapson

 

[NevilleT]

Hi Carl
Against all the rules of normalisation, I created 60 period fields. Strange
as it might seem, I had to do it for performance reasons. I initially did
use a normalised approach but performance was poor and I rewrote it to use
single records for 60 periods. Reduced screen display time from around 20 to
30 seconds down to less than 5 seconds.

I have overcome the issue by actually using VBA to create the SQL string.
It took a bit to get it working but I now create the SQL statement, and save
it as a querydef. I run the query and output it to Excel then delete the
query. Bit tedious but it gets the job done. Thanks for your help.