variable headings

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with Item & 12 quantity fields (one per month). However I
don't want the headings for the 12 qty fields to be Jan-Dec. Depending on
what a client's financial year start is, qty1 in the table may represent
January or March or June etc. So I want to design the form in such a way that
I store somewhere the starting month and then have the headings on the form
being derived from that.
 
The design you are suggesting is good for a spreadsheet, but incorrect for a
database.

Consider a table with these fields:
ClientID foreign key to your client table, indicating whose record
this is.
ForMonth Date/Time. First day of the month this record is for.
Quantity the amount for that client for that month.

In the Client table you can store the month number that begins their
financial year. In a query, you can then perform DateAdd() calculations to
give yourself a FinancialYear calculated field, so that you can group by the
client's financial year in your report.

This structure will be hugely more flexible, allowing comparisions in real
time, comparisions based on financial year, year-to-date operations and
comparisions (very easy in a report with running sum), and so on. The data
will still be usable even if the client changes when their financial year
starts at some point (which would be a real mess with your current
structure).

The crucial aspect is that where ever you see repeating fields - Month 1,
Month 2, ... - you *always* need to create a related table to hold a record
for each one instead.
 
John,

I think you should reconsider your design. In your design, you will have
fields that will be, for all intents and purposes, unquery-able. You'll
never know what month you're looking at.

Instead, I'd suggest that you create a second table for your monthly
amounts, and perhaps another one for your items and their descriptions.
You link them together using the "relational" part of the relational
database model, and go from there. You'll be a lot happier in the long run.

grep
 
Thanks Allen, I understand what you are saying so-
I now have tblSales with the fields: ItemID, ForMonth, Quantity.
I have tblControl with the fields: ClientID, FinStartMonth, CurrentMth
Is this what you meant I should do?

So now I need to display the current years quantities (all 12 separately)
with an appropriate heading for each (Jan, Feb etc) after the user has
selected the Item they want to view. How best do I build this query/form? I
know this is asking a lot but as you can see my inexperience is showing.
 
You can use a crosstab query to turn the months into column headings. But
before you do that you need to offset according the the client's financial
year.

I'm assuming your tblClient.FinStartMonth is a Number field, where you enter
the number of months offset for the year:
0 = the client's year starts in January;
1 = a one month offset (starts in Feb);
and so on.

You can then create a query that has both tblClient and tblSales, and type
something like this into the Field row:
MonthNum: Month(DateAdd("m", -[FinStartMonth], [ForMonth]))
The MonthNum will contain the value 1 for the first month of the client's
financial year, 2 for their 2nd month, and so on.

Now change the query to a Crosstab query (Crosstab on Query menu).
- RowHeading = ClientID
- ColumnHeading = the expression above.
- Value = Quantity (choosing Sum in the Group By row).
Limit the query so it contains only one financial year (Where in the Group
By row under ForMonth).
In the Properties of the query, enter Column Headings of:
1,2,3,4,5,6,7,8,9,10,11,12
so that the query returns all columns, even if data is not yet known.

You can now create a report that has fields named 1 to 12. To see these as
month names for the client, use a text box instead of a label over the
column, and use a DateAdd() expression to add the months back on again,
based on tblClient.FinStartMonth. Then set the Format propery of these text
boxes to
mmm
so they show the 3-char month you wanted.

HTH
 
Brilliant!
One question: I'm not sure which date to refer to for the Text boxes to
contain the column headings. i.e. =DateAdd("m",+[tblClient]![FinStartMonth],
? ])
Also, what if I now need two years of data on the form and a Forecast for
each of the twelve months by year? Do I need to have separate queries for
each year?
 
Since the date heading is just going to give a month, the year doesn't
matter, so you could use Jan 1 of any year, e.g.:
=DateAdd("m", [tblClient]![FinStartMonth], #1/1/2004#)

Yes, the query will handle 12 months only. If you want to report a different
period, managing the headings for a crosstab becomes messy. It is doable,
but requires programmatically showing and aligning the columns in
Report_Open, and possibly assigning the SQL property to the querydef before
opening the report so you get the desired Column Headings. OTOH, these
problems do not arise if you are willing to accept the monthly values one
underneath the other in the report instead of in columns for the months.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
John said:
Brilliant!
One question: I'm not sure which date to refer to for the Text boxes to
contain the column headings. i.e.
=DateAdd("m",+[tblClient]![FinStartMonth],
? ])
Also, what if I now need two years of data on the form and a Forecast for
each of the twelve months by year? Do I need to have separate queries for
each year?

Allen Browne said:
You can use a crosstab query to turn the months into column headings. But
before you do that you need to offset according the the client's
financial
year.

I'm assuming your tblClient.FinStartMonth is a Number field, where you
enter
the number of months offset for the year:
0 = the client's year starts in January;
1 = a one month offset (starts in Feb);
and so on.

You can then create a query that has both tblClient and tblSales, and
type
something like this into the Field row:
MonthNum: Month(DateAdd("m", -[FinStartMonth], [ForMonth]))
The MonthNum will contain the value 1 for the first month of the client's
financial year, 2 for their 2nd month, and so on.

Now change the query to a Crosstab query (Crosstab on Query menu).
- RowHeading = ClientID
- ColumnHeading = the expression above.
- Value = Quantity (choosing Sum in the Group By row).
Limit the query so it contains only one financial year (Where in the
Group
By row under ForMonth).
In the Properties of the query, enter Column Headings of:
1,2,3,4,5,6,7,8,9,10,11,12
so that the query returns all columns, even if data is not yet known.

You can now create a report that has fields named 1 to 12. To see these
as
month names for the client, use a text box instead of a label over the
column, and use a DateAdd() expression to add the months back on again,
based on tblClient.FinStartMonth. Then set the Format propery of these
text
boxes to
mmm
so they show the 3-char month you wanted.

HTH
Click to expand...
Click to expand...
 
I have done this but i get " The object doesn't contain the Automation object
'tblClient' " message, presumably because [tblClient]![FinStartMonth] doesn't
exist in the cross tab query and is therefore not available to the form. What
is the best way to approach this? a dlookup on form open and store the value
in a variable?
 
Yes, you could use DLookup() to get the field from the table.

That should be reliable if you put this into the ClientID Header section
(rather than the Page Header section), and set the section's Repeat property
to Yes so it shows up on each page.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John said:
I have done this but i get " The object doesn't contain the Automation
object
'tblClient' " message, presumably because [tblClient]![FinStartMonth]
doesn't
exist in the cross tab query and is therefore not available to the form.
What
is the best way to approach this? a dlookup on form open and store the
value
in a variable?

Allen Browne said:
Since the date heading is just going to give a month, the year doesn't
matter, so you could use Jan 1 of any year, e.g.:
=DateAdd("m", [tblClient]![FinStartMonth], #1/1/2004#)
Click to expand...
Click to expand...
 
Back
Top