Using number format for dates?

[Guest]

I have one table where I store information about guesthouses every year, like
how many employees, rooms etc. For these records I have one field for YEAR. I
have another table that has data on monthly occupancy at each guesthouse,
example Number of Occupants, Number of Bednights, etc. For this table, the
records have a date field that includes both month and year. In my query, I
can relate the yearly profiles for each guesthouse with the monthly occupancy
rates.

I have related the dates as follows:

I have made one table for the YEAR as follows:

ID txtYEAR
1 2000
2 2001
3 2002 ....

Then, I have one table for months which is linked to the YEAR table:

ID txtMONTH txtYEARID
1 01 1
2 02 1
3 03 1
4 01 2

By doing this, I am able to sort my records by year and by month very
easily, which helps with data entry. I am also able to relate my two tables
in my query as I need.

However, it seems like an awkward way to deal with dates. Surely there must
be some other way to deal with this, using date fields. One basic question is
how do I make a field with a date format accept only the four-digit year, and
not a full date? Also, in general is my structure OK in the longrun? What
limitations does it have?

 

[Duane Hookom]

You shouldn't need a year table. If you want to store a year and month in a
table (without the day of the month) use two fields, Yr (integer) and Mth
(integer). Don't store either of these value in text fields.