Making table with objects and dates

  • Thread starter Thread starter reidarT
  • Start date Start date
R

reidarT

I want to show data objects as Y-axis (rows) and dates alng x-axis (fields)

Date 01.01.05 02.01.05 03.01.05 ...
Obj1 Free Free Occupied
Obj2 Free Occupied Free
Obj3 Occupied Free Occupied
....

I wouldn't like to make a table with Dates as fields ( I need more than 2
years ahead)
Is threr another way to solve this problem?
regards
reidarT
 
It looks to me like you have it backwards. You are not creating a
spreadsheet, you are making a database. Your date would be a part of your
record. If you want a wide table with over 700 columns, then you need to do
it in Excel. Access does not allow 700+ fields in a table.

Rick B
 
Hi Reidar

You are quite right - you should certainly not create a table with dates (or
any other data for that matter) as field names. That would be breaking one
of the basic rules of database design.

Instead, create a simple table with two fields: OccObjID and OccDate. The
presence of a record in this table can indicate "Occupied" in the
corresponding cell, and the absence of a record indicates "Free".

You possibly want to store more related information, such as who the
occupier is, in which case you will need an additional field for the
purpose.

Now, your "spreadsheet" can be made with a crosstab query, using ObjName
(from a related table) as the row heading, OccDate as the column heading,
and the following expression as the value:
IIf( IsNull(OccDate), "Free", "Occupied")
 
Back
Top