How to treat data in months

[Guest]

Hello,

I am confused about how to handle columns of data in months in my new
database (I am a beginner). I have several tables (sales, cost, etc.) and
each table has monthly columns from Jan '03 through Dec. '05. How do I tie
these tables together so I can run a report that for example shows sales and
cost as separate lines under the monthly column. For example:


Jan03 Feb03 Mar03 Etc.
Sales
Cost
Etc.

Do I need to set up a separate table for the time periods and then set up a
relationship between the detail tables and this table? I am confused - any
help is much appreciated.

Thanks, John

 

[Ed Warren]

You have a database design problem. You have your rows and columns switched
and or need to 'Normalize' your data.

To add more data to your design requires you add new columns (jan06, feb06,
etc). In a properly designed database you should only have to add data, not
change the table design.
You should have something like:

SalesID
SaleDate
SaleCost
SaleAmount
SaleEtc.

Then you can run a query that summarizes (calculates the sum, count, etc)
for any period of interest groupd by month if desired.

That said, you can get from where you are to where you want to be using a
"union query". Look at the help screen and you will have to enter the query
as SQL not in the query design window.

(assuming all tables have the same column structure (e.g. Jan03 Feb03
Mar03, ....... Dec05)

Select *, "Sales" as type from Sales
Union
Select *, "Cost" as type from Cost
Union
Select *, "Ect" as type from Etc;


Ed Warren.

 

[John Vinson]

Hello,

I am confused about how to handle columns of data in months in my new
database (I am a beginner).

Simple: don't. See below.

I have several tables (sales, cost, etc.) and
each table has monthly columns from Jan '03 through Dec. '05.

These are not tables. These are SPREADSHEETS!

Data (dates) should NEVER be stored in fieldnames.

How do I tie
these tables together so I can run a report that for example shows sales and
cost as separate lines under the monthly column. For example:


Jan03 Feb03 Mar03 Etc.
Sales
Cost
Etc.

Do I need to set up a separate table for the time periods and then set up a
relationship between the detail tables and this table? I am confused - any
help is much appreciated.

Normalize your tables instead: rather than one FIELD per cost item or
sale item, use one RECORD for each:

Sales
SaleDate <e.g. #1/1/2003#, #2/1/2003# etc.>
SaleAmount <the value you now have in your Jan03 field>

You could - probably should - have one table with SaleDate as its
Primary Key and fields for Sales, Cost, and Etc. <g>.

If you want to DISPLAY the data in the form above, you can use a
Crosstab query.

John W. Vinson[MVP]