Can you use field names to point to needed data?

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

Guest

I have a table that I need to get data out of but I only need certain fields
during certain times of the year. Example:
GL Table has the following fields = FR_PER_1_Act, FR_PER_2_ACT,
FR_PER_3_Act,, etc...

I want to use this table to get data. I will have 4 weeks of data for
period 1 in the GL table. (Period 1- Week 1, Period 1-Week2, Period 1-Week3,
Period 1-Week 4) I want to take those figures and subtract one from the
other. In order to do that I need to Identify the column(Field) that I need
to pull the data from since all of the period information is stored in
columns and not rows.

In other words if the user inputs that he wants Period 1 information how do
I tell the system to only pull FR_PER_1_ACT out of the GL table? Or if the
user wants Period 2 data that the system only pulls FR_PER_2_ACT, etc...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The table is not in Normal form. Change it to Normal form & the query
will be MUCH easier. If you don't know what Normal form is then read a
book on database design. I usually recommend _Database Design for Mere
Mortals_ by Hernandez.

Example:

CREATE TABLE GL (
Account int not null ,
ActionDate datetime not null , -- date $ values entered
Acutal money not null ,
Budget money not null ,
CONSTRAINT PK_GL PRIMARY KEY (Account, ActionDate)
)

You should also have a table that holds the fiscal year date info:

CREATE TABLE FiscalDates (
Year int not null ,
Period byte not null ,
StartDate datetime not null ,
EndDate datetime not null ,
CONSTRAINT PK_FiscalDates PRIMARY KEY (Year, Period, StartDate)
)

Then you'd compare the GL.ActionDate to the FiscalDates.StartDate and
EndDate to find out the FY & Period.

The query for different time periods (1st Qtr 2004):

SELECT Account, FD.Year, FD.Period, Actual, Budget
FROM GL, FiscalDates AS FD
WHERE FD.Year = 2004 AND FD.Period IN (1,2,3)
AND GL.ActionDate BETWEEN FD.StartDate And FD.EndDate

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQksXNIechKqOuFEgEQKZTgCg4aOGSNLuget8ScNy92fKeFgyi+8AoO0M
aqaX5DlRoZ9N+zs6UGSD6Yt8
=K/u3
-----END PGP SIGNATURE-----
 
Back
Top