rename fields in queries

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

Guest

I need help in renaming fields in a query.

My current query output contains a single field "Start Date" and 180 columns
called "P1" through "P180". Each of the "P" columns contain numeric data for
each of 180 months.

I would like to use the "Start Date" (which is in date format) as a
reference in order to rename each of my monthly columns so that they have the
correct date, such as "2/28/06", "3/31/06", etc., in a date format that I can
later manipulate.

I imagine that I will need to use an "end of month" function (if similar to
Excel) and increment each column label one month more than the previous
column, with the starting month be "1/31/06" (assuming that the Start Date
field has a value of 1/1/06).

The eventual goal is that I would be able to select certain months, like all
12 months of 2007, say, and then present them in quarters, annually, etc.,
using the date summation functions of Access. Therefore, it is important
that the column headings be in date format if possible.

If you have a different approach, I’d like to hear it, too.

Thank you in advance for your help.
 
My best advice, and I do believe others will agree, that you need to change
your database structure to "Normalized" data.

Do not use separate fields for each month. Doing so is 'spreadsheet
thinking' and does not use the capabilities of a relational database.
 
If you really want to do it your way then the process I outline below will
get you there.

In Excel using the autofill create your list like this for the 180 colomns.
2/28/2006 3/31/2006 4/30/2006 5/31/2006
P1 P2 P3 P4

Copy, click in cell B3 and Paste Special – Transpose.
2/28/2006 P1
3/31/2006 P2
4/30/2006 P3
5/31/2006 P4
6/30/2006 P5
7/31/2006 P6

Auto fill column A like this for the 180 rows –
1
3
5
7
9
….
181

Below that autofill for 180 more like this –
2
4
6
8
….
182

Sort on column A so that data looks like this –
6 2/28/2006 P1
7
8 3/31/2006 P2
9
10 4/30/2006 P3
11
12 5/31/2006 P4
13
14 6/30/2006 P5

Insert one cell above P1 so the data looks like this –
6 2/28/2006
7 P1
8 3/31/2006
9 P2
10 4/30/2006
11 P3
12 5/31/2006
13 P4

In column D, one row below that of 2/28/2006 type in =Bx which is the
cell that has 2/28/2006. In column E of the same row as above type in =Cx
which is the cell that has P1.

The data will look like this –
6 2/28/2006
7 P1 2/28/2006 P1
8 3/31/2006 1/0/1900 0
9 P2 3/31/2006 P2
10 4/30/2006 1/0/1900 0
11 P3 4/30/2006 P3
12 5/31/2006 1/0/1900 0
13 P4 5/31/2006 P4

Highlight columns D & E, copy, and Paste Special – Values.

Sort on column B and data will look like this ( I only have some of the
numbers here).
28 1/31/2007 1/0/1900 0
30 2/28/2007 1/0/1900 0
32 3/31/2007 1/0/1900 0
7 P1 2/28/2006 P1
9 P2 3/31/2006 P2
11 P3 4/30/2006 P3
13 P4 5/31/2006 P4
15 P5 6/30/2006 P5
17 P6 7/31/2006 P6
19 P7 8/31/2006 P7
21 P8 9/30/2006 P8
23 P9 10/31/2006 P9
25 P10 11/30/2006 P10

Copy columns D & E and paste special in Word – Unformated and it will look
like this --
2/28/2006 P1
3/31/2006 P2
4/30/2006 P3
5/31/2006 P4
6/30/2006 P5
7/31/2006 P6
8/31/2006 P7
9/30/2006 P8

Do a replace all tab ( ^t ) with “: and then the data will look like
this –
2/28/2006â€:P1
3/31/2006â€:P2
4/30/2006â€:P3
5/31/2006â€:P4
6/30/2006â€:P5
7/31/2006â€:P6
8/31/2006â€:P7
9/30/2006â€:P8

Do a replace all line breaks ( ^p ) with , “ and then the data will
look like this –
2/28/2006â€:P1, “3/31/2006â€:P2, “4/30/2006â€:P3, “5/31/2006â€:P4,
“6/30/2006â€:P5, “7/31/2006â€:P6, “8/31/2006â€:P7, “9/30/2006â€:P8

Insert a double quote in front of 2/28/2006.

You can now copy and paste this in to your SQL statement.
 
Thank you. I will try it as you described.

FYI, I have been working with a real estate software package called "Argus".
I have used a feature of Argus where financial data and multitude of tenant
and assumption data for about 100 properties is loaded into various Access
Tables automatically. One of these tables lists out 180 months of financial
data. Therefore, I am dealing with a ready-made table that looks like a
giant spreadsheet. The Rows are Income and Expense accounts and the columns
are months 1-180.

I am hoping to get to a point where I can select those columns (a particular
set of time periods) for reporting.

Thanks again. I will report back progress.

Ken
 
KARL said:
My best advice, and I do believe others will agree, that you need to change
your database structure to "Normalized" data.

Do not use separate fields for each month. Doing so is 'spreadsheet
thinking' and does not use the capabilities of a relational database.
Click to expand...

I have a question about your proposed design.

To make things easier, let's assume there are three columns, rather
than 180, named P1, P2 and P3.

Assume some reasonable business rule requirements:

a) each entity must have exactly one date value for each period
b) date values are in chronological order for each entity (P1 then P2
then P3).

Question: with your design, how do you ensure there are exactly three
rows, P1, P2 and P3, for each entity i.e. no omissions? And the date
order?

This is very simple when the attributes are modelled as columns in the
same table (as the OP has it) by making the columns 'required' e.g.

CREATE TABLE Entities (
entity_ID INTEGER NOT NULL UNIQUE,
P1 DATETIME NOT NULL,
P2 DATETIME NOT NULL,
P3 DATETIME NOT NULL,
CHECK (P1 < P2),
CHECK (P2 < P3)
);

Your proposed design as presented seems to be this:

CREATE TABLE Periods (
entity_ID INTEGER NOT NULL
REFERENCES Entities (entity_ID),
period_name CHAR(2) NOT NULL,
CHECK (period_name LIKE 'P[1-3]'),
UNIQUE (entity_ID, period_name),
period_date DATETIME NOT NULL
);

However, this will not prevent omissions or dates in an illegal order
e.g.

INSERT INTO Periods (entity_ID, period_name, period_date)
VALUES (1, 'P1', #2006-01-01 00:00:00#)
;
INSERT INTO Periods (entity_ID, period_name, period_date)
VALUES (1, 'P3', #2005-11-01 00:00:00#)
;

Such constraints are not impossible to define with your proposed design
but they are more inconvenient e.g. (untested; I'd use an auxiliary
Sequence table of integers if there really were 180 tables involved):

ALTER TABLE Periods ADD
CONSTRAINT periods__always_three_in_chrono_order
CHECK (
EXISTS (
SELECT * FROM Periods AS P1, Periods AS P2, Periods AS P3
WHERE P1.entity_ID = P2.entity_ID
AND P1.entity_ID = P3.entity_ID
AND P1.period_name = 'P1'
AND P2.period_name = 'P2'
AND P3.period_name = 'P3'
AND P1.period_date < P2.period_date
AND P2.period_date < P3.period_date
)
);

I say 'inconvenient' because although this constraint is fine for
DELETE and UPDATE anomalies, it is no good for INSERT anomalies because
in Jet we cannot INSERT three rows in a single atomic action i.e. the
CHECK bites after the first INSERT without giving us the opportunity to
insert the other two. The workaround is to, in a serialized transaction
(i.e. one that prevents further data and schema changes on the table),
DROP the CHECK, do the inserts, recreate the CHECK, test the data
integrity e.g.

UPDATE Periods SET entity_ID = entity_ID;

and see if the CHECK bites, then either rollback or commit as
necessary. As I say, inconvenient, if even possible in Jet (e.g. the
transaction type).

I am not trying to rubbish your proposed design. I agree with you that
the OP's design is not normalized. Rather, my points are:

a) Your design is incomplete without at least pointing out that
additional constraints are required (ideally, you would define the
constrains in your reply);

b) given the limitations of Jet, it may be appropriate to denormalize.
Possible limitations: can you do more than one row atomic insert? are
serialized transactions supported (if yes, is it acceptable to lock the
entire table, data and schema)? is it possible to write a Jet SQL proc
to do the DROP, multiple insert, recreate and test, all in a
transaction?

I think, on balance, I may opt to denormalize in this case (the case
admittedly being extrapolated by me but is based on entirely reasonable
assumptions about date order and omissions).

Jamie.

--
 
Your thought process is still "spreadsheet" and not relational database. The
table structure should be like this --
TransactionID - Autonumber - primary key
TransDate - Datetime
Amount - number - single or double based on precission required.
EnterBy - text - optional - identifies who made the entry.
Remarks - text or memo based on number of characters that might be entered.
A text field can be change later to memo if needed.

Entries can be made on a daily basis instead of monthly. A totals query
then can be used to roll up the data to whatever level the report needs. A
crosstab query can then produce a spreadsheet report.

I just build my tables in the design view - GUI method.

Jamie Collins said:
KARL said:
My best advice, and I do believe others will agree, that you need to change
your database structure to "Normalized" data.

Do not use separate fields for each month. Doing so is 'spreadsheet
thinking' and does not use the capabilities of a relational database.
Click to expand...

I have a question about your proposed design.

To make things easier, let's assume there are three columns, rather
than 180, named P1, P2 and P3.

Assume some reasonable business rule requirements:

a) each entity must have exactly one date value for each period
b) date values are in chronological order for each entity (P1 then P2
then P3).

Question: with your design, how do you ensure there are exactly three
rows, P1, P2 and P3, for each entity i.e. no omissions? And the date
order?

This is very simple when the attributes are modelled as columns in the
same table (as the OP has it) by making the columns 'required' e.g.

CREATE TABLE Entities (
entity_ID INTEGER NOT NULL UNIQUE,
P1 DATETIME NOT NULL,
P2 DATETIME NOT NULL,
P3 DATETIME NOT NULL,
CHECK (P1 < P2),
CHECK (P2 < P3)
);

Your proposed design as presented seems to be this:

CREATE TABLE Periods (
entity_ID INTEGER NOT NULL
REFERENCES Entities (entity_ID),
period_name CHAR(2) NOT NULL,
CHECK (period_name LIKE 'P[1-3]'),
UNIQUE (entity_ID, period_name),
period_date DATETIME NOT NULL
);

However, this will not prevent omissions or dates in an illegal order
e.g.

INSERT INTO Periods (entity_ID, period_name, period_date)
VALUES (1, 'P1', #2006-01-01 00:00:00#)
;
INSERT INTO Periods (entity_ID, period_name, period_date)
VALUES (1, 'P3', #2005-11-01 00:00:00#)
;

Such constraints are not impossible to define with your proposed design
but they are more inconvenient e.g. (untested; I'd use an auxiliary
Sequence table of integers if there really were 180 tables involved):

ALTER TABLE Periods ADD
CONSTRAINT periods__always_three_in_chrono_order
CHECK (
EXISTS (
SELECT * FROM Periods AS P1, Periods AS P2, Periods AS P3
WHERE P1.entity_ID = P2.entity_ID
AND P1.entity_ID = P3.entity_ID
AND P1.period_name = 'P1'
AND P2.period_name = 'P2'
AND P3.period_name = 'P3'
AND P1.period_date < P2.period_date
AND P2.period_date < P3.period_date
)
);

I say 'inconvenient' because although this constraint is fine for
DELETE and UPDATE anomalies, it is no good for INSERT anomalies because
in Jet we cannot INSERT three rows in a single atomic action i.e. the
CHECK bites after the first INSERT without giving us the opportunity to
insert the other two. The workaround is to, in a serialized transaction
(i.e. one that prevents further data and schema changes on the table),
DROP the CHECK, do the inserts, recreate the CHECK, test the data
integrity e.g.

UPDATE Periods SET entity_ID = entity_ID;

and see if the CHECK bites, then either rollback or commit as
necessary. As I say, inconvenient, if even possible in Jet (e.g. the
transaction type).

I am not trying to rubbish your proposed design. I agree with you that
the OP's design is not normalized. Rather, my points are:

a) Your design is incomplete without at least pointing out that
additional constraints are required (ideally, you would define the
constrains in your reply);

b) given the limitations of Jet, it may be appropriate to denormalize.
Possible limitations: can you do more than one row atomic insert? are
serialized transactions supported (if yes, is it acceptable to lock the
entire table, data and schema)? is it possible to write a Jet SQL proc
to do the DROP, multiple insert, recreate and test, all in a
transaction?

I think, on balance, I may opt to denormalize in this case (the case
admittedly being extrapolated by me but is based on entirely reasonable
assumptions about date order and omissions).

Jamie.
Click to expand...
 
KARL said:
Your thought process is still "spreadsheet" and not relational database.
Click to expand...

*You* are not thinking 'relational' e.g. an autonumber is not a
relational key. You are not even thinking 'database' e.g. a unique
incrementing/random integer/GUID column does not make a good primary
key in Access/Jet because you end up with a poor (non-maintained)
clustered index for the table.
The
table structure should be like this --
TransactionID - Autonumber - primary key
TransDate - Datetime
Amount - number - single or double based on precission required.
EnterBy - text - optional - identifies who made the entry.
Remarks - text or memo based on number of characters that might be entered.
A text field can be change later to memo if needed.
Click to expand...

I've tried to simplify the OP's schema to focus on data integrity
constraints and whether to purposely denormalize (thinking
'relational') because of limitations in the product implementation
(thinking 'database', specifically Access/Jet).

You've extrapolated the OP's schema by adding descriptions of data and
metadata columns with still no discussion of data integrity
constraints, beyond the poor choice of PK (and data type questionable
data type for the amount column; hint: floating point data is
inaccurate by nature, amounts are usually required to have guaranteed
accuracy).

Really, I think you had a valid point about the OP needing to at least
consider a normalized structure but suggesting some data integrity
constraints would be nice.

Jamie.

--
 
Jamie said:
*You* are not thinking 'relational' e.g. an autonumber is not a
relational key. You are not even thinking 'database' e.g. a unique
incrementing/random integer/GUID column does not make a good primary
key in Access/Jet because you end up with a poor (non-maintained)
clustered index for the table.
Click to expand...

My reply sounds a little rude; that was not my intention and I
apologize. I admit I thought it a bit rich that you would say I was not
thinking 'relational database' only for you to immediately post
something unrelational e.g. the autonumber PK.

Again, I'm not trying to rubbish your design, rather I think it a
little simplistic (e.g. did you consider the clustered index
implications in choosing you PK?) and would encourage you do expand
your ideas (e.g. domain checking constraints, ensuring chronological
order, etc).

My view is that things like using autonumbers are lifestyle choices: I
won't challenge you when you use one, likewise I would appreciate if
you do not prescribe one for me.

Thanks,
Jamie.

--
 
My point was that the use of dates as field names was the wrong way to
collect data.

If you want more on primary keys then see the discussion “Primary Keys†post
started by LurfysMa yesterday 7/12/06 under General Questions.
 
Back
Top